View previous topic :: View next topic
|
Author |
Message |
Manigandan Aravindhan
New User
Joined: 09 Oct 2007 Posts: 81 Location: India
|
|
|
|
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 |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009 Posts: 197 Location: Mumbai
|
|
|
|
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 |
|
|
arvind.m
Active User
Joined: 28 Aug 2008 Posts: 205 Location: Hyderabad
|
|
|
|
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 |
|
|
arvind.m
Active User
Joined: 28 Aug 2008 Posts: 205 Location: Hyderabad
|
|
|
|
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 |
|
Back to top |
|
|
Manigandan Aravindhan
New User
Joined: 09 Oct 2007 Posts: 81 Location: India
|
|
|
|
Thanks Ketan Varhade |
|
Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009 Posts: 197 Location: Mumbai
|
|
|
|
You are welcome |
|
Back to top |
|
|
|