Updating record through cursor
Microsoft is conducting an online survey to understand your opinion of the Msdn Web site.
If you choose to participate, the online survey will be presented to you when you leave the Msdn Web site.
When you're using a cursor in SQL you use the FETCH statement to move the cursor to a particular record.
This part of the series examines the FETCH statement in more detail.
- How do I ensure that the row is locked during the above activity?
Sorry for the basic questions, but I'm not a database guy. Hi All, I've been tasked with writing a z/OS C program to read and update a table (DB/2 v8).
My question is this I am updating the qty in pos system everything is working fine but cursor is not holding the same updating record which i am updating untill i insert a next record cursor is moving to the next record cursor should be on 2222 record but moved to next record as I mentioned in uploaded.
"ABCE") 3) Write the incremented value back to the table In my code, I'm doing the following: EXEC SQL DECLARE C2 SENSITIVE STATIC SCROLL CURSOR FOR SELECT NEXT_MAILBOX_ID FROM BTMFE_MB_NEXT_AVL FOR UPDATE OF NEXT_MAILBOX_ID; EXEC SQL OPEN C2; EXEC SQL FETCH FROM C2 INTO : OLD_MAILBOXID; // the next value is calculated here and stored in : NEW_MAILBOXID // the new value is then written back to the table EXEC SQL UPDATE BTMFE_MB_NEXT_AVL SET NEXT_MAILBOX_ID = : NEW_MAILBOXID WHERE CURRENT OF C2; EXEC SQL CLOSE C2; It seems a little overkill to use a Cursor for something so simple, but our DBA recommended we use one.
The end of a transaction (commit or rollback) is completely independent of cursors.
So you have to use your own explicit COMMIT statement (or the respective API call).
Where a connection does exist is that all cursors are closed at ROLLBACK and non-holdable cursors are closed at COMMIT (holdable cursors are kept open after the commit).
-- Knut Stolze DB2 Information Integration Development IBM Germany Thanks, Mark. The following is the best solution and will avoid deadlocks: -- increment the sequence number and hold exclusive lock on the row UPDATE sequence_table SET seq_number = seq_number 1 WHERE seq_key = ?
This program (which will run as a Stored Proc) needs to implement a strategy for concurrency as the Stored Proc can be called concurrently by several different users.