EXECSH at the end of a job called by a sequence

TonyInFrance · Post by **TonyInFrance** » Fri Aug 10, 2012 2:49 am

Chetan... Thanks a lot. Its much clearer now...

However I'm still stuck at the UNIX script part:

I do not actually have a .ksh per se that calls the .sql. My DSEE job launches the sqlplus command as an After-job subroutine on successful completion of the job and looks like this:

sqlplus USR/PWD@SCHEMA @filename.sql PARAMETER

There is one PARAMETER which is the only one required by the .sql

Thus is there anyway I could include the way to catch the sql error in this? Or do I have to write a ksh. This ksh has to be called by Datastage and thus I decided to do it this way since just for one command I didn't see the point in creating a Sequence job.encapsulating the unitary job.

chulett · Post by **chulett** » Fri Aug 10, 2012 6:15 am

I don't know why you think you need a Sequence job to run a script, if you had one it could be run via the same ExecSH after job command you are using right now.

That being said, seems to me all you need to do is add the one line to the beginning of your sql file that Chetan noted:

WHENEVER SQLERROR EXIT SQL.SQLCODE;

Try that.

TonyInFrance · Post by **TonyInFrance** » Fri Aug 10, 2012 6:42 am

Bingo.

This works perfectly.

Thanks Craig. This is just what I was looking for. Just this one line and I get a warning in my Director.

Cheers

chetan.c · Post by **chetan.c** » Fri Aug 10, 2012 7:50 am

The job should have aborted as per the below link.
Even I tried it out, it just logs a warning.

http://www-01.ibm.com/support/docview.w ... wg1JR36642
Thanks,
Chetan.C

TonyInFrance · Post by **TonyInFrance** » Fri Aug 10, 2012 8:03 am

Well the unitary job throws warnings. Tha parent sequence which calls this unitary job aborts.

chetan.c · Post by **chetan.c** » Fri Aug 10, 2012 8:16 am

Anyways, glad its working as you required

TonyInFrance · Post by **TonyInFrance** » Fri Aug 10, 2012 8:22 am

Thanks once again Chetan, Ray & Craig...