CURSOR WITH HOLD across COMMIT in batch COBOL - supported pattern?

Good afternoon,

We are refactoring a legacy COBOL batch program that processes customer adjustments row by row. The current design opens a cursor, fetches one row, updates a status column, and issues a COMMIT after each successful update.

The SQL declaration in the program is structured as follows:

sql
DECLARE CUST_ADJ CURSOR WITH HOLD FOR
  SELECT CUST_ID, ADJ_AMT, ADJ_STATUS
    FROM BILLING.ADJUSTMENTS
   WHERE ADJ_STATUS = 'PENDING'
   FOR UPDATE OF ADJ_STATUS

After the first COMMIT, the next FETCH returns SQLCODE +100 even though additional qualifying rows remain. The DB2 manual states:

A WITH HOLD cursor remains open across COMMIT points when the application explicitly commits work.

Thank you,
Elena

WITH HOLD is valid, but the cursor must be declared WITH HOLD before the first OPEN, and the plan must be bound with the correct isolation. Please confirm your bind uses ISOLATION(CS) or the level your shop standardizes on.

Also verify you are not implicitly closing the cursor via a second CONNECT or a full rollback path in the error paragraph.

Thank you for the prompt reply. Bind is ISOLATION(CS). I reviewed the error paragraph and found a ROLLBACK that executes on any non-zero SQLCODE, including benign warnings.

Follow-up question: would you recommend retaining row-level commits for restartability, or switching to commit every N rows with a checkpoint table?

For restartability at scale, we use a checkpoint table keyed by last processed CUST_ID rather than committing every row. Pattern:

sql
UPDATE CTRL.CHECKPOINT
   SET LAST_KEY = :WS-LAST-CUST-ID,
       ROWS_DONE = ROWS_DONE + 1
 WHERE JOB_NAME = 'ADJUST01';

This keeps the cursor semantics simpler and makes recovery auditable.

Understood. We will prototype the checkpoint approach in QA this week. I appreciate the guidance.