Return status from a stored pros in a OS script

[Odd Bjørn Andersen]

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

[Knut Stolze]

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

[mike]

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

[Odd Bjørn Andersen]

>"mike" <_l*****@yahoo. comwrote in message
news:11******* *************** @k79g2000hse.go oglegroups.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_statu s
...whatever

:nonzero_statu s
...whatever

Yes, that worked just fine!

Thank you very much.

Regards
Odd B