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

WITH HOLD in cursor


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

New User


Joined: 18 Aug 2008
Posts: 50
Location: Bangalore

PostPosted: Wed Dec 29, 2010 6:22 pm
Reply with quote

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

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Wed Dec 29, 2010 8:35 pm
Reply with quote

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

New User


Joined: 23 Sep 2005
Posts: 91

PostPosted: Thu Dec 30, 2010 11:55 am
Reply with quote

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
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 Is SQLCODE -811 possible while fetchi... DB2 1
No new posts Restart logic by using cursor name in... DB2 1
No new posts Seeking Resolution for SQKCODE -991 o... DB2 2
No new posts Multiple rows within Cursor when Coun... DB2 14
No new posts Dynamic cursor name in Cobol program COBOL Programming 1
Search our Forums:

Back to Top