Hi,

I'm gonna pull my hair in the coming days with these DB2 stored
procedures.

So the issue, let's assume a simple stored procedure like this :

CREATE PROCEDURE MYSCHEMA.PROCEDURE1 ( )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

declare global temporary table Temp1 (tempdate date)
on commit preserve rows not logged ;

-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT PROCSCHEMA, PROCNAME FROM SYSCAT.PROCEDURES;

-- Cursor left open for client application
OPEN cursor1;
END P1


When I try to Build this I get an error :
------------------------------------------------------------
Create stored procedure returns -104.

DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: <cursor
declaration>;;<SQL statement>
------------------------------------------------------------

My idea is to create a temp table, do some processing there, and later
use the temp table (already filled with data) in a join clause within
the cursor declaration.

If I remove the cursor part , the procedure is built ok :

CREATE PROCEDURE MYSCHEMA.PROCEDURE1 ( )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

declare global temporary table Temp1 (tempdate date)
on commit preserve rows not logged ;

END P1



Also, if I keep the cursor part, but remove the temp table part,
everything is ok too:

CREATE PROCEDURE MYSCHEMA.PROCEDURE1 ( )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT PROCSCHEMA, PROCNAME FROM SYSCAT.PROCEDURES;

-- Cursor left open for client application
OPEN cursor1;
END P1


So what in this world is wrong with DB2 so it doesn't allows me to have
a temp table and a cursor in a stored procedure!?