Taken from http://victimizeit.blogspot.in/2012/07/xmeta-db-datastage-repository.html
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 = '';
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 = '';
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 = '';
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('')
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 = ''
AND NAME_XMETA = '';
8. Identifying largest tables and purging LoggingEvent Table
In DB2:
SELECT substr(tabname, 1, 25) tabname, substr(tabschema, 1, 25) tabschema,
((fpages * pagesize) / (1024*1024)) Size_in_MB
FROM
syscat.tables t, syscat.tablespaces ts
WHERE
fpages > 0 AND t.tbspace = ts.tbspace
ORDER BY card DESC
FETCH FIRST 50 ROWS ONLY ;
In Oracle:
SELECT * FROM
(SELECT segment_name, owner, bytes/1024/1024 Size_in_MB
FROM
DBA_SEGMENTS ORDER BY bytes/1024/1024 DESC )
WHERE
ROWNUM <= 50;
The above queries will show the 50 largest tables in the xmeta database. The column Size_in_MB gives an approximation of the spaced used by each table. If the largest table is LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F then you can purge messages in this table to remove unnecessary rows.
If the largest table is LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F then you should identify which message category is creating most of the rows in this table. You can run the following command to identify these categories:
SELECT CATEGORYNAME_XMETA, count(*)
FROM
LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F
GROUP BY CATEGORYNAME_XMETA
ORDER BY count(*) DESC;
9. Analyzing Xmeta tablespaces in DB2
To view the size/status of the xmeta tablespace open a DB2 command prompt and enter commands:
CONNECT to xmeta ;
LIST TABLESPACES SHOW DETAIL ;
This will list all the table spaces, including xmeta. For each it will show the number of used and free pages and page size. If there are few free pages then your options are to increase the disk space available to the xmeta tablespace, or by freeing pages within the tablespace by purging unnecessary data such as old job log messages.
10. Reorganizing DB2 tables after purging messages to free pages in tablespace
When messages have been purged from the LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F table, the space used by those messages is not immediately returned to the tablespace as free pages. That occurs when the table is reorganized. Some database configurations may periodically reorganized tables automatically. To force a DB2 table to reorganize immediately, you can issue the following DB2 command:
db2 REORG TABLE XMETA.LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F use XMETATEMP;
Once the above command completes, you can run the LIST TABLESPACES SHOW DETAIL command again to confirm how many pages were freed in the tablespace and determine if additional action is still needed.
Please note that this action frees pages WITHIN the tablespace, it does not actually reduce the amount of disk space that the tablespace is using. To do that would require dropping and recreating the tablespace or moving the database to a new volume.
Note:
1. Timestamp is stored in UNIX Time or POSIX time format.
2. In the queries above, timestamp is output in GMT.
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 = '
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 = '
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 = '
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('
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 = '
AND NAME_XMETA = '
8. Identifying largest tables and purging LoggingEvent Table
In DB2:
SELECT substr(tabname, 1, 25) tabname, substr(tabschema, 1, 25) tabschema,
((fpages * pagesize) / (1024*1024)) Size_in_MB
FROM
syscat.tables t, syscat.tablespaces ts
WHERE
fpages > 0 AND t.tbspace = ts.tbspace
ORDER BY card DESC
FETCH FIRST 50 ROWS ONLY ;
In Oracle:
SELECT * FROM
(SELECT segment_name, owner, bytes/1024/1024 Size_in_MB
FROM
DBA_SEGMENTS ORDER BY bytes/1024/1024 DESC )
WHERE
ROWNUM <= 50;
The above queries will show the 50 largest tables in the xmeta database. The column Size_in_MB gives an approximation of the spaced used by each table. If the largest table is LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F then you can purge messages in this table to remove unnecessary rows.
If the largest table is LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F then you should identify which message category is creating most of the rows in this table. You can run the following command to identify these categories:
SELECT CATEGORYNAME_XMETA, count(*)
FROM
LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F
GROUP BY CATEGORYNAME_XMETA
ORDER BY count(*) DESC;
9. Analyzing Xmeta tablespaces in DB2
To view the size/status of the xmeta tablespace open a DB2 command prompt and enter commands:
CONNECT to xmeta ;
LIST TABLESPACES SHOW DETAIL ;
This will list all the table spaces, including xmeta. For each it will show the number of used and free pages and page size. If there are few free pages then your options are to increase the disk space available to the xmeta tablespace, or by freeing pages within the tablespace by purging unnecessary data such as old job log messages.
10. Reorganizing DB2 tables after purging messages to free pages in tablespace
When messages have been purged from the LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F table, the space used by those messages is not immediately returned to the tablespace as free pages. That occurs when the table is reorganized. Some database configurations may periodically reorganized tables automatically. To force a DB2 table to reorganize immediately, you can issue the following DB2 command:
db2 REORG TABLE XMETA.LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F use XMETATEMP;
Once the above command completes, you can run the LIST TABLESPACES SHOW DETAIL command again to confirm how many pages were freed in the tablespace and determine if additional action is still needed.
Please note that this action frees pages WITHIN the tablespace, it does not actually reduce the amount of disk space that the tablespace is using. To do that would require dropping and recreating the tablespace or moving the database to a new volume.
Note:
1. Timestamp is stored in UNIX Time or POSIX time format.
2. In the queries above, timestamp is output in GMT.
Tags
Datastage