Simple stored procedure issues, pls help

[peaceburn]

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!?

[Serge Rielau]

pe*******@gmail.com wrote:

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!?

Cursor declaration is part of the "header" of a compount (like declaring
of variables).
DECLARE GLOBAL TEMPORARY TABLE is a DDL statement.

So any cursors defined in a block need to be specified before teh first
SQL statement.
The easiest way around your chicken-egg problem is:

P1: BEGIN

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

BEGIN -- Push a new compound!

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

-- Cursor left open for client application
OPEN cursor1;

END; -- End nested compound

END P1

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html