DataStage huge logs removal resolution

Problem – While running the Jobs in DataStage, sometimes the log becomes so huge that the jobs hangs down. Sometimes when the Job is aborted, even then the Warnings keep on generating for a long time. And this increases the log messages for that job, subsequently increasing the table saving information in the Metadata database.

This increases the size of the table and the database tremendously.

Solution –

The following are the steps which can help in resolving this problem –

1. If the Job is still running, then stop / abort the job.

2. Identify the Job name and the name of the target table for which these warning log messages are coming in DataStage.

3. In the Linux DataStage server, run the search command to find the process which is executing this job. The following Linux scripts can be used –

ps -ef|grep phantom|grep -v grep

ps -ef|grep JOBNAME

where JOBNAME is the name of the job.

Look for the process which is executing this job.

4. Kill this job with this command - kill -9 pid

Where pid is the process id.

5. If the Metadata is stored in DB2 database, then the table storing the log messages is – XMETA.LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F where XMETA is the name of the user.

To delete extra rows from this table, follow these steps.

a. select count(*) from XMETA.LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F where CATEGORYNAME_XMETA = 'IIS-DSTAGE-RUN' and cis12_xmeta = 'UDRPopulationTEST' and cis13_xmeta = 'jbDtl_NAVAID_Dtl'

b. delete from XMETA.LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F where CATEGORYNAME_XMETA = 'IIS-DSTAGE-RUN' and cis12_xmeta = 'UDRPopulationTEST' and cis13_xmeta = 'jbDtl_NAVAID_Dtl'

Please substitute the italicized names with relevant names.

6. After this you may need to re-org the table so that the data in the table is organized. Please note that for this step the DataStage and the metadata database needs to be closed sequentially.

Please re-start the DataStage server after making these changes.

Some other points which might be helpful –

1. Releasing the locks from DataStage Administrator –

a. Log in to the DS Administrator, select the Project and click Commands.

b. In the commands menu, type DS.TOOLS

c. In the options, select option 4 and find out the process which is related to the job

d. Then select option 7 to release the lock engaged by this process.

e. To select the job which is running, you can run the following query in the command option through DS Admin –

SELECT * FROM DS_JOBS WHERE DS_JOBS.NAME = 'jbDtl_ACFT_Type_Dtl'

f. One can also use the following command to find the processes running in DataStage –

list.readu every

SET.FILE UV VOC UV.VOC

COPY FROM UV.VOC TO VOC UNLOCK

unlock user

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