I have a problem with a stored procedure.A cursor is declared with a select and is opened. I do bunch of inserts and updates inside the loop for the fetch of the cursor opened.And for the next fetch, the updated values should be ignored until the fetch loop is exited.

Any ideas for doing this??

Maybe if you post your SP, I might be able to help you. Also post your DB2 version and OS.

Andy

I dont have the access to the code at the moment.But I can give you the scenario

Declare cursor C1
Select col1,col2 from tab1;

Open C1
Loop
Fetch a row from tab1;

Do deletes and updates on the rows in tab1;

Close C1


Is there a way that the deletes and updates on tab1 inside the fetch loop can be ignored by the next fetch of the cursor??

Although very interested into the solution, I am also curious about the business/tech reason for this logic.

If like to fetch original data, then update/delete it. Why not use separated logic?

Anyway, DB2 provide feature as: INSERT/UDPATE/DELETE within SELECT.
These features(v8 and v9) will allow you to combine those data-change-stmt inside a query. You can search for 'data-change-table-reference' in a SQL ref book to find out the detail.

This aricle is for DB2/LUW(you can find similar one for db2/zos):
http://www.databasejournal.com/featu...le.php/3342211


Anyway, since I don't fully understand your purpose, above is just a friendly suggestion.

You could simply place the DELETE/UPDATE statements inside an IF statement and, thus, control when they will be executed.

in DB2 for z/OS there is the option to declare the cursor as
INSENSITIVE SCROLL.

The result set will be materialized in a temporary table and changes to the original data will not affect it.

Make sure your result set gets materialized one way or the other, e.g. by adding an "ORDER BY" which cannot be implemented by an index. (E.g., use more columns in your ORDER BY than there are in any existing index.)
Also, a GROUP BY on the primary key would help (but would "cost" a little more).

That was my first thought, too. But it is dangerous.
May be an index is added later ( e.g. to tune a different query ) or a later version of DB2 allows it to use an index for the chosen order by clause. In those cases the result set isn't materialized any longer and the behavior of the program changes without any warning.

Thanks for your suggestions.Can we try setting DB2_EVALUNCOMMITTED on to avoid this behaviour??Any thoughts??

Can we try setting DB2_EVALUNCOMMITTED on to avoid this behaviour??

I don't expect this will help.
Afaik, this only makes uncommitted changes from others visible; your own changes are always visible.
(I'm not familiar enough with DB2 for LUW, so I might be mistaken; this holds certainly for DB2 on z/OS.)

It is true for LUW also.

Andy

First I didn't understand what the OP tried to do. Thanks for figuring that out, umayer.

Basically, you can forget everything that is related to isolation levels (like DB2_EVALUNCOMMITTED etc.). Those things only deal with multiple, concurrent transactions and the visibility of data changes between those transactions. Naturally, you will always be able to see the data changes made in your own transaction.

A real solution is - as umayer said - to use the correct sensitivity for your scrollable cursor. Given your additional questions, I don't think you are working with DB2 z/OS where this feature is available, correct? Therefore, you have to find some sort of work-around, which means that you have to rewrite your query in such a way that the result set will be written to temp before the cursor works on it.

Another solution may be that you explain exactly the scenario so that we can understand why INSERT INTO ... SELECT ... (or MERGE statement) won't work. Maybe we can find a different way to address this application issue instead of trying to work around symptoms. (I don't mean to imply that you have a problem in the application!)

Thanks for your comments.After all the words from you , I rewrote the SQL and it works fine now..Thanks again