468,535 Members | 1,671 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,535 developers. It's quick & easy.

Simple stored procedure issues, pls help

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

Jan 9 '07 #1
1 6952
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
Jan 9 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Toonman | last post: by
4 posts views Thread by Shawn Fletcher | last post: by
4 posts views Thread by PCL | last post: by
7 posts views Thread by Anthony Robinson | last post: by
4 posts views Thread by James | last post: by
7 posts views Thread by CSharper | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.