<mo*******@gmai l.comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.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.
