Temporary Tables and Cursors to read them

[Kburton]

All,

I am trying to declare a cursor on global temporary table. I declared the global temporary table first and then the cursor but I got the following error:

42601(-104)[IBM][CLI Driver][DB2/NT64] SQL0104N An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>". LINE NUMBER=11. SQLSTATE=42601

Here is the stored procedure defintion:

Create Procedure get_monitor_info (in userid varchar(10), out rstring varchar(2048))
Dynamic Result Sets 0
Modifies SQL Data
Language SQL
Begin
Declare tmpstring varchar(2048);
Declare cstring varchar(200);
Declare recordcnt integer default 0;
Declare loopcnt integer default 0;
Declare global temporary table monitortmp (name varchar(200)) on commit preserve rows not logged;
declare c1 cursor for select name from session.monitortmp;

begin
open c1
fetch
close cl
drop temporary table
end

I have had some success if I use multiple begin and end blocks. I declare the temporary table in the first block and then declare the cursor in the second block. The problem is that the second block does not seem to be executed.

Create Procedure DB2MON.get_monitor_info (in userid varchar(10), out rstring varchar(2048))
Dynamic Result Sets 0
Modifies SQL Data
Language SQL
S1: Begin
Declare tmpstring varchar(2048);
Declare cstring varchar(200);
Declare recordcnt integer default 0;
Declare loopcnt integer default 0;
Declare global temporary table monitortmp (name varchar(200)) on commit preserve rows not logged;
declare c1 cursor for select name from session.monitortmp;

insert into session.monitortmp
select count(*) into recordcnt from session.monitortmp;

S2: Begin
declare c1 cursor for select name from session.monitortmp;
open c1;
while loopcnt <= recordcnt do
fetch c1 into cstring;
set tmpstring = tmpstring || cstring || ';';
set loopcnt = loopcnt + 1;
end while;
close c1;
End S2;
set rstring = tmpstring;
/* drop table session.monitortmp; */
End S1

Questions:

1. What is the best way to create a temp table and a cursor against it?
2. How do I get the stored procedure to execute the second block of code?

[frozenmist]

Hi Kburton,

I did not not try out the explicit cursor way. But i tries with implicit cursor and it worked fine.
This is what I did

create procedure db2admin.sample
language sql
begin
declare global temporary table sample_table (col1 integer) on commit preserve rows;
insert into session.sample_table values(1);
for v_cursor as select * from session.sample_table
do
 update session.sample_table set col1= col1+v_cursor.col1;
end for;
end
 
 

The value in the Global temporary table was 2 after executing the procedure.
Hope this helps.
However I will try with explicit cursor later. In the meantime some one else may be able to help you on that.

Cheers