delete statement after a RETURN CURSOR in SP ?

[PaulR]

Hi,

we have a procedure with following structure ...

Insert into some regular tables using a unique 'call_id'
returning a cursor to caller with the rows relevant to this CALL .

CREATE PROCEDURE
begin
....
SET v_call_id = generate_unique();
....
begin
Declare return_cursor cursor with return to client for
select ......
.......
where t1.call_id = v_call_id
from table1,table2,table3

OPEN return_cursor;
end;
delete from table1 where call_id = v_call_id;
delete from table2 where call_id = v_call_id;
delete from table3 where call_id = v_call_id;
end;

The curious thing is this ...

If we don't perform the deletes at the end all works fine, if the
deletes are performed 1 row is lost from the result set ??

Is this a valid construct to OPEN Cursor with return then delete
immediately after before exiting the SP? - It builds and runs fine
apart from this.

[Serge Rielau]

PaulR wrote:

Hi,

we have a procedure with following structure ...

Insert into some regular tables using a unique 'call_id'
returning a cursor to caller with the rows relevant to this CALL .

CREATE PROCEDURE
begin
...
SET v_call_id = generate_unique();
...
begin
Declare return_cursor cursor with return to client for
select ......
.......
where t1.call_id = v_call_id
from table1,table2,table3

OPEN return_cursor;
end;
delete from table1 where call_id = v_call_id;
delete from table2 where call_id = v_call_id;
delete from table3 where call_id = v_call_id;
end;

The curious thing is this ...

If we don't perform the deletes at the end all works fine, if the
deletes are performed 1 row is lost from the result set ??

Is this a valid construct to OPEN Cursor with return then delete
immediately after before exiting the SP? - It builds and runs fine
apart from this.

The result set of the cursor gets computed as you retrieve the data.
So when you do the delete you are affecting the query "in flight".
This is called "self hosing"
I'm taking a guess here at what you may be trying to do:

Declare return_cursor cursor with return to client for
WITH d1 AS (SELECT * FROM OLD TABLE(DELETE FROM t1 WHERE ..)),
d2 AS (SELECT * FROM OLD TABLE(DELETE FROM t2 WHERE ..)),
d3 AS (SELECT * FROM OLD TABLE(DELETE FROM t3 WHERE ..))
select * from t1, t2, t3

Cheers
Serge

PS: Prereq: DB2 V8.1.4 for LUW
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

Serge Rielau wrote:

Declare return_cursor cursor with return to client for
WITH d1 AS (SELECT * FROM OLD TABLE(DELETE FROM t1 WHERE ..)),
d2 AS (SELECT * FROM OLD TABLE(DELETE FROM t2 WHERE ..)),
d3 AS (SELECT * FROM OLD TABLE(DELETE FROM t3 WHERE ..))

select from d1, d2, d3

Sorry
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[PaulR]

Serge,

Many thanks for your explanation, I was wrongly assuming the result set
was 'protected' once the cursor was opened.

This explains exactly what I am seeing.

Unfortunately, I don't understand the construct you are using with the
CTEs ?
- Do you have a pointer I can go off and read?

What the procedure is doing is using 3 Regular Tables as "temp tables"
rather than using SESSION tables.
and deleting from these temp tables at the end of the Procedure i.e
self cleaning.
- we could look at a scheduled garbaage collection instead as a
simple solution.

PS. The Procedure currently uses SESSION tables, but this incurs a
(signiifcant)compile overhead on the first
call after connection, and seems to require multiple plans in
the Package cache 1 per connection.
This is a very big/complex Procedure that is required to execute
sub-second, so we are always looking
for ways to optimise it.

Thanks.

[Knut Stolze]

PaulR wrote:

Serge,

Many thanks for your explanation, I was wrongly assuming the result set
was 'protected' once the cursor was opened.

This explains exactly what I am seeing.

Unfortunately, I don't understand the construct you are using with the
CTEs ?
- Do you have a pointer I can go off and read?

Have a look here: http://tinyurl.com/cal57

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[PaulR]

Many Thanks,

Found this too - trying to absorb it into my relatively tiny head !

http://www-128.ibm.com/developerwork...dm-0411rielau/

[s.sathyaram]

I tried to repoduce Paul Reddin's problem, but am unable to (See below)

I fail to understand why the behaviour is different.

Any clues ?

Sathyaram

create table sptest(i int,j int)

0 record(s) affected

insert into sptest values(1,1),(2,2),(3,3),(4,4)

4 record(s) affected
CREATE PROCEDURE PROC_SPTEST (in p_i int)
result sets 1
language sql
begin
begin
declare c1 cursor with return for select i,j from sptest ;
open c1 ;
end ;
delete from sptest where i=p_i ;
end

0 record(s) affected

select * from sptest

I J
---- ----
1 1
2 2
3 3
4 4

4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

call proc_sptest(1)

I J
---- ----
1 1
2 2
3 3
4 4

4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

select * from sptest

I J
---- ----
2 2
3 3
4 4

3 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 10/ms]

[PaulR]

Serge,

This works a treat !! many thanks, I now understand your paper too :-)

[PaulR]

Hi,

It doesn't surprise me you weren't able to re-create this ...

as in our scenario we only see the problem 'sometimes' e.g seems to
depend on how many rows are returned by the SP.

and interestingly we only ever seem to lose 1 row (but maybe that is
coincidence too).

Thanks.