DB2: Cursor WITH HOLD, CLOSE, OPEN Ideas

A cursor in an application programming plays crucial role. With cursor you can get matching rows for the condition given in the 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.

When 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:

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.