Usually, the datastage repository is built on DB2 database. because Db2
comes with the Information Server Software by defalt.
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 which can help you to fetch the lot of
information about the project or job.
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 = '<project_name>';
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
= '<job_name>';
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 = '<job_name>';
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('<project_name>')
and
upper(category_xmeta) like upper('\\Jobs\\');
njoy the simplicity.......
Atul Singh
