http://knol.google.com/k/seedhu/datasta ... v5dqqgym/1#
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. I think you can install the repository either in DB2 or in Oracle.IBM doesn't provide much information about the XMETA repository.
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 XMETA information away from the developers..:)
A word of caution: Do not try to alter the XMETA repository contents as it may have adverse effects.
Some sample XMETA queries
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 projectNAMESPACE_XMETA = 'COMMDSTAGEDEV:salesandadjustment';
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 -- S.OF_JOBDEF_XMETA = A.XMETA_REPOS_OBJECT_ID_XMETA
AND A.NAME_XMETA = 'mydsjob';
6. Query to show the job run report 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) - 8 HOURS AS XMETA_CREATION_TIMESTAMP_XMETA ,XMETA_MODIFIED_BY_USER_XMETA, (TIMESTAMP('01/01/1970', '00:00:00') + (XMETAMODIFICATIONTIMESTAMPXMET / 1000) SECONDS) - 8 HOURS AS XMETAMODIFICATIONTIMESTAMPXMET,NAME_XMETA,SHORTDESCRIPTION_XMETA,JOBTYPE_XMETA,CATEGORY_XMETA,DSNAMESPACE_XMETA FROM DATASTAGEX_XMETAGEN_DSJOBDEFC2E76D84 WHERE upper(DSNAMESPACE_XMETA) = upper(:')
and upper(category_xmeta) like upper('\\Jobs\\');