|
View previous topic :: View next topic
|
| Author |
Message |
siganesan
New User
Joined: 14 Feb 2007
Posts: 2
Location: Chennai
|
|
|
|
Hi,
I have a cursor declared with 'FOR UPDATE' option. I want to know whether the lock on the row will stay until we commit the unit of work or would the lock be released when we close the cursor. Can anyone please advise me.
Below is my sequence of work.
Module A:
Cursor open --> fetch --> update --> cursor close
Module B:
input record validation and other db2 updates --> call Module A --> other functional processes and updates --> commit.
Thanks
SRanjani |
|
| Back to top |
|
 |
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
Hi siganesan
When the FETCH statement is executed, your program gets a "U" lock on the object. It can be ROW, PAGE, TABLE or TABLESPACE depending on the LOCKSIZE you specified at the time of TABLESPACE creation. If it is ROW, that ROW will be under "U" lock. And if your program executes an UPDATE statement, then the "U" lock will be converted to "X" lock. This lock will be held until the program issues a COMMIT/ROLLBACK statement or at plan deallocation. This feature will depends on the BIND parameter RELEASE. Closing of CURSOR will not end a UOW. So there is not question of COMMIT or ROLLBACK.
Remember when COMMIT or ROLLBACK is issued, all the open CURSORs will be closed.
Hope this will help you
Regards
Raghu |
|
| Back to top |
|
|
siganesan
New User
Joined: 14 Feb 2007
Posts: 2
Location: Chennai
|
|
|
|
Hello Raghu,
Thanks a lot, your response is much helpful. |
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
You are Welcome
Raghu |
|
| Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
| Quote: |
| Remember when COMMIT or ROLLBACK is issued, all the open CURSORs will be closed. |
unless the WITH HOLD option is used, in which case the cursor remains open, right? |
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
Hi Terry
Yes, CURSOR with WITH HOLD option will not CLOSE even when a CLOSE CURSOR statement is issued(In Batch process). In CICS a CURSOR declared with WITH HOLD option will close when a RETRUN statement is issued.
Regards
Raghu |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
whether the lock on the row will stay until we commit the unit of work or would the lock be released when we close the cursor. Can anyone please advise me.
|
| DB2®DB2 Universal Database for z/OSApplication VSN 8 Application Programming Guide wrote: |
Close cursors: If you define a cursor using the WITH HOLD option, the locks it needs can be held past a commit point. Use the CLOSE CURSOR statement as soon as possible in your program to cause those locks to be released and the resources they hold to be freed at the first commit point that follows the CLOSE CURSOR statement. Whether page or row locks are held for WITH HOLD cursors is controlled by the RELEASE LOCKS parameter on installation panel DSNTIP4. Closing cursors is particularly important in a distributed environment. |
|
|
| Back to top |
|
|
|
|
