Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Select for update option declared in a cursor

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
siganesan

New User


Joined: 14 Feb 2007
Posts: 2
Location: Chennai

PostPosted: Thu Aug 20, 2009 5:51 pm    Post subject: Select for update option declared in a cursor
Reply with quote

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
View user's profile Send private message

Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Fri Aug 21, 2009 12:51 pm    Post subject: Reply to: Select for update option declared in a cursor
Reply with quote

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
View user's profile Send private message
siganesan

New User


Joined: 14 Feb 2007
Posts: 2
Location: Chennai

PostPosted: Fri Aug 21, 2009 2:27 pm    Post subject: Reply to: Select for update option declared in a cursor
Reply with quote

Hello Raghu,

Thanks a lot, your response is much helpful.
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Fri Aug 21, 2009 2:40 pm    Post subject: Reply to: Select for update option declared in a cursor
Reply with quote

You are Welcome

Raghu
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Fri Aug 21, 2009 8:16 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Mon Aug 24, 2009 1:12 pm    Post subject: Reply to: Select for update option declared in a cursor
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Aug 24, 2009 1:26 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts Release of Adabas Cursor mohitsethi All Other Mainframe Topics 1 Mon Feb 06, 2017 8:36 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us