471,047 Members | 1,237 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,047 software developers and data experts.

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 7169
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.