473,854 Members | 1,580 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

delete statement after a RETURN CURSOR in SP ?

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,t able3

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.

Feb 28 '06 #1
8 3442
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,t able3

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
Feb 28 '06 #2
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
Feb 28 '06 #3
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)co mpile 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.

Mar 1 '06 #4
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
Mar 3 '06 #5
Many Thanks,

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

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

Mar 3 '06 #6
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]

Mar 3 '06 #7
Serge,

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

Mar 7 '06 #8
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.

Mar 7 '06 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
6195
by: Andrew Edwards | last post by:
The following function results in an infinite loop! After 5+ hours of debugging, I am still unable to decipher what I am incorrectly. Any assistance is greatly appreciated. Thanks in advance, Andrew ==========>Code<========== //--------------------------------------------------------------------
18
1765
by: deanbrown3d | last post by:
I mean, is this correct? try { Screen->Cursor = crHourglass; Do something bad return false; else return true; }
1
1954
by: johnb41 | last post by:
I have a datagrid that successfully allows editing and updating (with dataadapter.update command). I want the user to be able to edit the "text" in the datagrid, but i DON'T want the user to be able to DELETE rows. The OleDbCommandBuilder automatically creates the DELETE SQL statement. Is there any way I can disable it? If not, what approach should I be using? Thanks!
1
4119
by: heming_g | last post by:
i see in this forum to make this routine using "DELETE FROM (SELECT 1 FROM <name> WHERE <condition> FETCH FIRST <n> ROWS ONLY) Prepare this one ONCE then EXECUTE in the loop. " it works . but now i find it's a bad performance when the data is scattering and
8
2409
by: surya | last post by:
hi all i hava eetable that is , eename sal _______ ________ suresh 100000 ramsesh 100000 raja 100000 susjssj 100000 dkddkd 100000 jfdjfdjfd 100000
6
25011
by: ilo | last post by:
When I want to delete a data from a table that this tabl has a trigger and this trigger reached another tables to delete the data in cursor I have this messeage: DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. My trigger : CREATE TRIGGER ON . FOR DELETE
2
2325
by: mathon | last post by:
hello, i thought i create a new topic for that because i deal with a certain problem. I have implemented all methods for a LinkdeList (for my sequence class) and tested all correct. Now i only have problems with the remove method to delete a certain node from the list. my remove method looks like this: void sequence::remove_current( )
3
2489
by: ujjwaltrivedi | last post by:
Can I use any method to allocate /deallocate memory other than new/ delete operators? Actually the problem is while using delete operator in my ".sqc" files the application crashes. It seems the memory location is locked by DB and so it throws an 'unhandled exception'. Following is the code snippet from my application and now i dont wanna touch the basic design. Regards, UvT
8
2989
by: Michel Esber | last post by:
Hello, Env: DB2 V8 LUW FP16 running Linux create Table X (machine_id varchar(24) not null, ctime timestamp not null); create index iFoo on X (MACHINE_ID, CTIME) allow reverse scans; alter table X add primary key (MACHINE_ID, CTIME); Our C++ application inserts data into a table X using CLI array insert
0
9901
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9751
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11025
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10682
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10371
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9513
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5942
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4562
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3187
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.