By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,417 Members | 1,825 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,417 IT Pros & Developers. It's quick & easy.

Returning rows after select for update and update current of

P: n/a
Hi,

I'm using stored procedures in DB2 UDB 8.1.2. In this stored, I execute
a select for update command, opening a cursor. Then I update the rows
using fetch and current of. The problem is that I want to return the
rows for my Java application. But if I set the cursor of the select for
WITH RETURN, I get the values, but I cant execute the update. If I dont
use WITH return, I execute the update, but I cant get the result set.
Any ideas.

Thank you

Oct 19 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
<mo*******@gmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
Hi,

I'm using stored procedures in DB2 UDB 8.1.2. In this stored, I execute
a select for update command, opening a cursor. Then I update the rows
using fetch and current of. The problem is that I want to return the
rows for my Java application. But if I set the cursor of the select for
WITH RETURN, I get the values, but I cant execute the update. If I dont
use WITH return, I execute the update, but I cant get the result set.
Any ideas.

Thank you
In a similar situation, I did the following:

I first get a unique timestamp from DB2. Then when I update each row where
current of the cursor (cursor not defined as WITH RETURN), I use the unique
timestamp to update one of the columns (such as CHANGED_TS column defined as
a timestamp).

Then after I close the cursor above, I open another cursor WITH RETURN to
select the rows with the timestamp that I obtained in the beginning and that
was used for each row I updated.

Another option is to define a temporary table and insert the updated rows
into the temp table. Then at the end, open a cursor WITH RETURN with the
results of the temp table.

For my particular situation, the first option tested faster than the temp
table, but that would depend on the number of rows in the table, whether you
have an index on the timestamp column, bufferpool hit ratio, and other
related factors.
Oct 19 '06 #2

P: n/a
Can you place the UPDATE inside the cursor?

DECLARE cur CURSOR WITH RETURN FOR
SELECT * FROM NEW TABLE(UPDATE T SET ....)

Works in DB2 V8.1.4 for LUW and up
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 19 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.