IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

With hold for cursors


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Manigandan Aravindhan

New User


Joined: 09 Oct 2007
Posts: 81
Location: India

PostPosted: Wed Aug 26, 2009 2:47 pm
Reply with quote

Dear Friends,
What is the difference between Using With hold option in cursor and not using it in cursor?

The one thing i know is that if we are using any commit statement then the cursors with not hold for will close automatically.......

Is there anything else like performance issues?
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Wed Aug 26, 2009 2:58 pm
Reply with quote

Hi,
While giving with hold for that mean the lock is been applied on the table and no it wont allow any other program to use the table,
when it come to performance if you use with hold even after the commit the cursor wont release the value while if you are not mentioning the with hold option then just after the commit the value will be released, this save the resource use and considered to be more better way to utilize the resources available
Back to top
View user's profile Send private message
arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Wed Aug 26, 2009 3:56 pm
Reply with quote

Quote:

When a COMMIT is issued by the program, open cursors are closed unless the WITH HOLD option is
coded for the cursor.

WITH HOLD prevents subsequent COMMITs from destroying the intermediate results table for the SELECT
statement, thereby saving positioning within the cursor. This technique will not hold the cursor position over
separate tasks in pseudo-conversational programs.
Back to top
View user's profile Send private message
arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Wed Aug 26, 2009 4:02 pm
Reply with quote

Also....
Quote:

Use Caution When Specifying WITH HOLD

Using the CURSOR WITH HOLD clause causes locks and claims to be held across commits. This capability can
increase the number of timeouts and affect availability. Before coding the WITH HOLD clause on a cursor, be
sure that the benefit gained by doing so is not negated by reduced availability.
Use the DSNZPARM RELCURHL to minimize these locks. Specify whether DB2 should, at COMMIT time, release
a page or row lock on which a cursor defined WITH HOLD is positioned. This lock is not necessary for
maintaining cursor position. YES is the default causing DB2 to release this lock after a COMMIT is issued.
Specifying RELCURHL YES can improve concurrency. If you choose NO, DB2 holds the lock for WITH HOLD
cursors after the COMMIT. This option is provided so that existing applications that rely on this lock can continue to
work correctly.


You can find many more thru google icon_smile.gif
Back to top
View user's profile Send private message
Manigandan Aravindhan

New User


Joined: 09 Oct 2007
Posts: 81
Location: India

PostPosted: Wed Aug 26, 2009 4:40 pm
Reply with quote

Thanks Ketan Varhade icon_smile.gif
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Wed Aug 26, 2009 5:15 pm
Reply with quote

You are welcome icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Positioned Deletes/Updates using curs... DB2 3
No new posts Any limit on usage of cursors ? DB2 1
No new posts Is there drawbacks in using DB2 rowse... DB2 11
No new posts Process file after 7 days of hold All Other Mainframe Topics 4
No new posts How LOAD PROGRAM and LOAD on HOLD is ... CICS 4
Search our Forums:

Back to Top