DataStage 8.1 Logging Issues and How to Purge the Logs

In DataStage 8.1 logging is changed to the metadata repository database by default, which has caused some performance issues including the logs being delayed in the director, or even missed. In 8.5 IBM reverts the change and the logs are logged back to the local project log files. See the link:

https://www-304.ibm.com/support/docview.wss?dc=D600&rs=14&uid=swg21370048&context=SSZJPZ&cs=UTF-8&lang=en&loc=en_US

In addition to the performance issues, I had another issue that my metadata database was filled up quickly because the logs in the xmeta would never been purged by the system. Before I realized it, the xmeta tablespace was already 30GB and the majority of the space was consumed by a table called LOGGING_LOGGINGEVENT1466CB5F, which had over 6 million records. I needed to purge the logs and release the space.

In my case, I looked into two ways to purge the logs:

• Using the Information Server web console. I created a log view 'ALL' with all the categories - but really later when querying the table (group by the column CATEGORYNAME_XMETA) I found the majority of the record is IIS-DSTAGE-RUN category.  Then I tried to purge the log view, but my web console session timed out every once a while with just some number of records being purged. I could repeat the way many times until all the records were purged but I decided to go with the second way of using LoggingAdmin.sh, as mentioned in the above IBM link.

• Using LoggingAdmin.sh. My initial try failed with Java class not being found errors. After worked with IBM support, it was found out the Websphere was in what is known as the minimal initialization state, meaning only a few of the services were active which didn't include the scheduling service. I was asked to put the WAS back to the normal state with commands:

         - Stop: $ISHOME/ASBServer/bin/MetadataServer.sh stop
         - Invoke: $ISHOME/ASBServer/bin/PropertyAdmin.sh -unset -key minimalISFInitialization
         - Start: $ISHOME/ASBServer/bin/MetadataServer.sh start

         After that, I was able to schedule a purge job with LoggingAdmin.sh and removed the log entries from  the table. However it was not done yet. I needed to free LOB space, see my another note:
         http://ccdba-net.blogspot.com/2010/09/undo-data-for-lob-segment-is-kept.html

        The SQL commands I used:
        alter table xmeta.LOGGING_LOGGINGEVENT1466CB5F modify lob(CIT1_XMETA) (shrink space cascade);
        alter table xmeta.LOGGING_LOGGINGEVENT1466CB5F modify lob(CIT2_XMETA) (shrink space cascade);
        alter table xmeta.LOGGING_LOGGINGEVENT1466CB5F modify lob(CIT3_XMETA) (shrink space cascade);
        alter table xmeta.LOGGING_LOGGINGEVENT1466CB5F modify lob(CIT4_XMETA) (shrink space cascade);
        alter table xmeta.LOGGING_LOGGINGEVENT1466CB5F modify lob(CIT5_XMETA) (shrink space cascade);
        alter table xmeta.LOGGING_LOGGINGEVENT1466CB5F modify lob(CIT6_XMETA) (shrink space cascade);
        alter table xmeta.LOGGING_LOGGINGEVENT1466CB5F modify lob(CONTEXT_XMETA) (shrink space cascade);
        alter table xmeta.LOGGING_LOGGINGEVENT1466CB5F modify lob(MESSAGE_XMETA) (shrink space cascade);
        alter table xmeta.LOGGING_LOGGINGEVENT1466CB5F modify lob(THROWABLEINFO_XMETA) (shrink space cascade);

        ALTER TABLE xmeta.LOGGING_LOGGINGEVENT1466CB5F ENABLE ROW MOVEMENT;
        ALTER TABLE xmeta.LOGGING_LOGGINGEVENT1466CB5F SHRINK SPACE CASCADE;
        ALTER TABLE xmeta.LOGGING_LOGGINGEVENT1466CB5F DISABLE ROW MOVEMENT;

        But still, I was not done yet, the database data file wouldn't shrink due to high water mark usage. My last thing was to shutdown the server, export the xmeta schema out, drop and recreate the tablespace then import the xmeta schema back, then bring up the server.