Return status from a stored pros in a OS script

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Odd Bjørn Andersen - 24 May 2007 09:53 GMT

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

Reply to this Message

Knut Stolze - 25 May 2007 09:04 GMT

Odd Bjrn Andersen wrote:

> Is there a way to check the return status from a stored procedure when
> called from a OS (Windows) script?
[quoted text clipped - 18 lines]
> 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.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

mike - 25 May 2007 15:01 GMT

On May 24, 9:53 am, "Odd Bj?rn Andersen" <oba...@online.no> wrote:

> Is there a way to check the return status from a stored procedure when
> called from a OS (Windows) script?
[quoted text clipped - 25 lines]
> 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

Reply to this Message

Odd Bjørn Andersen - 29 May 2007 09:45 GMT

>> Is there a way to check the return status from a stored procedure when
>>called from a OS (Windows) script?
[quoted text clipped - 47 lines]
>:nonzero_status
>...whatever

Yes, that worked just fine!

Thank you very much.

Regards
Odd B

Reply to this Message