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:

• Advanced Programming with cursors

Keep Reading