XMeta DB : Datastage Repository - 2

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.


Here is the Part 1 of this tutorial :   XMeta DB : Datastage Repository - 1

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 = '<project_name>'

AND NAME_XMETA = '<job_name>';

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.



till then.....
njoy the simplicity.......