Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 Process file after 7 days of hold charlessxavier All Other Mainframe Topics 4 Tue May 22, 2018 3:54 pm
No new posts -502 Error - Cursor already open anand jeyapaul DB2 7 Sun Oct 29, 2017 4:22 am
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am

Back to Top
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us