DB2 Anti-Pattern: COMMIT and Cursor Issues

The two opposite commit mistakes

COMMIT makes the current unit of recovery durable and begins a new one. It also releases locks that no longer need to be retained, closes ordinary cursors, and establishes a useful recovery boundary. Those benefits do not mean “more commits are always better.” COMMIT itself requires coordination and logging work. More importantly, a commit is a promise: work before that point will not be undone by a later application rollback.

The correct boundary starts with business meaning. If transferring money requires a debit, a credit, and an audit entry, those changes normally belong in one coordinated unit of work. A COMMIT between the debit and credit is not a tuning improvement; it is a correctness defect. After preserving that atomic boundary, tune how many independent business units can safely share one commit.

Committing too often

A loop that updates one row and immediately commits repeats commit processing for every row. In high-volume batch, that can increase elapsed time and CPU and reduce the benefit of efficient sequential processing. It can also make recovery awkward: if row 7,001 fails, rows 1 through 7,000 are permanent. The program needs a durable way to identify exactly what completed, and replay must be idempotent or begin after a trustworthy restart key.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Anti-pattern when one order needs several related changes
UPDATE ACCOUNT
   SET BALANCE = BALANCE - :AMOUNT
 WHERE ACCOUNT_ID = :FROM_ACCOUNT;
 
COMMIT;  -- Dangerous boundary: the debit is now permanent
 
UPDATE ACCOUNT
   SET BALANCE = BALANCE + :AMOUNT
 WHERE ACCOUNT_ID = :TO_ACCOUNT;
 
INSERT INTO TRANSFER_AUDIT
       (TRANSFER_ID, FROM_ACCOUNT, TO_ACCOUNT, AMOUNT)
VALUES (:TRANSFER_ID, :FROM_ACCOUNT, :TO_ACCOUNT, :AMOUNT);
 
-- Better: commit only after the complete transfer succeeds
COMMIT;

A commit per row is not automatically wrong. If each row is an independent, complete message and the system requires immediate visibility, that boundary might be correct. The anti-pattern is using row count as a substitute for transaction design.

Committing too infrequently

The opposite loop processes millions of updates with no intermediate commit. Depending on isolation, access path, and statement type, locks can remain until commit and block other work. The unit of recovery accumulates log records. A timeout, cancellation, or abend can require a long rollback. Db2 restart and application restart also face a much larger scope. Long readers can cause concurrency problems too, especially when repeatable read semantics retain qualifying locks.

• Lock duration: commit is a primary point at which transaction locks are released. A long unit of work can turn otherwise short conflicts into timeouts.
• Rollback duration: more uncommitted change means more log work to undo after failure.
• Restart exposure: without a committed restart marker, a failed job may need to repeat an enormous range.
• Operational impact: long-running units of recovery complicate maintenance, shutdown, utility coordination, and incident recovery.

Choose a commit interval from evidence

Advice such as “commit every 1,000 rows” is only a starting hypothesis. One row could be a tiny status change or a large LOB update with many index changes. One thousand rows could take a second or an hour. Choose a maximum business-unit count or elapsed interval, then measure log volume, lock waits, commit cost, rollback time, and restart behavior in a representative environment. Never split a single atomic business operation merely to hit the number.

A good restart design records the last fully committed key, not merely the last fetched key. If the program fetches customer 500, updates through customer 480, and then fails, saving 500 as the restart value loses twenty customers. Save the restart row in the same unit of work as the corresponding business updates when the design permits it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Process an ordered, restartable range
SELECT WORK_ID, CUSTOMER_ID, AMOUNT
  FROM PAYMENT_WORK
 WHERE WORK_ID > :LAST_COMMITTED_WORK_ID
   AND STATUS = 'READY'
 ORDER BY WORK_ID;
 
-- After a complete chunk has succeeded:
UPDATE BATCH_RESTART
   SET LAST_WORK_ID = :LAST_SUCCESSFUL_WORK_ID,
       UPDATED_TS   = CURRENT TIMESTAMP
 WHERE JOB_NAME = :JOB_NAME;
 
COMMIT;
 
-- The durable restart key now describes durable business work.

Cursor lifecycle and SQLCODE -501

A cursor has state. DECLARE describes it, OPEN creates the active result, FETCH advances through rows, and CLOSE ends it. Host variables in its search predicates are evaluated when the cursor opens. A later FETCH is valid only while that cursor remains open.

A common defect is OPEN, FETCH, update, COMMIT, then FETCH again using an ordinary cursor. COMMIT closed that cursor, so the next FETCH can return SQLCODE -501. Repeating FETCH does not repair the state. The program must intentionally reopen and resume, or declare WITH HOLD when retaining the cursor is appropriate.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DECLARE C_WORK CURSOR FOR
    SELECT WORK_ID, AMOUNT
      FROM PAYMENT_WORK
     WHERE WORK_ID > :LAST_KEY
     ORDER BY WORK_ID;
 
OPEN C_WORK;
FETCH C_WORK INTO :WORK_ID, :AMOUNT;
 
UPDATE PAYMENT_WORK
   SET STATUS = 'DONE'
 WHERE WORK_ID = :WORK_ID;
 
COMMIT;
 
-- Anti-pattern: C_WORK was not declared WITH HOLD.
-- This FETCH can receive SQLCODE -501.
FETCH C_WORK INTO :WORK_ID, :AMOUNT;

SQLCODE -501 can also follow a missing OPEN, an earlier CLOSE, a second CLOSE, a rollback, or an error path that invalidated cursor state. Build control flow around states rather than scattering OPEN and CLOSE statements across branches. Check SQLCODE after every SQL statement before changing the application's idea of the state.

WITH HOLD semantics and costs

DECLARE CURSOR WITH HOLD requests that a cursor remain open across a successful commit. The next FETCH can continue the result rather than reopening from the start. This is useful for a batch reader that commits processed chunks while scanning an ordered input. It is not a general instruction to add WITH HOLD to every cursor.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE C_WORK CURSOR WITH HOLD FOR
    SELECT WORK_ID, CUSTOMER_ID, AMOUNT
      FROM PAYMENT_WORK
     WHERE STATUS = 'READY'
     ORDER BY WORK_ID;
 
OPEN C_WORK;
 
-- FETCH and process a complete chunk.
FETCH C_WORK INTO :WORK_ID, :CUSTOMER_ID, :AMOUNT;
 
-- COMMIT preserves C_WORK because it is WITH HOLD.
COMMIT;
 
-- Continue with the next row, after checking COMMIT succeeded.
FETCH C_WORK INTO :WORK_ID, :CUSTOMER_ID, :AMOUNT;

WITH HOLD preserves cursor state, not the atomicity of all rows processed through that cursor. Earlier chunks are already committed. It also does not promise that every lock acquired before commit remains held. Concurrent programs can change data after locks are released, and the exact sensitivity of what the cursor observes depends on cursor attributes, isolation, access path, and Db2 rules.

• A held cursor keeps Db2 and application resources across commit, so close it promptly when processing ends.
• A full rollback or error can close a held cursor. Do not assume WITH HOLD survives every unsuccessful boundary.
• LOB locators and other resources can have commit lifetimes different from the held cursor itself; follow the interface documentation.
• Commit still divides the business work. WITH HOLD changes cursor lifecycle, not transaction history.

Positioned UPDATE and commit boundaries

A positioned update uses WHERE CURRENT OF to modify the row at an updateable cursor's current position. The cursor declaration must support update, the FETCH must establish a valid current row, and the positioned statement must run while that position is usable. The safest pattern is FETCH, validate, positioned UPDATE, check SQLCODE, and only then commit the complete business unit.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE C_ORDER CURSOR FOR
    SELECT STATUS, UPDATED_TS
      FROM CUSTOMER_ORDER
     WHERE ORDER_ID = :ORDER_ID
       FOR UPDATE OF STATUS, UPDATED_TS;
 
OPEN C_ORDER;
FETCH C_ORDER INTO :STATUS, :UPDATED_TS;
 
UPDATE CUSTOMER_ORDER
   SET STATUS     = 'COMPLETE',
       UPDATED_TS = CURRENT TIMESTAMP
 WHERE CURRENT OF C_ORDER;
 
-- Check SQLCODE before ending the unit of work.
COMMIT;

Do not fetch a row, commit merely to release its lock, and then assume a positioned update is protected from concurrent change. Another transaction may change or delete the row after the commit. If processing must cross a commit, use a stable key and a searched UPDATE with an optimistic predicate such as the previously read timestamp or version. Then verify the affected-row count and handle a concurrent-change result.

1
2
3
4
5
6
7
8
9
-- Revalidate after a commit instead of trusting an old cursor position
UPDATE CUSTOMER_ORDER
   SET STATUS     = 'COMPLETE',
       UPDATED_TS = CURRENT TIMESTAMP
 WHERE ORDER_ID   = :ORDER_ID
   AND UPDATED_TS = :ORIGINAL_UPDATED_TS;
 
-- If no row was updated, another unit of work changed or removed it.
-- Follow the application's conflict policy; do not silently overwrite.

Deadlocks, timeouts, -911, and -913

A deadlock occurs when units of work wait in a cycle. A timeout occurs when a requester waits beyond the configured limit. Db2 chooses or reports a victim so the conflict can end. The application must not treat every negative SQLCODE as “retry the last statement” because the unit-of-work outcome differs.

SQLCODE -911 states that the current unit of work was rolled back. Any successful changes since its beginning are no longer committed work, and cursor assumptions must be reset. Retrying only the failed UPDATE can omit prerequisite reads or changes. Retry the entire business transaction from a known boundary, with a limit, delay or backoff, and idempotency protection where duplicates are possible.

SQLCODE -913 reports an unsuccessful execution caused by a deadlock, timeout, or related resource condition, but it does not carry the same general statement that Db2 rolled back the complete current unit of work. The program must inspect SQLSTATE, SQLERRMC or GET DIAGNOSTICS information, reason code, resource details, and its attachment environment. It may need to issue rollback through the proper coordinator before a safe retry. Never continue as though the failed statement succeeded.

1
2
3
4
5
6
7
8
9
10
11
IF SQLCODE = -911
   reset cursor and in-memory transaction state
   restart the complete business unit from the last commit
   apply a bounded retry policy
ELSE IF SQLCODE = -913
   collect reason and resource diagnostics
   determine whether the transaction manager requires rollback
   restart only from a documented, safe boundary
ELSE IF SQLCODE = -501
   fix cursor lifecycle; do not treat it as a lock retry
END-IF

CICS and IMS own broader boundaries

In a simple batch or TSO-style Db2 application, SQL COMMIT and ROLLBACK can define the Db2 unit of recovery. Under CICS, a unit of work can include Db2 changes, VSAM updates, queues, and other recoverable resources. CICS coordinates them with EXEC CICS SYNCPOINT or task-boundary behavior. Issuing an arbitrary SQL COMMIT would not be a sound way to coordinate the complete transaction.

IMS similarly owns checkpoint and synchronization behavior for message processing or batch message processing applications. Use the applicable IMS CHKP, SYNC, ROLL, or ROLB design and understand how it affects Db2 cursors and restart data. Exact -911/-913 presentation and rollback behavior can depend on the attachment and transaction manager, which is why production code must follow the shop's documented interface rather than a generic batch-only example.

• Align the Db2 boundary with non-Db2 resources participating in the business event.
• Use the transaction manager's synchronization API in CICS or IMS.
• Test timeout, deadlock, abend, and restart behavior in the actual attachment environment.
• Confirm whether held cursors are appropriate and supported across that syncpoint design.

A safer batch design

A robust batch loop separates input position, business completion, and commit state. Read rows in a deterministic order based on a unique or tie-broken key. Process only complete business units. After a bounded chunk succeeds, save the last completed key and commit. If COMMIT fails, do not advance the durable restart key in memory or externally. On restart, begin after the last key whose business work and marker committed together.

• Choose a deterministic order; a non-unique timestamp alone is not a safe restart key.
• Make replay harmless through status predicates, unique request identifiers, or idempotent logic.
• Close held cursors on normal completion and every controlled exit path.
• Keep retry counts bounded so a hot resource does not create an endless loop.
• Record SQLCODE, SQLSTATE, reason code, resource name, retry count, and last committed key.
• Measure commit latency, lock waits, timeouts, rollback time, log volume, and rows per commit.

Explain It Like I'm Five

Imagine moving blocks from one box to another while writing checkpoints in a notebook. If you write “finished” after every tiny block, you spend most of your time writing. If you wait until all day's blocks are moved, one spill makes you redo the whole day. Instead, move a sensible complete group, write down the last safely moved group, and continue. A cursor is your finger pointing at the next block. A normal COMMIT removes your finger; WITH HOLD keeps the finger there. But the notebook checkpoint still means everything before it is final, so put it only where restarting will be safe.

Exercises

• A batch updates 200,000 independent invoices and commits only at job end. Design an experiment to compare 100, 1,000, and 5,000 invoices per commit without splitting one invoice's related updates.
• Explain why FETCH, COMMIT, FETCH receives -501 for a cursor without WITH HOLD, then show two valid designs: held cursor and close/reopen with a restart key.
• Design a restart table for input ordered by (BUSINESS_DATE, WORK_ID). State exactly when the two-part key is updated and committed.
• Compare the recovery action for -911 with the diagnostic and rollback decision needed for -913. Include a bounded retry policy.
• Rewrite a FETCH, COMMIT, WHERE CURRENT OF sequence as a searched UPDATE that checks an original version or timestamp.
• Map a CICS transaction that updates Db2 and VSAM. Identify why EXEC CICS SYNCPOINT, rather than an independent SQL COMMIT, defines the coordinated boundary.