In my project, the datastage repository was built on DB2 database. In fact, datastage installation comes with DB2 database software.
I found that the table names and column names are bit weird with some funky characters & numerals at the end may be that's IBM strategy to keep this information away from the developers..:). Please note that table names listed below may not be exactly same for you as the characters at the end of the table name may be different for each installation.
A word of caution: Do not try to alter the XMETA repository contents as it may have adverse effects.
Below are some of the queries that I have developed and I found that useful
1. Query to list Projects
SELECT *
FROM
XMETA.DATASTAGEX_XMETAGEN_DSPROJECTC2E76D84;
2. List the folders within a project
SELECT *
FROM
XMETA.DATASTAGEX_XMETAGEN_DSFOLDERC2E76D84
WHERE
XMETA_CREATED_BY_USER_XMETA != 'DataStageSystemUser'
AND NAMESPACE_XMETA = 'myproject';
3. Query to retrieve the DS JOB information
SELECT
(TIMESTAMP('01/01/1970', '00:00:00') + (XMETA_CREATION_TIMESTAMP_XMETA / 1000) SECONDS) AS CREATION_TIME,
(TIMESTAMP('01/01/1970', '00:00:00') + (XMETAMODIFICATIONTIMESTAMPXMET / 1000) SECONDS) AS MODIFIED_TIME,
A.*
FROM
XMETA.DATASTAGEX_XMETAGEN_DSJOBDEFC2E76D84 AS A
WHERE
NAME_XMETA = 'mydsjob';
4. Query to list the jobs that are locked
SELECT
A.NAME_XMETA, B.LOCKED_BY_USER,
(TIMESTAMP('01/01/1970', '00:00:00') + (B.LOCKED_AT / 1000) SECONDS) AS LOCKED_AT
FROM
XMETA.XMETALOCKINFO B,
XMETA.DATASTAGEX_XMETAGEN_DSJOBDEFC2E76D84 A
WHERE
A.XMETA_REPOS_OBJECT_ID_XMETA = B.REPOSITORY_ID;
5. Query to list the stages within the job
SELECT A.NAME_XMETA JOB_NAME, S.NAME_XMETA STAGE_NAME,
S.STAGETYPE_XMETA STTAGE_TYPE,S.*
FROM
XMETA.DATASTAGEX_XMETAGEN_DSSTAGEC2E76D84 S,
XMETA.DATASTAGEX_XMETAGEN_DSJOBDEFC2E76D84 A
WHERE
S.CONTAINER_RID = A.XMETA_REPOS_OBJECT_ID_XMETA
AND A.NAME_XMETA = 'mydsjob';
6. Query to show the job details by folder
SELECT CONTAINER_RID, XMETA_REPOS_OBJECT_ID_XMETA, XMETA_CREATED_BY_USER_XMETA,
(TIMESTAMP('01/01/1970', '00:00:00') + (XMETA_CREATION_TIMESTAMP_XMETA / 1000) SECONDS) AS XMETA_CREATION_TIMESTAMP_XMETA , XMETA_MODIFIED_BY_USER_XMETA, (TIMESTAMP('01/01/1970', '00:00:00') + (XMETAMODIFICATIONTIMESTAMPXMET / 1000) SECONDS) AS XMETAMODIFICATIONTIMESTAMPXMET,NAME_XMETA,SHORTDESCRIPTION_XMETA,
JOBTYPE_XMETA,CATEGORY_XMETA,DSNAMESPACE_XMETA FROM
DATASTAGEX_XMETAGEN_DSJOBDEFC2E76D84
WHERE
upper(DSNAMESPACE_XMETA) = upper('myproject'
and upper(category_xmeta) like upper('\\Jobs\\');
7. List all the table definitions
SELECT
(TIMESTAMP('01/01/1970', '00:00:00') + (XMETA_CREATION_TIMESTAMP_XMETA / 1000) SECONDS) - 7 HOURS AS CREATION_TIME,
(TIMESTAMP('01/01/1970', '00:00:00') + (XMETAMODIFICATIONTIMESTAMPXMET / 1000) SECONDS) - 7 HOURS AS MODIFIED_TIME,
T.*
FROM
XMETA.DATASTAGEX_XMETAGEN_DSTABLEDEFINITIONC2E76D84 T
WHERE
XMETA_CREATED_BY_USER_XMETA != 'DataStageSystemUser'
AND DSNAMESPACE_XMETA = 'myproject'
AND NAME_XMETA = 'mydsjob';
Note:
1. Timestamp is stored in UNIX Time or POSIX time format.(http://en.wikipedia.org/wiki/Unix_time).
2. In the queries above, timestamp is output in GMT.
I will update the blog when i find time to do more research on this.
