DSEXECUTE Help in after job subroutine

jpr196 · Post by **jpr196** » Thu Jun 25, 2009 4:30 pm

Hi,

We've created a routine that runs as an after-job subroutine in which DSExecute is utilized to launch a unix script with some sql in it.

The program works fine. However, we're trying to build in error messages, so that let's say the SQL didn't execute for whatever reason (database down, etc.) then an error message will be passed to the Ascential Log files.

At first, I thought I could use DSExecute's SysRet, but it appears to return 0 as long as the command is launched...regardless of what happens from there.

Anyone have any suggestions as to how to pass an error message to the Ascential logs?

chulett · Post by **chulett** » Thu Jun 25, 2009 5:52 pm

What database? Your script needs to farm any problems from whatever logs or session capturing you might be able to do. You can then echo those to std out and set an appropriate exit status so DataStage will notice.

jpr196 · Post by **jpr196** » Thu Jun 25, 2009 6:14 pm

Thanks for the response. We're on DB2. I think I have something that will work using Exit 0 and 1 based on conditions for logging an error.

chulett · Post by **chulett** » Thu Jun 25, 2009 9:37 pm

I've had success redirecting sqlplus output to a temp file and grepping the file afterwards for ORA errors. If found they can be echo'd and the exit status set appropriately.