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, 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.


dfhtk67.html | Timestamp icon Last updated: Monday, 5 June 2017