SEO

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

peaceburn@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