DSXchange

All,
Is it possible to execute a stored procedure as an After Job Routine.. If it is possible, can you please help me with the same.

Thanks in advance.

It is possible, but it's possibly not what you want to do. The technique is to use BASIC SQL Client Interface (BCI) functions to mimic code written to the ODBC API. You will need a licensed ODBC driver; the ones that come with DataStage are not licensed for this type of use.

After establishing the three environments, setting connection options, connection to the database, you would then invoke SQLExecDirect with a CALL statement before retrieving results, disconnecting and freeing the environments. Did I mention binding parameters to the statement and to the result set? Oh, and there are possible errors at every step, so these have to be coded for too.

A preferred approach is to add an ODBC stage to your job (after existing processing), or to a separate job (run the one after the other using a sequence perhaps), and use its stored procedure capability.

Hi,
another though more complex (?) would be to use an after sub to run a job that will do it using the ODBC stage.

Just an idea.

by the way this is the 2nd of third post I've seen to day that was actually began in a prior one, so please try to continue your posts rather then post new ones.

IHTH,

you could use the after job sub routine. There u could use ExecSH routine to call a shell script that just calls the stored procedure...

hope u get the idea.. or look at this to understand what to write in shell script..send the database userid, pwd and connect string for sqlplus as parameters to this shell script, and also the arguments that u need to send to procedure..

sqlplus $1/$2@$3 << EOF
exec proc1('$4', '$5', '$6');
EOF
exit 0
~