Datastage Repository - XMETA

Prior to Datastage version 8, the datastage repository information was stored in files(uv). From version 8, the repository information is also stored in database. Since the information is available in database, it is easier to write queries and find the details. During the installation you will have the option to install the repository either in DB2 or in Oracle. Currently there is not much docuementation about the XMETA repository from IBM.


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.