We have moved to www.dataGenX.net, Keep Learning with us.

Tuesday, July 24, 2012

XMeta DB : Datastage Repository

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.

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\\');


Post continued in :  XMeta DB : Datastage Repository - 2




njoy the simplicity.......
Atul Singh