Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
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 in cursor

Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message

New User

Joined: 18 Aug 2008
Posts: 50
Location: Bangalore

PostPosted: Wed Dec 29, 2010 6:22 pm    Post subject: WITH HOLD in cursor
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.

Back to top
View user's profile Send private message


Active User

Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Wed Dec 29, 2010 8:35 pm    Post subject:
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

New User

Joined: 23 Sep 2005
Posts: 91

PostPosted: Thu Dec 30, 2010 11:55 am    Post subject:
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    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 Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am
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 DB2 Stored Procedure - Dynamic SQL - ... GuyC DB2 2 Wed Feb 17, 2016 5:10 pm
No new posts ISREDIT - Position Cursor on the Comm... Tarique Anwer TSO/ISPF 5 Wed Feb 03, 2016 12:44 pm

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