473,224 Members | 1,477 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 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 7414
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Toonman | last post by:
I'm trying to use a couple of variables in a stored procedure. Things work fine when I hard code the data into the variables and also work fine when I use the variable in the WHERE clause and hard...
4
by: Shawn Fletcher | last post by:
Hi, I'm trying to work around a bug that our helpdesk software has. When a new issue is created, it cannot automatically default 2 fields to the value of No like we need it to. I have a...
4
by: PCL | last post by:
I am new to asp.net and am having a hard time with what i would usually do with a recordset. I have a simple query that returns a record - how can i get that record value into a variable after i...
5
by: Lili | last post by:
I'm having problems creating a simple stored procedure in DB2. Can someone help? Here is the screen dump when I tried to load the stored procedure. Thanks for any help. Create procedure...
7
by: Anthony Robinson | last post by:
Have been encountering an odd issue. Every now and again, certain packages of stored procedures just become invalid. I'm aware that dropping or altering an underlying table would render a package...
1
by: E.T. Grey | last post by:
I have been busting my nut over this for pretty much most of the day and it is driving me nuts. I posted this to an mySQL ng yesterday and I have not had any response (I'm pulling my hair out...
4
by: James | last post by:
How can I see/set the timeout for a postback? I have a button click event which loops through a DataSet and calls a stored procedure for every record in the dataset. This can be a very large...
1
by: Ted | last post by:
In MS SQL I used the following to create a stored procedure. USE AdventureWorks; GO IF OBJECT_ID ( 'HumanResources.usp_My_Search', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.usp_My_Search;...
7
by: CSharper | last post by:
Yesterday I had a heated discussion with my colleagues on what is a data centric application and having business logic in sql. I have group of people who wants to include all the business logic in...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.