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
 

 

With hold for cursors

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: With hold for cursors
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    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 How LOAD PROGRAM and LOAD on HOLD is ... pkmurali CICS 4 Tue May 03, 2016 6:26 pm
No new posts How to hold a daily job for a monthly... anandgbe CA Products 8 Mon Mar 07, 2016 12:59 pm
No new posts How to place the HOLD ? raviudaya CA Products 3 Thu Aug 30, 2012 12:49 am
No new posts Cursor WITH HOLD option, gets closed ... genesis786 DB2 7 Fri Jul 06, 2012 6:22 pm
No new posts JOB submitted goes on HOLD even witho... Anuj Dhawan JCL & VSAM 10 Mon Feb 27, 2012 1:59 pm


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