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.
Executing Stored Procedure as a After Job Routine
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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,
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,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
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
~
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
~