z/OS stored procedure doesn't return value

**Knut Stolze** · Apr 7 '08, 12:25 PM

Re: z/OS stored procedure doesn't return value

N61601@gmail.co m wrote:

I'm running a java program on a PC which calls z/OS SQL stored
procedure. The stored procedure returns an integer which should
contain "2", but is always "0".
>
I (pre)compile the stored procedure from the PC using "IBM Data
Studio" which automatially refreshes the WLM proc.
>
Is there something simple I'm missing? Do I need to stop/start the
DB2 procedure?
>
Thank you in advance,
Serenity
>
Background -
Running UDB V9.5 on PC
Running DB2 v8.1 on z/OS 1.5
>
Java snippet -
cstmt = con.prepareCall ("CALL SCRATCHDB.SAMPL EPROCEDURE(?)") ;
cstmt.registerO utParameter(1, Types.INTEGER);
cstmt.execute() ;
int AwCrap = cstmt.getInt(1) ;
System.out.prin tln("SAMPPROC output - " + AwCrap);
>
Stored procedure -
CREATE PROCEDURE SAMPLEPROCEDURE
(OUT RETURN_THIS_DAM NIT INTEGER )
RESULT SETS 1
LANGUAGE SQL
EXTERNAL NAME SAMPPROC
FENCED
COLLID SCRATCHDB
WLM ENVIRONMENT WLMENV1
RUN OPTIONS 'NOTEST(NONE,*, *,*)'
BEGIN
DECLARE RETURN_THIS_DAM NIT INTEGER DEFAULT 1;

This declaration here is shadowing the input parameter. You're sure you
want to do this?

SET RETURN_THIS_DAM NIT = 2;
END

Try this procedure:

CREATE PROCEDURE SAMPLEPROCEDURE (OUT return_this_dam nit INTEGER)
LANGUAGE SQL
BEGIN
SET return_this_dam nit = 2;
END@

This one works fine for me.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

z/OS stored procedure doesn't return value

z/OS stored procedure doesn't return value

Comment