How to return the return code of BTEQ to Datastage Routine.

[RaviReena]

I am running a BTEQ script from test.sh and test.sh is executed by routine from Sequencer.When my BTEQ script failed i didn't see any error message in Datastage director for my Sequencer.
If Datstage routine excecuting the BTEQ script failed, I want to send a notification for the failure.Since I am not getting any warnings or failures for my sequencer, I am not able to send the Email.

Any help or suggestions are greatly appreciated.

[chulett]

You need to incorporate some mechanism in your script to capture those BTEQ errors and propogate them back up to DataStage. I've had the same issue in the past with sqlplus, all the script (and thus DataStage) knew what that it successfully launched the sqlplus session. It had no clue that the sql itself inside that session failed miserably.

Added something to spool the session information to a file and then had the script grep through that file after the session ended looking for "errors". If some number of them other than zero were found, the exit status / return code of the wrapper script was set accordingly. DataStage was then able to take appropriate action.

[ray.wurlod]

You have to build that in. test.sh must echo anything returned from bteq and exit with the bteq exit status. The exit status can be detected through the fourth argument of the DSExecute subroutine; the output, including whatever you generated with echo statements, through the third argument.

[chulett]

There's probably more to it than that, if it works anything like sqlplus. It will begin and exit just fine as nothing went wrong with the session itself and could care less that everything you ran in the session blowed up. You may need to take extra steps to detect those problems, as I mentioned in my post.