DB2 WITH HOLD Close and Open of Cursors in COMMIT or ROLLBACK

NoSQL Interview QuestionsA cursor in an application programming plays crucial role. With cursor, we can retrieve bunch of rows matching to criteria of WHERE clause.

Cursor life cycle:

Declare => OPEN => Fetch => CLOSE

Declare-It is nothing but declaration, like working storage variable in COBOL program. But, it is not executable.

OPEN=> With open statement cursor execution will start. Then, FETCH and CLOSE follows.

CLOSE=> It closes all open cursors

A cursor is declared with WITH HOLD option, will be closed, when ROLLBACK is happened.

In the case of COMMIT, cursor will not be closed. So, you can continue process the table during next read.

WITHOUT HOLD – By default there is WITHOUT HOLD. Until you specify, in the declaration with WITH HOLD.

Even If you mention with WITH HOLD option, by default, after STOP RUN all the cursors will be released. CLOSE cursor anyway closes all cursors.

Which scenarios CURSOR WITH HOLD will not work

Let us see the details:

  • The connection associated with the cursor is in the release pending status.
  • The bind option DISCONNECT(AUTOMATIC) is in effect.
  • The environment is one in which the option WITH HOLD is ignored.

Why Cursor WITH HOLD does not work in CICS

Cursors that are declared with WITH HOLD in CICS® ,message-driven programs, will be closed after Sync point or ROLLBACK operation. Each time you need to re-open the Cursors.

So, Cursor with WITH HOLD option we usually do not give in CICS.

IMS DC programs, does not support WITH HOLD clause.

Recommended reading:

Advanced Programming with cursors


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Comments are closed.