View previous topic :: View next topic
|
Author |
Message |
sandhyaimmadi
New User
Joined: 18 Aug 2008 Posts: 50 Location: Bangalore
|
|
|
|
Hi ,
I want to know the specific use of WITH HOLD clause in DB2.
I know it will retain the cursor position after commit.
I would like to know if there is any other specific functionality.
Thanks,
Sandhya. |
|
Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009 Posts: 365 Location: Denmark
|
|
|
|
Does the SQL reference manual specify any other side effects?
You should be aware that declaring cursors with hold can be ignored in some environments. |
|
Back to top |
|
|
mlp
New User
Joined: 23 Sep 2005 Posts: 91
|
|
|
|
The only purpose of "WITH HOLD" is to keep the result-set prepered by cursor as it is, even after COMMIT. The only way to close the result set is to close the cursor. Apart from that I dont think "WITH HOLD" represents any other functionality.
Also below are some of the lines from SQL reference regarding "WITH HOLD" option.
At the end of a unit of work, all cursors that belong to an application
process and that were declared without the WITH HOLD option are
implicitly closed.
All locks acquired by the unit of work subsequent to its initiation are released,
except necessary locks for open cursors that are declared WITH HOLD.
Different options specified in the
DISCONNECT precompiler option affect intentionally ending a connection. If
set to AUTOMATIC, then all connections are ended. If set to CONDITIONAL,
then all connections that do not have open WITH HOLD cursors are ended.
Maintains resources across multiple units of work. The effect of the WITH
HOLD cursor attribute is as follows:
For units of work ending with COMMIT:
– Open cursors defined WITH HOLD remain open. The cursor is
positioned before the next logical row of the results table.
If a DISCONNECT statement is issued after a COMMIT statement
for a connection with WITH HOLD cursors, the held cursors must be
explicitly closed or the connection will be assumed to have
performed work (simply by having open WITH HELD cursors even
though no SQL statements were issued) and the DISCONNECT
statement will fail.
– All locks are released, except locks protecting the current cursor
position of open WITH HOLD cursors. The locks held include the
locks on the table, and for parallel environments, the locks on rows
where the cursors are currently positioned. Locks on packages and
dynamic SQL sections (if any) are held.
– Valid operations on cursors defined WITH HOLD immediately
following a COMMIT request are:
- FETCH: Fetches the next row of the cursor.
- CLOSE: Closes the cursor.
– UPDATE and DELETE CURRENT OF CURSOR are valid only for
rows that are fetched within the same unit of work.
– LOB locators are freed.
For units of work ending with ROLLBACK:
– All open cursors are closed.
– All locks acquired during the unit of work are released.
– LOB locators are freed.
For special COMMIT case:
– Packages may be recreated either explicitly, by binding the package, |
|
Back to top |
|
|
|