How to reduce the current size of IBM InfoSphere Information Server XMETA database

Xmeta DB is used by DataStage itself for doing its work. But sometimes when there is lot of project on DataStage then it need to be monitored. 
In this case, Xmeta size is growing rapidly so here is some steps which can solve this problem a bit.


The first method is from within db2 (the system should be quiesced for this approach):

   1.  Start the db2 command line processor:

    cd /opt/IBM/db2/V9/bin

    ./db2

    2. Connect to the database (replace with db2 admin username and password):

    db2 => connect to xmeta user <username> using <password>

    3. Temporarily disable transaction logging (note that the table name suffix may be different for your installation):

    db2 => alter table XMETA.LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F activate not logged initially

    4. Delete events matching the category name 'IIS-DSTAGE-RUN'

    db2 => delete from LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F where CATEGORYNAME_XMETA = 'IIS-DSTAGE-RUN'

    5. Exit the command processor:

    db2 => quit

The XMETA.LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F table (the suffix may differ depending on your installation) is the only table affected by OR logging and is the only table that should grow rapidly (if OR logging is enabled).

Assuming this is a DB2 xmeta, and the repository database is named XMETA, an SQL command such as this will also tell you if there are any log events that belong to DataStage in the LoggingEvent table:

SELECT *

FROM XMETA.LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F

WHERE CATEGORYNAME_XMETA = 'IIS-DSTAGE-RUN'

The Oracle table name is XMETA.LOGGING_LOGGINGEVENT1466CB5F.

On an Oracle db you can use the TRUNCATE command to delete all rows in a table. The LOGGING_LOGGINGEVENT1466CB5F table is independent and can also be dropped and recreated if necessary.

The second method is to schedule a background purge of the log events.

   1.  Use the following command line (from the ASBServer/bin (ASBServer\bin directory)) to create the scheduled auto purge task:

    ./LoggingAdmin.sh -user <user> -password <password> -create -schedule -name "DS job event purge task" -frequency -minutes 30 -threshold 10000 -percentage 100 -includeCategories IIS-DSTAGE-RUN

Or

   

LoggingAdmin.bat -user <user> -password <password> -create -schedule -name "DS job event purge task" -frequency -minutes 30 -threshold 10000 -percentage 100 -includeCategories IIS-DSTAGE-RUN

    Note: Replace <user> <password> with a suite admin user/password

    2. After all DS log events have been purged, the scheduled task can be deleted with the following command line:

    ./LoggingAdmin.sh -user <user> -password <password> -delete -schedule -name "DS job event purge task"

Or

    LoggingAdmin.bat -user <user> -password <password> -delete -schedule -name "DS job event purge task"

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