CICS and CURSOR WITH HOLD option

Use CURSOR WITH HOLD in your CICS® program to keep the cursor open and in position during a SYNCPOINT.

The WITH HOLD option on a CURSOR declaration in a CICS program causes the following effects during a SYNCPOINT:
  • The cursor is kept open.
  • The cursor remains in position after the last row which was retrieved, and before the next row in the results table.
  • Dynamic SQL statements are still prepared.

All locks are released, except for those required to maintain the cursor's position. Any exclusive page locks are downgraded to shared locks.

In conversational CICS applications, you can use DECLARE CURSOR...WITH HOLD to request that the cursor is not closed at syncpoint time. However, all cursors are always closed at end of task (EOT) and on SYNCPOINT ROLLBACK. Across EOTs, a cursor declared WITH HOLD must be reopened and repositioned just as if the WITH HOLD option were not specified. The scope of the held cursor is a single task.

In summary:
  • The next FETCH following a syncpoint must come from the same task.
  • You cannot hold a cursor across end of task.
  • Therefore, cursors are not held across the EOT portions of pseudoconversational transactions.

If you try to hold a cursor across EOT, the cursor is closed and you get an SQLCODE -501 when you execute the next FETCH. The precompiler cannot detect this and you do not get a warning message notifying you of this situation.

In general, threads can become candidates for reuse at each syncpoint. When you use DECLARE CURSOR...WITH HOLD in the CICS applications, consider the following recommendations:
  • Close held cursors as soon as they are no longer needed. Once all held cursors are closed, the syncpoint can free the thread for thread reuse.
  • Always close held cursors before EOT. If you do not close your held cursors, the CICS Db2® attachment facility forces signon to restore the thread to the initial state, and this incurs additional processor time.
Note:

If SHARELOCKS(YES) is specified on a DB2ENTRY definition, CICS can pass an XID to Db2 and instruct Db2 to share locks between threads that pass the same XID. The passing of an XID involves a partial signon to Db2 for each unit of work (UOW). This action closes cursors, so held cursors across syncpoints are not supported. Applications must reposition cursors after a syncpoint.

Using the same XID, other threads that originate from other CICS regions or from other transaction managers such as IMS TM can access Db2 in the same global UOW. This avoids having to deal with UOW affinities. The XID token is not used for recovery between CICS and Db2.