By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,190 Members | 777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,190 IT Pros & Developers. It's quick & easy.

Return status from a stored pros in a OS script

P: n/a
Is there a way to check the return status from a stored procedure when
called from a OS (Windows) script?

I have a stored procedure that does some checking in the database, and the
return status is set to 0 or -1 if 'OK' or 'not OK' respectively.

When I call the from CLP I get something like this:

Value of output parameters
--------------------------
Parameter Name : P_SQLSTATE
Parameter Value : 00000

Parameter Name : P_MESSAGE
Parameter Value :

Return Status = 0

I want to do this in my script:
db2 call proc(?, ?)
if RC= 0 then do something
else then do something else

If I run this script from the Task Center, is there a way to 'force' the
task to fail if the return status is not 0 ?

Regards
Odd B Andersen
ErgoGroup AS, Oslo, Norway
May 24 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Odd Bjørn Andersen wrote:
Is there a way to check the return status from a stored procedure when
called from a OS (Windows) script?

I have a stored procedure that does some checking in the database, and the
return status is set to 0 or -1 if 'OK' or 'not OK' respectively.

When I call the from CLP I get something like this:

Value of output parameters
--------------------------
Parameter Name : P_SQLSTATE
Parameter Value : 00000

Parameter Name : P_MESSAGE
Parameter Value :

Return Status = 0

I want to do this in my script:
db2 call proc(?, ?)
if RC= 0 then do something
else then do something else
I don't think that the exit status from the CLP is related to the return
status of a stored procedure. Therefore, you will have to parse the output
and act on the parsed result. Alternatively, you can write a small program
that invokes your procedure and uses the return status of the CALL as its
own exit status.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
May 25 '07 #2

P: n/a
On May 24, 9:53 am, "Odd Bjørn Andersen" <oba...@online.nowrote:
Is there a way to check the return status from a stored procedure when
called from a OS (Windows) script?

I have a stored procedure that does some checking in the database, and the
return status is set to 0 or -1 if 'OK' or 'not OK' respectively.

When I call the from CLP I get something like this:

Value of output parameters
--------------------------
Parameter Name : P_SQLSTATE
Parameter Value : 00000

Parameter Name : P_MESSAGE
Parameter Value :

Return Status = 0

I want to do this in my script:
db2 call proc(?, ?)
if RC= 0 then do something
else then do something else

If I run this script from the Task Center, is there a way to 'force' the
task to fail if the return status is not 0 ?

Regards
Odd B Andersen
ErgoGroup AS, Oslo, Norway

It depends on the scripting language that you use on Windows.

You must parse the output of the CLP invokation "db2 call ..." to
search for "Return Status = 0".

With scripting tools like Perl or *nix-like shells (like Ksh or Bash)
under Cygwin - you have more power to parse text.

If your only tool is basic CMD.EXE script language, then something
like this hack will work on Win/XP.

....
db2 "CALL your_procedure(...)" 2>&1 1>spcall.log
@rem the find command returns 0 when the string is found
find "Return Status = 0" spcall.log null
@if ERRORLEVEL 1 goto :nonzero_status
:success_status
....whatever

:nonzero_status
....whatever

May 25 '07 #3

P: n/a
>"mike" <_l*****@yahoo.comwrote in message
news:11**********************@k79g2000hse.googleg roups.com...
On May 24, 9:53 am, "Odd Bjørn Andersen" <oba...@online.nowrote:
>Is there a way to check the return status from a stored procedure when
called from a OS (Windows) script?

I have a stored procedure that does some checking in the database, and the
return status is set to 0 or -1 if 'OK' or 'not OK' respectively.

When I call the from CLP I get something like this:

Value of output parameters
--------------------------
Z Parameter Name : P_SQLSTATE
Parameter Value : 00000

Parameter Name : P_MESSAGE
Parameter Value :

Return Status = 0

I want to do this in my script:
db2 call proc(?, ?)
if RC= 0 then do something
else then do something else

If I run this script from the Task Center, is there a way to 'force' the
task to fail if the return status is not 0 ?

Regards
Odd B Andersen
ErgoGroup AS, Oslo, Norway


It depends on the scripting language that you use on Windows.

You must parse the output of the CLP invokation "db2 call ..." to
search for "Return Status = 0".

With scripting tools like Perl or *nix-like shells (like Ksh or Bash)
under Cygwin - you have more power to parse text.

If your only tool is basic CMD.EXE script language, then something
like this hack will work on Win/XP.

...
db2 "CALL your_procedure(...)" 2>&1 1>spcall.log
@rem the find command returns 0 when the string is found
find "Return Status = 0" spcall.log null
@if ERRORLEVEL 1 goto :nonzero_status
:success_status
...whatever

:nonzero_status
...whatever
Yes, that worked just fine!

Thank you very much.

Regards
Odd B
May 29 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.