Capture Error Message in Server Routine (Transform Function)

[bandish]

Hi,

I need to populate two columns
ERR_C
ERR_MSG_T

ERR_C will contain the error code describing the exception condition which caused the job to abort.
ERR_MSG_T will have the error message of the exception condition which caused the job abort.
For example:
ERR_C=01017
ERR_MSG_T=Invalid Username/Password: logon denied.

How can I fetch these values after a job has run.
Can I do it in a Server Routine? If yes, then which function do I need to use. Else, what should be my approach?

Thanks
Bandish

[chulett]

After the job has run, you would fetch this information from the job's log. Techniques have been discussed here a great many times, search for the wealth of good information you'll find here. Yes, you could use another job that leverages the various 'DSGet' functions that access log information, or something similar using the equivalents with the 'dsjob' command in a command line script.

[poonamgoyal]

if it is a case of row rejections then you can visit vmcburney's blog which gives a way to capture sqlcode and sqlstate in case of row rejections for db2 and oracle databses at ITtoolkit

[bandish]

In the routine I can use the following functions to fetch log entries:
1. DSGetLogEntry Function
2. DSGetLogSummary Function
3. DSGetNewestLogId Function

But in a particular job there can be multiple fatal errors. So, how to fetch the exact EventID for fatal error which has the Error code : 1017 and
Error Message: invalid username/password; logon denied

Is there any way to fetch the exact fatal exception condition which caused the job to abort ?

[ray.wurlod]

You can not invoke a server routine from a parallel job. You can, however, invoke one from a Routine activity in a job sequence. Review the online help and/or manuals for the DataStage API functions. There is one family of functions with names beginning DSGet... that allow you to interrogate the system including reading a job log.

The other half of your problem is getting these values into your table. I would suggest that the routine writes them to a file, or invokes a server job and passes the values as job parameters, to get the values into the table.

Most people implementing such an approach also include in the table the job name, job start timestamp, executing user ID and various other diagnostic values. Most of these can be obtained with other DataStage API functions.

[chulett]

You can fetch all messages from the current run of a certain type - warning, fatal, whatever you like.