Blog › ICP guides

IBM Db2 developer on retainer: COBOL embedded SQL FOR UPDATE OF cursor without intermediate COMMIT accumulates 847,000 Db2 row-level X locks, IBM Db2 for z/OS developer, Db2 batch developer on monthly retainer

October 10, 2026 · ~15 min read

An IBM Db2 developer was maintaining the nightly investment position closing batch program for a regional bank running on an IBM z15 mainframe (IBM z/OS 2.5, IBM Db2 for z/OS 13, subsystem DBRP). The bank’s investment operations platform maintained 12 million open positions across equity, fixed income, and derivatives portfolios. Each night, a batch program processed the day’s position closings: recording final prices, calculating profit and loss, and updating position statuses from PENDING_CLOSE to CLOSED in the INVEST_POSITIONS table (the central fact table for the investment operations platform, 12 million rows). The batch program was INVUPD01, a COBOL program with embedded SQL (Db2 host variable declarations in the WORKING-STORAGE section, DECLARE CURSOR and DML statements in the PROCEDURE DIVISION), executing in JCL job INVUPD STEP010. On a typical night, INVUPD01 processed 847,000 PENDING_CLOSE positions — roughly 7% of the total position table — over a 3.5-hour batch window beginning at midnight. The concurrent online system was a CICS-based wealth management inquiry platform whose primary query transaction WPQA (Wealth Position Query Application, program WPMAIN) allowed 24-hour wealth management phone service staff to look up current position statuses and closing prices for client inquiries.

INVUPD01 declared its working cursor as: EXEC SQL DECLARE INVPOS CURSOR WITH HOLD FOR SELECT POSN_ID, POSN_STATUS, POSN_QTY, POSN_COST FROM INVEST_POSITIONS WHERE POSN_STATUS = 'PENDING_CLOSE' FOR UPDATE OF POSN_STATUS, POSN_QTY, POSN_COST END-EXEC. The FOR UPDATE OF clause is the Db2 construct that designates specific columns as updateable via UPDATE CURRENT OF positioned update statements. A cursor declared FOR UPDATE OF causes Db2 to acquire an exclusive (X-type) row lock on each row as it is fetched and updated via UPDATE CURRENT OF. The WITH HOLD option preserves the cursor’s open state and position across COMMIT points — the cursor does not need to be re-opened after each COMMIT, and it repositions itself correctly after COMMIT on the next FETCH. The processing loop in INVUPD01 was: open INVPOS cursor; enter FETCH loop — EXEC SQL FETCH INVPOS INTO :WS-POSN-ID, :WS-POSN-STATUS, :WS-POSN-QTY, :WS-POSN-COST END-EXEC; if SQLCODE = +100 (end of cursor), exit loop; calculate closing P&L (multiplied by current exchange rate, applied overnight pricing adjustment); EXEC SQL UPDATE CURRENT OF INVPOS SET POSN_STATUS = 'CLOSED', POSN_QTY = :WS-NEW-QTY, POSN_COST = :WS-CLOSING-COST END-EXEC; loop back to FETCH. The loop contained no EXEC SQL COMMIT WORK END-EXEC statement.

The absence of an intermediate COMMIT in the FETCH loop is the root cause of the contention. In IBM Db2 for z/OS, a row updated via UPDATE CURRENT OF acquires an exclusive X lock on that row’s page (with page-level locking) or the row itself (with row-level locking, DSNZPARM NUMLKTS and NUMLKUS permitting). This X lock is held for the duration of the current unit of work (UOW) — from the first SQL DML statement after the most recent COMMIT (or program start) until the next COMMIT or ROLLBACK. With no COMMIT in the FETCH loop, INVUPD01’s unit of work begins at cursor open and ends at program termination (implicit COMMIT on normal GOBACK). Over 3.5 hours, INVUPD01 processes 847,000 rows one by one, and after each UPDATE CURRENT OF, the X lock on that row is added to INVUPD01’s lock list for the current UOW. After 3.5 hours of processing, INVUPD01 holds X locks on all 847,000 updated INVEST_POSITIONS rows simultaneously. The DSNZPARM parameter NUMLKUS (maximum locks per user) was set to 0 (unlimited) in the bank’s Db2 subsystem, meaning INVUPD01 could accumulate locks without hitting an escalation threshold — which also meant there was no automatic escalation to a table lock that would have made the contention visible in monitoring.

At 02:47 AM on the night of the incident, a wealth management phone representative received a call from an institutional client asking about closing prices for eight equity positions in account WMC-0087642-00. The representative entered the account number into the WPQA transaction, which invoked the CICS program WPMAIN. WPMAIN issued EXEC SQL SELECT POSN_ID, POSN_STATUS, POSN_COST, POSN_PRICING_DATE FROM INVEST_POSITIONS WHERE ACCT_NBR = :WS-ACCT-NBR ORDER BY POSN_ID END-EXEC — a read-only SELECT (no FOR UPDATE OF, no write access) at the CICS transaction’s default isolation level of Cursor Stability (CS). Of the eight positions for account WMC-0087642-00, three (POSN_IDs POSN-2024-08742, POSN-2024-08743, POSN-2024-08744) had already been processed by INVUPD01 earlier in the night and held outstanding Db2 X locks as part of INVUPD01’s uncommitted unit of work. Under CS isolation, a read-only SELECT must wait for any X lock on the row to be released before it can read the current committed value. Db2 suspended the WPMAIN thread, waiting for INVUPD01’s X locks to release. The Db2 subsystem DSNZPARM IRLMRWT parameter (IRLM resource wait time) was set to 60 seconds. After 60 seconds, Db2 timed out the WPMAIN thread and returned SQLCODE -911 REASON 00C90088 (lock timeout, reason code 00C90088 = X'00C90088' corresponds to LOCK TIMEOUT in Db2 for z/OS) to the WPMAIN EXEC SQL SELECT statement.

The CICS program WPMAIN checked SQLCODE after the SELECT: its error handling evaluated SQLCODE = -502 (cursor not open), SQLCODE = -803 (duplicate key on INSERT), and SQLCODE = 0 (success). It did not evaluate SQLCODE = -911. With -911 falling through all handled conditions, the COBOL program attempted to move data from the host variable area into the display map fields — the host variables contained binary zeros because the SELECT returned no data (SQLCA was populated with the -911 error, not row data). The MOVE of binary zeros into a field defined as PIC 9(12)V99 COMP-3 (packed decimal) caused a data exception (ABEND S0C7 equivalently, but in the CICS context the ABEND code was AEY9 — CICS’s task-level ABEND code for an unhandled SQL exception in a Db2-connected CICS program). The wealth management representative’s terminal displayed “CICS system error — please try again”. The representative could not retrieve the client’s position data; the client had to be called back. The AEY9 ABEND rate during INVUPD01’s 3.5-hour run window was 8 to 12 per night, corresponding to WPQA queries that happened to request positions already processed by INVUPD01 but not yet committed.

IBM Db2 for z/OS in 2026: the relational database backbone of banking, insurance, and capital markets

IBM Db2 for z/OS is the primary relational database management system for IBM z/OS mainframe applications at major U.S. and European commercial banks, investment banks, insurance carriers, government agencies, and airlines. IBM Db2 12 for z/OS entered General Availability in October 2016 and reached end of IBM service in September 2024 for organizations not enabled at the highest function level. IBM Db2 13 for z/OS entered General Availability in April 2022 and is the current deployed version, with function levels FL501 through FL510 activated progressively as organizations validate compatibility. Most major financial services organizations have migrated their Db2 subsystems to Db2 13 and are activating function levels FL504–FL506 (AI-powered query acceleration, enhanced JSON, temporal data improvements) in 2025–2026 planning windows. Db2 for z/OS runs concurrently with CICS (IBM CICS TS 6.1, the online transaction processing middleware) and with IMS (IBM IMS 15, the hierarchical database and transaction manager), sharing z/OS address spaces and memory, making lock interactions between batch Db2 jobs and online CICS-Db2 transactions a routine operational concern for any organization running 24/7 transaction processing on z/OS.

The IBM Db2 developer’s role on a monthly retainer for a bank’s investment operations platform typically covers three distinct areas. First, COBOL and PL/I embedded SQL program maintenance: maintaining and optimizing the SQL embedded in application programs — DECLARE CURSOR definitions, FETCH loop structures, UPDATE and INSERT DML, COMMIT and ROLLBACK placement, host variable declarations and data type alignment, SQLCA checking and SQLCODE handling. Second, Db2 object and utility management: running RUNSTATS to refresh optimizer statistics after large batch loads, scheduling REORG to reclaim extents and restore row clustering after insert-heavy batches, COPY for online tablespace backups, RECOVER for point-in-time restore after data corruption, CHECK INDEX and CHECK DATA for referential integrity validation, LOAD for bulk initial loads or nightly extracts from upstream VSAM or IMS sources. Third, BIND and REBIND management: maintaining the BIND PACKAGE definitions that compile embedded SQL programs (resolving SQL to access paths), triggering REBIND after RUNSTATS updates to allow the optimizer to adopt new access paths, managing BIND isolation level (CS, RS, RR, UR) and CURRENTDATA options that directly control whether read-only cursors hold locks during navigation.

The COMMIT placement in FETCH loops is the most critical application-level decision for Db2 batch programs that update large tables. The principle: a FOR UPDATE OF cursor UPDATE CURRENT OF statement acquires an X lock on the updated row, and that X lock is held for the entire current unit of work — until the next COMMIT or ROLLBACK. With no intermediate COMMIT, a program that processes 847,000 rows holds 847,000 X locks for the entire run. With COMMIT every 1,000 rows, the program holds a maximum of 1,000 X locks at any time, releasing them every ~1.5 seconds (the time to process 1,000 rows at INVUPD01’s average processing rate of 670 rows per second). The WITH HOLD cursor option is the mechanism that makes intermediate COMMIT feasible for a FOR UPDATE OF cursor: a cursor declared WITH HOLD is not closed by a COMMIT — it remains open and positioned at the row after the most recently fetched row. When INVUPD01 issues EXEC SQL COMMIT WORK END-EXEC mid-loop, the INVPOS cursor remains open, and the next EXEC SQL FETCH INVPOS retrieves the row immediately following the last-fetched row, exactly as if the COMMIT had not occurred. The tradeoff: if the program ABENDs after a COMMIT has been issued, only the uncommitted portion (at most 1,000 rows) needs to be reprocessed — the committed batches are already in the database. This requires the program to be designed for restartability at the COMMIT boundary (typically by storing the last committed POSN_ID in a restart control table so the cursor’s WHERE clause can skip already-committed rows on restart).

Db2 lock diagnosis: DISPLAY THREAD, accounting trace, and DSNZPARM

The Db2 DISPLAY THREAD command is the primary diagnostic for active lock contention. -DISPLAY THREAD(*) TYPE(ACTIVE) DETAIL issued at the z/OS console (or via SDSF with the appropriate RACF authority) lists all active Db2 threads with their lock accumulation counts, current SQL statement, elapsed time, and resource consumption. For INVUPD01, the DISPLAY THREAD output showed: PLAN=INVUPD CORRID=INVUPD01 STATUS=ACTIVE ELAPSED=03:22:14 X-LOCK-ROWS=847212 CURRENT-SQL=UPDATE CURRENT OF INVPOS — 847,212 exclusive row locks held, 3 hours 22 minutes of elapsed time, currently executing an UPDATE CURRENT OF. The WPMAIN thread showed: PLAN=WPQA CORRID=WPMAIN STATUS=LOCK-WAIT LATCH-WAIT=NO LOCK-RESRC=INVEST_POSITIONS.POSN-2024-08742 LOCK-TYPE=S LOCK-WAIT-SEC=57 — WPMAIN waiting for an S (shared) lock on a specific INVEST_POSITIONS row for 57 seconds, approaching the 60-second IRLMRWT timeout. The Db2 accounting trace (IFCID 1, 2, and 3) provides historical lock suspension data: IFCID 1 records per-request lock suspend time; IFCID 223 records lock escalations. Running START TRACE(ACCTG) DEST(GTF) IFCID(1,2,3,223) and collecting the trace data via IPCS or IBM Db2 Query Monitor showed WPMAIN accumulating an average of 47 seconds of lock suspension per execution during INVUPD01’s run window.

The DSNZPARM parameters governing Db2 lock behavior are critical context for any Db2 retainer engagement. IRLMRWT (IRLM Resource Wait Time) specifies how long a thread waits for a lock before receiving SQLCODE -911 (lock timeout). The bank had IRLMRWT=60 (60 seconds) — long enough that a brief lock conflict would resolve itself, but short enough to avoid hanging CICS threads for the full batch run duration. DEADLOK specifies the frequency (in seconds) at which Db2 scans for deadlock cycles; the bank had DEADLOK=5. NUMLKUS (maximum locks per user) was 0 (unlimited), which prevented automatic lock escalation from row-level X locks to a tablespace-level X lock — if NUMLKUS had been set to a lower value (e.g., 5,000), INVUPD01 would have escalated from row locks to a tablespace X lock at 5,000 rows, which would have been immediately visible in monitoring and would have blocked all concurrent access to the entire INVEST_POSITIONS tablespace. The unlimited NUMLKUS hid the severity of the problem: 847,000 individual row locks were invisible to threshold-based monitoring until a DISPLAY THREAD DETAIL was run manually.

The fix was a targeted change to the INVUPD01 COBOL source. After every 1,000 successful FETCH + UPDATE CURRENT OF iterations (tracked by a WS-COMMIT-COUNTER variable incremented with each FETCH), the program issues EXEC SQL COMMIT WORK END-EXEC. Since INVPOS is declared WITH HOLD, the COMMIT does not close or reposition the cursor — the next FETCH retrieves the next unprocessed row correctly. The commit counter is also written to a Db2 restart control table (UPDATE BATCH_RESTART_CTL SET LAST_COMMITTED_POSN = :WS-POSN-ID, COMMIT_TIME = CURRENT TIMESTAMP WHERE PROGRAM_ID = 'INVUPD01') immediately before COMMIT, providing a restart checkpoint: if the program ABENDs mid-run, the restart version re-opens the cursor with an additional AND POSN_ID > :WS-RESTART-POSN-ID predicate to skip already-committed positions. The INVUPD01 program was rebound with BIND PACKAGE, the COBOL source change was committed through the bank’s change management system (ServiceNow change CHG0091742), and the program was regression-tested in the pre-production environment by running INVUPD01 concurrently with a WPQA simulation script. After the fix: INVUPD01 issues COMMIT every 1,000 rows; maximum X lock accumulation at any time is approximately 1,000; WPMAIN lock suspend time drops to 0; AEY9 ABEND rate drops from 8–12 per night to 0.

Typical IBM Db2 developer retainer work and what it looks like in a work log

FOR UPDATE OF cursor without intermediate COMMIT accumulating tens of thousands of X locks is the canonical invisible Db2 batch locking bug for any COBOL program that updates a large table in a single unit of work. It appears systematically in Db2 retainer work logs for bank nightly position posting programs (updating DDA, savings, investment, or loan account records for the day’s transactions), insurance carrier batch claim status updates (updating claim records from PENDING_PAYMENT to PAID), payroll processor batch pay run updates (updating employee pay period records), and utility billing batch rate update programs. The work log entry: “IBM Db2 for z/OS 13 (DBRP); INVUPD01 COBOL DECLARE CURSOR INVPOS WITH HOLD FOR SELECT ... FROM INVEST_POSITIONS FOR UPDATE OF POSN_STATUS, POSN_QTY, POSN_COST; FETCH loop processes 847,000 rows with UPDATE CURRENT OF INVPOS after each FETCH; no EXEC SQL COMMIT WORK in loop; Db2 X locks on all 847,000 updated rows held 3.5h; CICS WPQA WPMAIN SELECT on INVEST_POSITIONS for acct WMC-0087642-00 SQLCODE -911 REASON 00C90088 after 60s IRLMRWT; WPMAIN handles -502, -803 not -911; CICS ABEND AEY9 offset +X'0D4C'; DISPLAY THREAD INVUPD DETAIL: X-LOCK-ROWS=847212; rate 8–12 AEY9/night; fix: COMMIT WORK every 1,000 FETCHes; WITH HOLD cursor survives COMMIT; max X locks ~1,000; AEY9 rate 0; hours: 4h.”

Access path regression after RUNSTATS refresh causing batch job time from 45 minutes to 6 hours is the most common planned Db2 retainer maintenance issue for organizations where RUNSTATS is run on a schedule without access path validation. The pattern: a REORG of the INVEST_POSITIONS tablespace is followed by RUNSTATS to refresh histogram statistics (RUNSTATS TABLESPACE INVDB.INVPOS INDEX ALL SHRLEVEL CHANGE TABLESAMPLE SYSTEM 5); the RUNSTATS updates the column statistics for POSN_STATUS (cardinality: PENDING_CLOSE rows now = 847,000, representing 7% of the table after the nightly batch cleaned up previous nights’ data); the next REBIND of the INVUPD01 package causes the optimizer to switch from a matching index scan on IX_INVPOS_STATUS (POSN_STATUS, POSN_ID) to a tablespace scan (because the optimizer now estimates 847,000 rows = 7% table cardinality, above the typical index-scan threshold); INVUPD01 cursor open now performs a full tablespace scan of 12 million rows instead of an index-range scan of 847,000; INVUPD01 batch time: 45 minutes to 6 hours 12 minutes. Fix: EXPLAIN the INVUPD01 cursor after REBIND; verify ACCESSTYPE (I for index, R for tablespace scan); if tablespace scan, evaluate RUNSTATS HISTOGRAM histogram key selection, add OPTIMIZE FOR n ROWS hint on the DECLARE CURSOR, or rebuild IX_INVPOS_STATUS with CLUSTER option to improve filter factor estimation. Work log: “Db2 13 DBRP; INVUPD01 INVPOS cursor access path regression after RUNSTATS; EXPLAIN PLAN DETAIL: ACCESSTYPE R (tablespace scan) vs. prior ACCESSTYPE I (IX_INVPOS_STATUS); POSN_STATUS FREQUENT VALUE POSN_STATUS='PENDING_CLOSE' COUNT=847000 (7% cardinality); optimizer threshold: index scan abandoned at 5–8% filter factor; fix: OPTIMIZE FOR 847000 ROWS on DECLARE CURSOR; REBIND PACKAGE (INVDBRM.INVUPD01) CURRENTDATA(NO) ISOLATION(CS); EXPLAIN post-rebind: ACCESSTYPE I1 IX_INVPOS_STATUS; INVUPD01 elapsed 45m restored; hours: 2h.”

Db2 LOAD utility displacement of ongoing batch SELECT causing SQLCODE -904 unavailable resource occurs when a DBA schedules a LOAD RESUME YES on a Db2 tablespace while a batch program is running a SELECT or cursor on the same tablespace. LOAD RESUME YES operates at tablespace-level drain: it issues a write claim on the tablespace (prevents new readers from opening cursors) but does not drain existing readers immediately. However, LOAD RESUME YES with SHRLEVEL NONE drains all current readers and issues an exclusive tablespace-level drain — any program with an open cursor on that tablespace receives SQLCODE -904 (RESOURCE UNAVAILABLE, REASON 00C2008E = tablespace quiesced for LOAD). Work log: “Db2 13 DBRP; LOAD REPLACE SHRLEVEL NONE on INVDB.REFDATA (reference data tablespace, 2M rows, 45-minute load window); INVUPD01 CURSOR REFPOS open on REFDATA at time of LOAD REPLACE; INVUPD01 EXEC SQL FETCH REFPOS SQLCODE -904 REASON 00C2008E at row 312,847; INVUPD01 ABEND S222 (JCL CANCEL by ABEND handler); root cause: LOAD schedule not coordinated with INVUPD01 REFPOS cursor window; fix: LOAD REPLACE scheduled after INVUPD01 completes; TWS dependency REFDATA-LOAD predecessor=INVUPD RC=0; -904 rate: 1–2/week → 0; hours: 1.5h.”

How HourTab tracks IBM Db2 developer retainer hours

The FOR UPDATE OF cursor with no intermediate COMMIT is invisible in unit and integration testing by the same mechanism that makes all batch lock accumulation bugs invisible when only one program runs at a time. The INVUPD01 developer who wrote the original cursor loop was solving the correct problem — processing 847,000 rows efficiently with positioned updates — and the loop tested correctly in isolation: run INVUPD01, verify 847,000 rows updated from PENDING_CLOSE to CLOSED, verify P&L calculations. The test environment did not run WPQA concurrently. The COMMIT omission was not a bug in the business logic — INVUPD01 produced correct results with or without intermediate COMMITs. The bug was in the concurrency model: the assumption that INVUPD01 could hold 847,000 X locks for 3.5 hours without affecting concurrent online transactions. That assumption held for years because the CICS WPQA transaction historically ran at isolation UR (uncommitted read), which does not wait for X locks. The isolation-level change to CS (cursor stability) — required for audit compliance — exposed the latent lock accumulation as an overnight CICS failure pattern.

The work log entry that makes Db2 FOR UPDATE OF lock accumulation work auditable must name every element of the causal chain: the Db2 version and subsystem (Db2 13, DBRP), the program and cursor (INVUPD01, INVPOS WITH HOLD FOR UPDATE OF), the table and row count (INVEST_POSITIONS, 847,000 rows), the missing COMMIT (no EXEC SQL COMMIT WORK in loop), the DSNZPARM timeout (IRLMRWT=60 seconds), the blocked transaction (CICS WPQA WPMAIN), the SQLCODE and reason code (-911, 00C90088), the CICS ABEND code and frequency (AEY9, 8–12/night), the DISPLAY THREAD DETAIL confirmation (X-LOCK-ROWS=847212), the fix (COMMIT WORK every 1,000 FETCHes, WITH HOLD cursor survives COMMIT), and the zero-ABEND outcome. A log that says “fixed Db2 locking issue in batch program, 4h” is not auditable to a bank’s investment operations manager who needs to explain to the wealth management phone service why the overnight batch program caused 8–12 CICS failures per night for the past three weeks since the isolation level change. HourTab gives IBM Db2 developers a public retainer-hours URL they send to clients — banks, insurance carriers, and capital markets firms that run critical nightly Db2 batch programs maintained by a Db2 specialist on monthly retainer.

The broader context for IBM Db2 retainer billing is that a FOR UPDATE OF cursor with no intermediate COMMIT is the Db2 for z/OS instantiation of the universal principle that any transaction-oriented system demands that long-running update processes release their exclusive locks periodically so concurrent readers can proceed. JCL developer retainers cover the equivalent at the z/OS allocation layer: PREMBIL1 STEP020 DISP=OLD on PREM.BILLING.MASTER.FILE holds a z/OS ENQ EXCLUSIVE for 63 minutes, blocking PREMRPT1’s DISP=SHR allocation for 61 minutes — same principle, different system layer: a long-held exclusive reservation blocks concurrent shared access. Oracle PL/SQL developer retainers cover the equivalent in Oracle: a PL/SQL procedure’s SELECT ... FOR UPDATE NOWAIT acquires a TX LMODE=6 exclusive lock held for 47 minutes when the procedure returns without ROLLBACK — Oracle X lock held indefinitely on the same pattern. CICS developer retainers cover the equivalent at the CICS application layer: EXEC CICS READ UPDATE on a VSAM KSDS record held for 23 minutes while a CSR steps away from the terminal — VSAM exclusive record lock held for the duration of the conversational task.

FAQ: IBM Db2 developer retainers