DataStage logging changes introduced at 8.1 can impact performance and can also result in missing log entries

Abstract

DataStage logging was changed at release 8.1 to log job run detail records into the operational repository, (xmeta) rather than the local project level log files, (RT_LOGxxx) that we utilized in prior releases for each job. As a result of this change we have seen the following issues:

Job run times can increase and Information Server client applications may not be as responsive depending on the amount of log data that is generated by the jobs.

In some cases no job log entries are written to the log as viewed via the DataStage Director client or Web Console, even though jobs appear to run and in some cases job log detail entries may be missing or do not show up until later.

Job log purge operations may run slowly or fail depending on the amount of log entries.

Content

These issues can be worked around by reverting to the logging mechanism that was in place prior to release 8.1 by implementing the following project level changes on the DataStage engine server.

Edit the project level DSParams file (replace %projectName% with the actual project name) typically located in:
 /opt/IBM/InformationServer/Server/Projects/%projectName% for Linux/UNIX andC:\IBM\InformarmationServer\Server\Projects\%projectName% for Windows and modify the following 2 lines as shown to revert to pre 8.1 logging:

RTLogging=1
ORLogging=0

Keep in mind that newly created projects inherit their settings from the DSParams file that is located in /opt/IBM/InformationServer/Server/Template/DSParams for Linux/UNIX and C:\IBM\InformationServer\Server\Template\DSParams for Windows by default and that it should also be modified to ensure that new projects use the pre 8.1 logging mechanism.

After switching to RTLogging, the existing log details entries in the repository can still be viewed via the Web Console or Server Console but they will not be accessible using the DataStage Director. These log entries should be purged when they are no longer required by scheduling a background purge of the log entries; up to 10,000 at a time is suggested to minimize the memory requirements and to ensure that we do not run into WebSphere Application Server out of memory issues by trying to purge all the log entries as one task.

The following commands will schedule a task to delete up to 10,000 log entries at a time and will loop until all the DataStage entries are purged. This could take some time depending on how many log entries you have to purge. This method has the advantage in that it can be run without stopping other activities.

Use the following command line, (from the …InformationServer/ASBServer/bin directory) to create the scheduled auto purge task (replace <user> <password> with a suite admin user/password):

For Windows:

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

For Linux/UNIX:

./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 

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

For Windows:

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

For Linux/UNIX:

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

Source: IBM Technical Documentation

The XMETA Repository for Information Server 8.1 grows rapidly until it runs out of disk space

Question

Why does the xmeta repository for Information Server v8.1 grow in size rapidly (much faster than in v8.0) until running out of disk space. Also, how do we recover from this situation if no more space can be allocated?

Note: The following document discusses several topics related to the size management of the Xmeta repository. Before you perform any of the suggested procedures to free up space in the Xmeta repository you must obtain a backup of the database.




Impact of Xmeta Logging (ORLogging) in the growth of the Repository


Beginning with Information Server (IIS) 8.1, sites may experience a rapid growth in the size of the xmeta repository due to IIS 8.1 changing the default logging method to "xmeta logging" resulting in all job log messages written to the xmeta repository. Prior to IIS 8.1, only non-job messages were stored in xmeta. As a result, sites that do not closely monitor or regulate the volume of logged messages may allow the xmeta repository too large on busy systems resulting in errors. Additionally, xmeta logging has performance implications and some known problems that can occur on systems that do not yet have fix pack 1 applied.


The following technote discusses the xmeta logging change in more detail, including how to switch to the original logging method, and also how to schedule a purge of DataStage job log messages already logged to the xmeta repository:


DataStage logging changes introduced in Information Server v8.1


In addition to the actions listed in the above technote, further analysis of the xmeta repository may be needed to determine if the growth was primarily due to message logging or other activities, and to adjust the Information Server and database configuration to best meet your needs.




Managing job log message histories with disk logging

• After RTLogging is enabled you won't be able to use the Information Server Console to see new job logs. It will still show old log messages saved before the configuration change, plus messages generated by other IIS components such as the Information Service Director (ISD), but new job logs won't be accessible thought this tool. To view job logs after RTLogging is enabled you need to use DataStage Director Client. 
• After you switch to RTLogging you can use DataStage Administrator client to define purge actions for all jobs in a specific project. Automatic purge can be configured based on either message age or the number of job runs (i.e. save messages from only the last 3 runs of a given job). By setting auto-purge you can prevent job logs from growing excessively. Be aware that changes here only affect new created jobs. 
• The DataStage Directory client can be used to purge the history of a job after RTLogging has been enabled. To purge logs already stored in xmeta repository you should use either the console options listed below, or the commands listed in the previously referenced technote. The instructions for purging messages via the consoles are described in the following InfoCenter topics:
  Purging log entries via the Web Console for Information Server
  Purging log entries via the Console for Information Server
• Note that purging messages from the xmeta repository will not directly reduce the size of the table or the tablespace used by the xmeta repository. The table size can be reduced after purging messages by performing a table reorganization as discussed at the end of this technote.

Identifying largest tables and purging LoggingEvent Table

Use the following queries to determine what are the largest tables in Xmeta and determine the best course of action:

• 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

Once you have identified the message category that has the most rows then you can delete messages belonging to that category. For example, if most of the messages have category of IIS-DSTAGE-RUN then those are job log messages and can be removed by running the LoggingAgent command to purge messages with that category as discussed in the following technote:

DataStage logging changes introduced in Information Server v8.1

NOTE: We recommend using only the methods discussed this technote to purge messages from the logging table. You should not need to directly clear the table via database commands. Also do not remove records from any table other than the LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F table. Other tables in the xmeta repository store data that should not be deleted and doing so could render the repository unusable.


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.

Note that if you purge old messages and then reorganize the table where messages were removed, that may free up pages within the tablespace but it will not reduce tablespace size on disk. For example, if the xmeta tablespace was using 25GB of space and 99% full, then after a purge/reorg the tablespace will still have size of 25GB but you may only be 50% full, meaning that additional usage of xmeta can occur without needing to increase tablespace size.

Normally DB2 can increase tablespace size as needed until the disk/volume runs out of space. Thus if you only had a small amount of space, such as 4GB, allocated for DB2, you may need to increase disk space allocation in addition to managing the deletion of aged messages.


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, which are actions beyond the scope of this technote.

If the large xmeta tablespace caused a disk full condition, then you will need to add additional space to that volume to prevent problems for any applications which run on that volume (including DB2 since it does have need for space outside the xmeta tablespace such as for other databases and DB2 log files).


Source : IBM FAQ

How to improve Job Performance?

There could be various reasons behind this issue. I will list down few of them here. In my case the IBM Information server set-up is on Windows Server 2003 SP2 and with IBM Information server 8.1. It is a SMP set-up and meta data repository database is on the same server as well. The meta data repository database is on SQL Server 2005.

Now server hang issue can occurred when

1) Metadata repository database detects a deadlock condition and choose failing job as the victim of the deadlock.
2) Log maintenance is ignored.
3) Temp folders are not maintained periodically.

I will try to explain above three points in detail below:

1) Occurrence of deadlock into meta data repository database - I have seen this scenario in the DataStage 8.1 with no fix packs installed in it. If you have fix packs (released later) installed then you may not get this problem at all. IBM Information Server throws an exception like "[IBM][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." This exception makes the whole process unresponsive which in turn hangs the server and needs a physical reboot. However deadlock would always be there and we can only reduce their occurrence. Following two actions can be performed to reduce the possibility of having deadlock situation:

a) Keep the user interaction minimized during the transactions. This is more related to SQL queries where user input is required.
b) Keep less number of records and in one batch. For example if you are using Oracle Enterprise stage to load data then by defining less array size would reduce the possibility of having deadlock during the job run.


2) Log maintenance is ignored - In DataStage 8.1 log gets stored into metadata repository database. A table called "logging_xmetagen_LoggingEvent1466cb5f" holds all the logs in XMETA database. If we ignore purging the logs periodically then this table's size would go very large and DMLs would take longer to execute. Hence we should schedule the log purging/clearing periodically. Logs purging/clearing can be scheduled from Director client.

To schedule the auto log purge, select a job and then by selecting Job menu --> Clear Log --> options from pop up window. You can set the logs to purge immediately after job run or set auto purge up to some last run or till a particular date. It would be better if we set the auto purge with "Older than (days)" setting.

One more thing to note here is that the table "logging_xmetagen_LoggingEvent1466cb5f" keep logs for all the projects present on the server. Hence we should not just fire a DML on this table as this could impact other projects as well. To remove entries from this table we can use the following command which creates a scheduler on the server using the logadmin utility and delete the logs.

This command will delete the logs from table "logging_xmetagen_LoggingEvent1466cb5f" irrespective to the project hence be careful while executing this command.

LoggingAdmin -user [DataStage Admin User] -Password [DataStage Admin Password] -create -schedule -name "DS job log purge" -frequency –minutes 30 -threshold 10000 -percentage 100 -includeCategories IIS-DSTAGE-RUN

Above scheduler can be deleted by following command:

LoggingAdmin –user [DataStage Admin User] -password [DataStage Admin Password] -delete -schedule -name "DS job log purge"

To check the number of records in "logging_xmetagen_LoggingEvent1466cb5f" table following query can be fired on XMETA database:

select count(*) from xmeta.logging_xmetagen_LoggingEvent1466cb5f

Having lots of logs into repository database would slow the log purge activity which in turn slows the performance of the job.


In DatStage 8.1 job log storage has changed from file system to database. In case your jobs logs gone very huge and you need some quick performance then you can switch back to the file system logging by just changing the following two project level parameters in DSparam in windows:

RTLogging=1
ORLogging=0

After changing the above settings logs would be stored in the file system and old logs can still be viewed into director client. Once we clear all the logs we should revert the changes to original setting. DSparam file can be found at \IBM\InformationServer\Server\Projects\YOUR_PROJECT\

3) Folders maintenance - There are few folder which needs maintenance in DataStage in windows environment. First one is tmp folder which store all the virtual dataset and other stuff while executing the jobs. Another one is UVtemp. We should make sure that this folder should not get full as this would cause the jobs to hang or slow. Keep clearing this folder periodically. Last but not the least is &PH& folder. Keep this folder clean as well. To remove the entries from &PH& folder "CLEAR.FILE &PH&" command can be executed from administrator client. To execute this command go to Administrator client --> Click on Projects tab --> Select the appropriate project --> Click on Command --> Paste CLEAR.FILE &PH& into command text box and click on Execute button. Once cleared you will see a popup window with a message "File &PH& has been cleared".

If we follow above listed maintenance activities then we can increase the performance of the jobs as well as keep the server safe from going into hung state. There are few more
tunings which we can perform on the server itself to make it suitable for large parallel jobs on Windows environment. 


Source : datastagetips

Generating DataStage Job Info

This syntax is used to generating job info status from a job:
dsjob -jobinfo
                  ${SERVER_NAME}
                  ${JOB_NAME} > ${OUTPUT_DIRECTORY}.txt

Generate DataStage Log from UNIX command

To Generate DataStage Log File from UNIX, you can use dsjob syntax.

dsjob syntax:
dsjob -logdetail
              {Server_Name}
              {Job_Name}
               `dsjob -logsum -type {Type} -max 2 {Server_Name} {Job_Name}|cut -f1` > {Destination File}.txt

Notes:
{Type} = [ <INFO | WARNING | FATAL | REJECT | STARTED | RESET | BATCH>]

Maintain Log Table on DB2 Repository

ps: Please becareful, I've never tried to delete logs from DB2 repository, it just stored for my notes in the future

The DDL for the log table is on DB2:

CREATE TABLE "XMETA "."LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F" (
"XMETA_CREATED_BY_USER_XMETA" VARGRAPHIC(64) ,
"XMETA_CREATION_TIMESTAMP_XMETA" BIGINT ,
"XMETAMODIFICATIONTIMESTAMPXMET" BIGINT ,
"XMETA_MODIFIED_BY_USER_XMETA" VARGRAPHIC(64) ,
"XMETA_OPTIMISTIC_LOCK_ID_XMETA" INTEGER ,
"XMETA_REPOS_OBJECT_ID_XMETA" VARCHAR(64) NOT NULL ,
"CATEGORYNAME_XMETA" VARGRAPHIC(255) ,
"CIS10_XMETA" VARGRAPHIC(255) ,
"CIS11_XMETA" VARGRAPHIC(255) ,
"CIS12_XMETA" VARGRAPHIC(255) ,
"CIS13_XMETA" VARGRAPHIC(255) ,
"CIS14_XMETA" VARGRAPHIC(255) ,
"CIS15_XMETA" VARGRAPHIC(255) ,
"CIS1_XMETA" VARGRAPHIC(255) ,
"CIS2_XMETA" VARGRAPHIC(255) ,
"CIS3_XMETA" VARGRAPHIC(255) ,
"CIS4_XMETA" VARGRAPHIC(255) ,
"CIS5_XMETA" VARGRAPHIC(255) ,
"CIS6_XMETA" VARGRAPHIC(255) ,
"CIS7_XMETA" VARGRAPHIC(255) ,
"CIS8_XMETA" VARGRAPHIC(255) ,
"CIS9_XMETA" VARGRAPHIC(255) ,
"CIT1_XMETA" DBCLOB(536870912) LOGGED NOT COMPACT ,
"CIT2_XMETA" DBCLOB(536870912) LOGGED NOT COMPACT ,
"CIT3_XMETA" DBCLOB(536870912) LOGGED NOT COMPACT ,
"CIT4_XMETA" DBCLOB(536870912) LOGGED NOT COMPACT ,
"CIT5_XMETA" DBCLOB(536870912) LOGGED NOT COMPACT ,
"CIT6_XMETA" DBCLOB(536870912) LOGGED NOT COMPACT ,
"CONTAINER_RID" VARCHAR(64) ,
"CONTEXT_XMETA" DBCLOB(536870912) LOGGED NOT COMPACT ,
"DELETED_XMETA" SMALLINT ,
"LEVEL_XMETA" INTEGER ,
"MESSAGE_XMETA" DBCLOB(536870912) LOGGED NOT COMPACT ,
"MSGID_XMETA" VARGRAPHIC(255) ,
"SEQ_XMETA" BIGINT ,
"THROWABLEINFO_XMETA" DBCLOB(536870912) LOGGED NOT COMPACT ,
"TIMESTAMP_XMETA" BIGINT )
IN "XMETA" ;

If you want to delete a job's log from DB2 repository:

delete FROM XMETA.LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F where cis13_xmeta = '<Job Name>'


Here are steps to remove all rows in the logging table

as db2inst1 (Instance owner)

1) db2 "select count(*) from xmeta.LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F"

record the number of rows in this table

2) create an empty file called empty.txt

3) db2 "import from empty.txt of del replace into xmeta.LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F"

Thanks

RTLogging and ORLogging

On DataStage 8.x by default it put all logging process into repository, but it will make your repository capacity full faster and will impact with your project / job because DataStage will raise an error if the capacity is full.

The other way, we can change into old logging mechanism with changing the parameter value on DSParams:
RTLogging=1
ORLogging=0

RTLogging=1 will make your logging saved into a RT File, and ORLogging=0 will avoid you from storing logging into repository database.