> "Every shop I've walked into that's been running DB2 for twenty years has the same problem: the batch window that was comfortable in 2005 is now a knife fight. The programs that survive aren't the ones that were written most cleverly — they're the...
In This Chapter
- 12.1 Patterns That Survive at Scale
- 12.2 Batch DB2 Patterns
- 12.3 Cursor Management at Scale
- 12.4 Dynamic SQL in COBOL
- 12.5 CICS-DB2 Thread Management
- 12.6 Data Architecture Patterns
- 12.7 Anti-Patterns: The Patterns That Work Until They Don't
- 12.8 Spaced Review: Connecting Back
- 12.9 HA Banking System: DB2 Architecture Checkpoint
- Chapter Summary
Chapter 12: DB2 Application Patterns: Batch Window Optimization, Cursor Management at Scale, and Dynamic SQL Security
"Every shop I've walked into that's been running DB2 for twenty years has the same problem: the batch window that was comfortable in 2005 is now a knife fight. The programs that survive aren't the ones that were written most cleverly — they're the ones that were written with patterns that scale." — Kwame Osei, VP of Core Banking Systems, CNB
12.1 Patterns That Survive at Scale
Let me tell you something that took me fifteen years to learn: the difference between a DB2 program that runs fine in development and one that runs fine at two billion rows is not optimization tricks. It's patterns. Structural decisions made before the first line of COBOL is written that determine whether the program will survive its first encounter with production volume.
In Chapters 6 through 8, you learned how the DB2 optimizer works, how to write advanced SQL, and how locking governs concurrency. Those chapters gave you the mechanisms. This chapter gives you the architecture — the repeatable patterns that experienced DB2/COBOL shops use to build systems that run reliably inside shrinking batch windows, handle millions of cursor operations without collapse, and use dynamic SQL without creating security nightmares.
The Three Pressures
Every DB2-COBOL application at scale faces three converging pressures:
The Batch Window Squeeze. Twenty years ago, a six-hour batch window was generous. Today, that same shop processes five times the volume in a window that's shrunk to three hours because online availability demands have expanded. The batch programs that survived weren't rewritten — they were designed with patterns that scale.
The Volume Wall. A cursor loop that processes 50,000 rows in development behaves completely differently at 50 million rows. Memory allocation, lock escalation thresholds, log consumption, and checkpoint frequency all change character. Programs designed for volume anticipate these transitions.
The Security Perimeter. Dynamic SQL, once a niche technique used only by ad-hoc query tools, now appears in application code regularly — report generators, configurable business rules, multi-tenant queries. Every one of these is a potential SQL injection vector unless designed with disciplined patterns.
Pattern vs. Practice
A pattern is not a coding trick. It's a structural decision that shapes how an entire program behaves. Consider the difference:
Practice: "Use FETCH with ROWSET to get multiple rows at once." Pattern: "Design batch programs around a commit-frequency architecture where the commit interval, cursor lifecycle, and restart logic are unified into a single coherent strategy."
The practice is a technique. The pattern is an architecture. This chapter teaches patterns.
What You Need Coming In
This chapter builds directly on:
- Chapter 6 (DB2 Optimizer): You need to understand access paths, EXPLAIN output, and why the optimizer makes the choices it does. We'll reference filter factors and index selection frequently.
- Chapter 7 (Advanced SQL): Common table expressions, MERGE statements, and multi-row operations appear throughout. If you skipped the set-based operations section, go back now.
- Chapter 8 (Locking): Lock escalation, isolation levels, and deadlock mechanics are foundational. Every pattern in this chapter has locking implications.
- Chapters 9-11 (soft prerequisites): CICS transaction management, error handling strategies, and performance monitoring provide context but aren't strictly required.
12.2 Batch DB2 Patterns
The Commit Frequency Architecture
The single most important architectural decision in a batch DB2 program is the commit strategy. Get this wrong and everything else — cursor management, restart logic, error handling — becomes a patchwork of compensating hacks.
The naive approach commits after every row. This is safe but catastrophically slow at volume. Each COMMIT is a synchronous write to the DB2 log, a release of all locks, and a cursor reposition (if using WITH HOLD). At two million rows, the overhead dominates.
The opposite extreme commits once at the end. This is fast but dangerous. The program holds locks for the entire run, the log fills up, and if the program abends at row 1,999,999, you lose everything.
The production pattern commits at calculated intervals, and that interval is a first-class design parameter, not an afterthought.
Calculating Optimal Commit Intervals
The optimal commit interval balances four factors:
-
Lock escalation threshold. DB2 escalates from row or page locks to table locks when the number of locks held exceeds
NUMLKTS(per-tablespace) orLOCKS PER TABLE(system-wide). If your commit interval allows more locks to accumulate than the threshold, you get table-level locking, which blocks concurrent access. On most z/OS DB2 systems, the default is around 1,000 locks per tablespace before escalation. -
Log consumption. Each uncommitted change consumes active log space. If your uncommitted changes exceed the active log allocation, DB2 forces a log archive, which stalls your program and potentially others. Calculate:
(average row size * commit interval * 1.5)should not exceed 25% of active log space. -
Restart granularity. If the program abends, you restart from the last commit point. A commit interval of 100,000 means you might reprocess up to 99,999 rows on restart. Your restart logic must handle this — and the business must accept the time cost.
-
Elapsed time per interval. Each commit interval should complete in roughly the same wall-clock time. This makes monitoring predictable and allows operations staff to estimate completion.
For most batch programs processing standard transactional rows, commit intervals between 500 and 5,000 rows are the sweet spot. Lisa Hwang at CNB standardized on 2,000 for most batch programs after benchmarking showed it balanced lock management against commit overhead:
WORKING-STORAGE SECTION.
01 WS-COMMIT-INTERVAL PIC S9(9) COMP VALUE 2000.
01 WS-COMMIT-COUNTER PIC S9(9) COMP VALUE 0.
01 WS-TOTAL-PROCESSED PIC S9(9) COMP VALUE 0.
01 WS-TOTAL-COMMITTED PIC S9(9) COMP VALUE 0.
01 WS-RESTART-KEY PIC X(20).
01 WS-CURRENT-KEY PIC X(20).
01 WS-COMMIT-STATS.
05 WS-COMMITS-ISSUED PIC S9(9) COMP VALUE 0.
05 WS-AVG-COMMIT-TIME PIC S9(9) COMP VALUE 0.
05 WS-MAX-COMMIT-TIME PIC S9(9) COMP VALUE 0.
05 WS-COMMIT-START PIC S9(15) COMP-3.
05 WS-COMMIT-END PIC S9(15) COMP-3.
The Commit-Restart Loop Pattern
This is the foundational batch pattern. Every element — the cursor, the commit, and the restart — is integrated:
PROCEDURE DIVISION.
0000-MAIN.
PERFORM 1000-INITIALIZE
PERFORM 2000-PROCESS-BATCH
PERFORM 9000-FINALIZE
STOP RUN.
1000-INITIALIZE.
* Check restart table for prior incomplete run
EXEC SQL
SELECT LAST_KEY, ROWS_COMMITTED
INTO :WS-RESTART-KEY, :WS-TOTAL-COMMITTED
FROM BATCH_RESTART
WHERE PROGRAM_ID = 'ACCTUPD'
AND RUN_DATE = CURRENT DATE
END-EXEC
EVALUATE SQLCODE
WHEN 0
DISPLAY 'RESTART: Resuming from key '
WS-RESTART-KEY
SET WS-IS-RESTART TO TRUE
WHEN 100
MOVE LOW-VALUES TO WS-RESTART-KEY
SET WS-IS-RESTART TO FALSE
WHEN OTHER
PERFORM 8000-SQL-ERROR
END-EVALUATE
* Open cursor positioned after last committed key
EXEC SQL
DECLARE BATCH-CURSOR CURSOR WITH HOLD FOR
SELECT ACCOUNT_KEY,
ACCOUNT_BAL,
LAST_ACTIVITY_DATE
FROM ACCOUNTS
WHERE ACCOUNT_KEY > :WS-RESTART-KEY
AND PROCESS_FLAG = 'P'
ORDER BY ACCOUNT_KEY
END-EXEC
EXEC SQL OPEN BATCH-CURSOR END-EXEC.
2000-PROCESS-BATCH.
PERFORM UNTIL WS-END-OF-DATA
EXEC SQL
FETCH BATCH-CURSOR
INTO :WS-ACCOUNT-KEY,
:WS-ACCOUNT-BAL,
:WS-LAST-ACTIVITY
END-EXEC
EVALUATE SQLCODE
WHEN 0
PERFORM 3000-PROCESS-ROW
ADD 1 TO WS-COMMIT-COUNTER
ADD 1 TO WS-TOTAL-PROCESSED
MOVE WS-ACCOUNT-KEY TO WS-CURRENT-KEY
IF WS-COMMIT-COUNTER >= WS-COMMIT-INTERVAL
PERFORM 5000-COMMIT-CHECKPOINT
END-IF
WHEN 100
SET WS-END-OF-DATA TO TRUE
WHEN OTHER
PERFORM 8000-SQL-ERROR
END-EVALUATE
END-PERFORM
* Final commit for remaining rows
IF WS-COMMIT-COUNTER > 0
PERFORM 5000-COMMIT-CHECKPOINT
END-IF.
5000-COMMIT-CHECKPOINT.
* Update restart table BEFORE commit
EXEC SQL
MERGE INTO BATCH_RESTART R
USING (VALUES ('ACCTUPD', CURRENT DATE))
AS S(PROGRAM_ID, RUN_DATE)
ON R.PROGRAM_ID = S.PROGRAM_ID
AND R.RUN_DATE = S.RUN_DATE
WHEN MATCHED THEN
UPDATE SET LAST_KEY = :WS-CURRENT-KEY,
ROWS_COMMITTED = :WS-TOTAL-COMMITTED
+ :WS-COMMIT-COUNTER,
COMMIT_TS = CURRENT TIMESTAMP
WHEN NOT MATCHED THEN
INSERT (PROGRAM_ID, RUN_DATE, LAST_KEY,
ROWS_COMMITTED, COMMIT_TS)
VALUES (S.PROGRAM_ID, S.RUN_DATE,
:WS-CURRENT-KEY,
:WS-COMMIT-COUNTER,
CURRENT TIMESTAMP)
END-EXEC
EXEC SQL COMMIT END-EXEC
ADD WS-COMMIT-COUNTER TO WS-TOTAL-COMMITTED
MOVE 0 TO WS-COMMIT-COUNTER
ADD 1 TO WS-COMMITS-ISSUED
DISPLAY 'COMMIT: ' WS-TOTAL-COMMITTED ' rows, '
'key=' WS-CURRENT-KEY.
Why this pattern works at scale:
- The cursor uses
WITH HOLD, so it survives the COMMIT without requiring repositioning. - The restart key is always the natural ordering key of the cursor, so restart is a simple WHERE clause modification.
- The MERGE into the restart table is atomic with the data changes — both are in the same commit scope.
- The commit counter is separate from the total counter, preventing drift.
Cursor-Based vs. Set-Based Processing
A persistent myth in DB2 programming is that set-based operations (UPDATE ... WHERE, INSERT ... SELECT) are always faster than cursor-based processing. At moderate volumes, yes. At extreme volumes with complex business logic, the picture reverses.
Use set-based when: - The transformation is expressible in pure SQL (no procedural logic) - The affected row count is under ~500,000 - You don't need row-level error handling - Lock duration is acceptable (set-based holds locks until the statement completes)
Use cursor-based when: - Business logic requires COBOL procedural processing per row - You need commit checkpointing for restartability - The volume exceeds the point where lock escalation would occur - You need row-level error logging (skip bad rows, continue processing) - Different rows require different processing paths
Rob Chen at CNB learned this lesson during the quarterly interest calculation rewrite. The original set-based UPDATE processed 12 million accounts in a single statement. It worked for three years — until the account base grew to 18 million and the statement consumed enough log space to force an archive switch, stalling the entire DB2 subsystem for 40 seconds. The cursor-based rewrite with 2,000-row commits ran 15% slower in wall-clock time but never threatened log capacity and could be restarted from any checkpoint.
Partition-Aware Processing
When tables are partitioned (and at enterprise scale, they should be), batch programs should be partition-aware. This means:
- Processing one partition at a time to contain lock scope
- Parallelizing across partitions when the batch window demands it
- Using partition-specific utilities (REORG, RUNSTATS) between batch steps
* Partition-aware cursor using limiting key ranges
EXEC SQL
DECLARE PART-CURSOR CURSOR WITH HOLD FOR
SELECT ACCOUNT_KEY, ACCOUNT_BAL
FROM ACCOUNTS
WHERE ACCOUNT_KEY >= :WS-PART-LOW-KEY
AND ACCOUNT_KEY < :WS-PART-HIGH-KEY
AND PROCESS_FLAG = 'P'
ORDER BY ACCOUNT_KEY
END-EXEC
The partition key ranges come from the DB2 catalog:
SELECT LOWKEY, HIGHKEY, PARTITION
FROM SYSIBM.SYSTABLEPART
WHERE TSNAME = 'ACCTS_TS'
ORDER BY PARTITION;
At CNB, partition-aware batch processing reduced the nightly batch window by 35% when they parallelized the interest calculation across eight partitions, each running as a separate job step with its own commit cycle.
12.3 Cursor Management at Scale
The WITH HOLD Decision
Every cursor in a batch program must be declared WITH HOLD or without it. This is not a detail — it's an architectural decision that affects restartability, lock behavior, and resource consumption.
WITH HOLD cursors survive a COMMIT. The cursor position is maintained, and you can continue fetching after the commit. This is the standard pattern for batch processing because it allows the commit-checkpoint architecture described above.
Without HOLD cursors are closed by every COMMIT. If you commit, you must reopen the cursor and reposition. This is the correct choice when: - You're processing a cursor in a CICS pseudo-conversational transaction (cursor can't survive the RETURN) - You need full lock release between iterations (WITH HOLD maintains some positioning locks) - The cursor's result set should be refreshed after each commit (you want to see changes made by other programs)
The hidden cost of WITH HOLD: DB2 maintains a "drain lock" on the tablespace for WITH HOLD cursors. This lock prevents utilities like REORG from running while the cursor is open. If your batch program holds a WITH HOLD cursor open for three hours, no utility can touch that tablespace for three hours. At CNB, this became a crisis when a REORG job kept timing out — the root cause was a batch program that opened a WITH HOLD cursor on the same tablespace and ran for four hours with infrequent commits.
The fix was disciplined cursor lifecycle management:
* Close and reopen WITH HOLD cursor periodically
* to release drain locks
5100-CURSOR-REFRESH.
IF WS-COMMITS-ISSUED > 0
AND FUNCTION MOD(WS-COMMITS-ISSUED, 50) = 0
* Every 50 commits (100,000 rows at interval=2000),
* close and reopen the cursor to release drain locks
EXEC SQL CLOSE BATCH-CURSOR END-EXEC
EXEC SQL OPEN BATCH-CURSOR END-EXEC
DISPLAY 'CURSOR REFRESH at commit #'
WS-COMMITS-ISSUED
END-IF.
Positioned Updates
When you need to update or delete the row you just fetched, use positioned updates (UPDATE ... WHERE CURRENT OF). This eliminates the need for DB2 to re-locate the row, which saves a catalog lookup and index traversal:
EXEC SQL
DECLARE UPD-CURSOR CURSOR WITH HOLD FOR
SELECT ACCOUNT_KEY, ACCOUNT_BAL,
INTEREST_RATE, LAST_CALC_DATE
FROM ACCOUNTS
WHERE ACCOUNT_TYPE = 'SAV'
AND LAST_CALC_DATE < CURRENT DATE - 1 DAY
ORDER BY ACCOUNT_KEY
FOR UPDATE OF ACCOUNT_BAL, LAST_CALC_DATE
END-EXEC
* In the processing loop:
3000-PROCESS-ROW.
COMPUTE WS-NEW-BAL = WS-ACCOUNT-BAL *
(1 + (WS-INTEREST-RATE / 36500) *
WS-DAYS-ELAPSED)
EXEC SQL
UPDATE ACCOUNTS
SET ACCOUNT_BAL = :WS-NEW-BAL,
LAST_CALC_DATE = CURRENT DATE
WHERE CURRENT OF UPD-CURSOR
END-EXEC
IF SQLCODE NOT = 0
PERFORM 8000-SQL-ERROR
END-IF.
Critical constraint: A cursor declared FOR UPDATE OF cannot also use certain optimizations. DB2 cannot use read-only access paths (list prefetch, certain parallel operations) for updatable cursors. If your batch program fetches far more rows than it updates, consider using two separate operations: a read-only cursor for selection, and a searched UPDATE with the primary key for modification:
* Read-only cursor (allows list prefetch, parallelism)
EXEC SQL
DECLARE READ-CURSOR CURSOR WITH HOLD FOR
SELECT ACCOUNT_KEY, ACCOUNT_BAL,
INTEREST_RATE, LAST_CALC_DATE
FROM ACCOUNTS
WHERE ACCOUNT_TYPE = 'SAV'
AND LAST_CALC_DATE < CURRENT DATE - 1 DAY
ORDER BY ACCOUNT_KEY
FOR FETCH ONLY
END-EXEC
* Searched update using primary key from fetched row
EXEC SQL
UPDATE ACCOUNTS
SET ACCOUNT_BAL = :WS-NEW-BAL,
LAST_CALC_DATE = CURRENT DATE
WHERE ACCOUNT_KEY = :WS-ACCOUNT-KEY
END-EXEC
The searched UPDATE costs one additional index probe per row, but the read-only cursor can use prefetch, which at high volumes more than compensates.
Cursor Pools in CICS Applications
In CICS environments, cursor management takes a different shape. A CICS transaction cannot hold a cursor across a SYNCPOINT ROLLBACK or across a pseudo-conversational boundary. The pattern for high-volume CICS inquiry programs is the cursor pool — a set of pre-declared cursors that are opened and closed within each task, with the cursor selection determined by the query parameters.
Ahmad Nazari at Pinnacle Health implemented this pattern for their patient lookup system, which handles 3,000 queries per minute during peak hours:
01 WS-CURSOR-SELECTOR PIC 9.
88 USE-CURSOR-BY-NAME VALUE 1.
88 USE-CURSOR-BY-MRN VALUE 2.
88 USE-CURSOR-BY-DOB-NAME VALUE 3.
88 USE-CURSOR-BY-SSN VALUE 4.
* Four cursors, each optimized for its access path
EXEC SQL
DECLARE CURS-BY-NAME CURSOR FOR
SELECT PATIENT_ID, LAST_NAME, FIRST_NAME,
DATE_OF_BIRTH, MRN
FROM PATIENTS
WHERE LAST_NAME = :WS-SEARCH-LAST
AND FIRST_NAME LIKE :WS-SEARCH-FIRST
ORDER BY LAST_NAME, FIRST_NAME
FETCH FIRST 50 ROWS ONLY
END-EXEC
EXEC SQL
DECLARE CURS-BY-MRN CURSOR FOR
SELECT PATIENT_ID, LAST_NAME, FIRST_NAME,
DATE_OF_BIRTH, MRN
FROM PATIENTS
WHERE MRN = :WS-SEARCH-MRN
END-EXEC
EXEC SQL
DECLARE CURS-BY-DOB-NAME CURSOR FOR
SELECT PATIENT_ID, LAST_NAME, FIRST_NAME,
DATE_OF_BIRTH, MRN
FROM PATIENTS
WHERE DATE_OF_BIRTH = :WS-SEARCH-DOB
AND LAST_NAME = :WS-SEARCH-LAST
ORDER BY FIRST_NAME
FETCH FIRST 50 ROWS ONLY
END-EXEC
EXEC SQL
DECLARE CURS-BY-SSN CURSOR FOR
SELECT PATIENT_ID, LAST_NAME, FIRST_NAME,
DATE_OF_BIRTH, MRN
FROM PATIENTS
WHERE SSN_HASH = :WS-SEARCH-SSN-HASH
END-EXEC
Each cursor is backed by a different index, and the application routes to the appropriate cursor based on what the user provided. This avoids the anti-pattern of a single cursor with optional predicates (WHERE (:WS-LAST = '' OR LAST_NAME = :WS-LAST)), which the optimizer cannot optimize because it must plan for all possible combinations.
Scrollable Cursors
DB2 for z/OS supports scrollable cursors (SCROLL keyword) that allow forward, backward, and absolute positioning. In practice, scrollable cursors are useful in two scenarios:
- CICS screen paging — allowing users to page forward and backward through result sets
- Multi-pass processing — when you need to read the same result set multiple times
EXEC SQL
DECLARE SCROLL-CURSOR SENSITIVE SCROLL CURSOR FOR
SELECT CLAIM_ID, PATIENT_ID, AMOUNT,
SERVICE_DATE, STATUS
FROM CLAIMS
WHERE PROVIDER_ID = :WS-PROVIDER
AND SERVICE_DATE BETWEEN :WS-START AND :WS-END
ORDER BY SERVICE_DATE
END-EXEC
* Page forward
EXEC SQL
FETCH NEXT FROM SCROLL-CURSOR
INTO :WS-CLAIM-ID, :WS-PATIENT-ID,
:WS-AMOUNT, :WS-SERVICE-DATE, :WS-STATUS
END-EXEC
* Page backward
EXEC SQL
FETCH PRIOR FROM SCROLL-CURSOR
INTO :WS-CLAIM-ID, :WS-PATIENT-ID,
:WS-AMOUNT, :WS-SERVICE-DATE, :WS-STATUS
END-EXEC
* Jump to specific row
EXEC SQL
FETCH ABSOLUTE :WS-TARGET-ROW FROM SCROLL-CURSOR
INTO :WS-CLAIM-ID, :WS-PATIENT-ID,
:WS-AMOUNT, :WS-SERVICE-DATE, :WS-STATUS
END-EXEC
Performance warning: Scrollable cursors require DB2 to materialize the result set in a workfile. For large result sets, this means significant TEMP space consumption and an initial delay while the result set is built. Diane Park at Pinnacle Health limited scrollable cursor result sets to 500 rows maximum after a provider inquiry that returned 12,000 claims consumed 400 MB of workfile space and degraded the entire DB2 subsystem.
12.4 Dynamic SQL in COBOL
Why Dynamic SQL in COBOL
Static SQL — the kind embedded directly in COBOL source with EXEC SQL — is precompiled, bound to a plan or package, and authorized at bind time. It's safe, predictable, and fast. So why would you ever use dynamic SQL?
Because sometimes you don't know the SQL at compile time:
- Report generators where the user selects columns, filters, and sort order
- Configurable business rules stored in tables ("apply discount to customers where REGION IN (...)")
- Multi-tenant applications where the table name varies by tenant
- Administrative utilities that run DDL or operational SQL
- Search screens with optional criteria (the proper alternative to OR-stuffed static queries)
The PREPARE/EXECUTE Pattern
Dynamic SQL in COBOL uses a two-phase approach: PREPARE converts a SQL string into an executable statement, and EXECUTE runs it.
WORKING-STORAGE SECTION.
01 WS-SQL-STATEMENT PIC X(2000).
01 WS-SQLCODE PIC S9(9) COMP.
01 WS-PARAM-REGION PIC X(10).
01 WS-PARAM-MIN-BAL PIC S9(13)V99 COMP-3.
01 WS-ROWS-UPDATED PIC S9(9) COMP.
PROCEDURE DIVISION.
* Build the SQL statement with parameter markers
STRING
'UPDATE ACCOUNTS '
'SET DISCOUNT_FLAG = ''Y'' '
'WHERE REGION = ? '
'AND ACCOUNT_BAL > ? '
'AND ACCOUNT_TYPE = ''RETAIL'''
DELIMITED BY SIZE
INTO WS-SQL-STATEMENT
END-STRING
* Prepare the statement
EXEC SQL
PREPARE DYNSTMT FROM :WS-SQL-STATEMENT
END-EXEC
IF SQLCODE NOT = 0
DISPLAY 'PREPARE failed: ' SQLCODE
PERFORM 8000-SQL-ERROR
END-IF
* Execute with parameter values
MOVE 'NORTHEAST' TO WS-PARAM-REGION
MOVE 10000.00 TO WS-PARAM-MIN-BAL
EXEC SQL
EXECUTE DYNSTMT
USING :WS-PARAM-REGION,
:WS-PARAM-MIN-BAL
END-EXEC
IF SQLCODE NOT = 0
PERFORM 8000-SQL-ERROR
ELSE
MOVE SQLERRD(3) TO WS-ROWS-UPDATED
DISPLAY 'Updated ' WS-ROWS-UPDATED ' rows'
END-IF.
The USING clause is the key to security. Parameter markers (?) are never interpolated as text — they are bound as typed values. A parameter marker cannot contain SQL syntax, cannot inject a second statement, cannot modify the query structure. This is the first and most important defense against SQL injection.
Dynamic SELECT with OPEN CURSOR
For dynamic queries that return result sets, you use PREPARE with DECLARE CURSOR:
01 WS-DYN-SQL PIC X(4000).
01 WS-RESULT-ACCT PIC X(20).
01 WS-RESULT-NAME PIC X(50).
01 WS-RESULT-BAL PIC S9(13)V99 COMP-3.
PROCEDURE DIVISION.
1000-BUILD-QUERY.
* Build query dynamically based on user criteria
STRING
'SELECT ACCOUNT_KEY, CUSTOMER_NAME, '
'ACCOUNT_BAL '
'FROM ACCOUNTS A '
'JOIN CUSTOMERS C '
' ON A.CUSTOMER_ID = C.CUSTOMER_ID '
'WHERE 1=1 '
DELIMITED BY SIZE
INTO WS-DYN-SQL
END-STRING
IF WS-HAS-REGION-FILTER
STRING WS-DYN-SQL DELIMITED BY SPACES
' AND A.REGION = ? '
DELIMITED BY SIZE
INTO WS-DYN-SQL
END-STRING
ADD 1 TO WS-PARAM-COUNT
END-IF
IF WS-HAS-BALANCE-FILTER
STRING WS-DYN-SQL DELIMITED BY SPACES
' AND A.ACCOUNT_BAL > ? '
DELIMITED BY SIZE
INTO WS-DYN-SQL
END-STRING
ADD 1 TO WS-PARAM-COUNT
END-IF
STRING WS-DYN-SQL DELIMITED BY SPACES
' ORDER BY ACCOUNT_KEY '
' FETCH FIRST 1000 ROWS ONLY'
DELIMITED BY SIZE
INTO WS-DYN-SQL
END-STRING.
2000-EXECUTE-QUERY.
EXEC SQL
PREPARE DYNSEL FROM :WS-DYN-SQL
END-EXEC
EXEC SQL
DECLARE DYN-CURSOR CURSOR FOR DYNSEL
END-EXEC
* Open with appropriate parameter list
EVALUATE WS-PARAM-COUNT
WHEN 0
EXEC SQL OPEN DYN-CURSOR END-EXEC
WHEN 1
IF WS-HAS-REGION-FILTER
EXEC SQL OPEN DYN-CURSOR
USING :WS-PARAM-REGION
END-EXEC
ELSE
EXEC SQL OPEN DYN-CURSOR
USING :WS-PARAM-MIN-BAL
END-EXEC
END-IF
WHEN 2
EXEC SQL OPEN DYN-CURSOR
USING :WS-PARAM-REGION,
:WS-PARAM-MIN-BAL
END-EXEC
END-EVALUATE.
The DESCRIBE Statement and SQLDA
When the result set structure isn't known at compile time (true ad-hoc query tools), you use DESCRIBE to interrogate the prepared statement's column metadata through the SQL Descriptor Area (SQLDA):
01 WS-SQLDA-PTR POINTER.
01 WS-SQLDA.
05 SQLDAID PIC X(8) VALUE 'SQLDA '.
05 SQLDABC PIC S9(9) COMP VALUE 3016.
05 SQLN PIC S9(4) COMP VALUE 100.
05 SQLD PIC S9(4) COMP VALUE 0.
05 SQLVAR OCCURS 100 TIMES.
10 SQLTYPE PIC S9(4) COMP.
10 SQLLEN PIC S9(4) COMP.
10 SQLDATA POINTER.
10 SQLIND POINTER.
10 SQLNAME.
15 SQLNAMEL PIC S9(4) COMP.
15 SQLNAMEC PIC X(30).
PROCEDURE DIVISION.
EXEC SQL
DESCRIBE DYNSEL INTO :WS-SQLDA
END-EXEC
* SQLD now contains the number of result columns
* Each SQLVAR entry describes one column
PERFORM VARYING WS-COL-IDX FROM 1 BY 1
UNTIL WS-COL-IDX > SQLD OF WS-SQLDA
DISPLAY 'Column ' WS-COL-IDX ': '
SQLNAMEC OF SQLVAR(WS-COL-IDX)
' Type=' SQLTYPE OF SQLVAR(WS-COL-IDX)
' Len=' SQLLEN OF SQLVAR(WS-COL-IDX)
END-PERFORM.
DESCRIBE is advanced territory. Most COBOL programs that use dynamic SQL know the result structure at compile time and use the simpler PREPARE/OPEN/FETCH pattern. DESCRIBE is for true ad-hoc query engines, which are rare in new COBOL development.
SQL Injection Prevention: A Complete Strategy
SQL injection in COBOL/DB2 is real. It's less publicized than web application injection, but the attack surface exists wherever dynamic SQL incorporates user-supplied data. Yuki Tanaka at SecureFirst discovered this during a security audit of their legacy claims processing system.
Layer 1: Always use parameter markers. Never concatenate user input directly into SQL text.
* WRONG — SQL injection vulnerable
STRING 'SELECT * FROM ACCOUNTS WHERE ACCT = '''
WS-USER-INPUT
''''
DELIMITED BY SIZE
INTO WS-SQL-STATEMENT
END-STRING
* RIGHT — parameterized
MOVE 'SELECT * FROM ACCOUNTS WHERE ACCT = ?'
TO WS-SQL-STATEMENT
EXEC SQL PREPARE STMT FROM :WS-SQL-STATEMENT END-EXEC
EXEC SQL OPEN CUR USING :WS-USER-INPUT END-EXEC
Layer 2: Input validation. Even with parameter markers, validate that input conforms to expected patterns. An account number should be numeric. A region code should be in a known list.
3500-VALIDATE-INPUT.
* Account number: exactly 10 digits
INSPECT WS-USER-ACCT TALLYING WS-DIGIT-COUNT
FOR ALL '0' '1' '2' '3' '4' '5' '6' '7' '8' '9'
IF WS-DIGIT-COUNT NOT = 10
MOVE 'INVALID ACCOUNT FORMAT' TO WS-ERROR-MSG
SET WS-INPUT-INVALID TO TRUE
END-IF
* Region code: whitelist validation
EVALUATE WS-USER-REGION
WHEN 'NORTHEAST'
WHEN 'SOUTHEAST'
WHEN 'MIDWEST'
WHEN 'SOUTHWEST'
WHEN 'WEST'
WHEN 'PACIFIC'
CONTINUE
WHEN OTHER
MOVE 'INVALID REGION CODE' TO WS-ERROR-MSG
SET WS-INPUT-INVALID TO TRUE
END-EVALUATE.
Layer 3: Restrict dynamic SQL scope. The DYNAMICRULES bind option controls what dynamic SQL can do. Set it to BIND so dynamic SQL inherits the static SQL authorization — meaning it can only access objects the plan is authorized for, not everything the user ID can access.
Layer 4: Audit trail. Log every dynamic SQL statement before execution, including the parameter values. This creates a forensic trail and also helps with performance debugging:
5500-AUDIT-DYNAMIC-SQL.
EXEC SQL
INSERT INTO SQL_AUDIT_LOG
(TIMESTAMP, PROGRAM_ID, USER_ID,
SQL_TEXT, PARAM_VALUES, SOURCE_IP)
VALUES
(CURRENT TIMESTAMP, 'RPTGEN01',
:WS-CURRENT-USER, :WS-SQL-STATEMENT,
:WS-PARAM-STRING, :WS-SOURCE-IP)
END-EXEC.
Layer 5: Prepared statement caching. If the same dynamic SQL structure is executed repeatedly with different parameters (common in report loops), prepare once and execute many times. This avoids repeated preparation overhead and also means the SQL text is fixed after the first preparation — reducing the window for injection:
01 WS-STMT-PREPARED-FLAG PIC 9 VALUE 0.
88 STMT-NOT-PREPARED VALUE 0.
88 STMT-PREPARED VALUE 1.
4000-EXECUTE-REPORT-QUERY.
IF STMT-NOT-PREPARED
EXEC SQL
PREPARE RPT-STMT FROM :WS-SQL-TEMPLATE
END-EXEC
SET STMT-PREPARED TO TRUE
END-IF
* Execute with this iteration's parameters
EXEC SQL
EXECUTE RPT-STMT
USING :WS-PARAM-1, :WS-PARAM-2
END-EXEC.
Carlos Mendez at SecureFirst now requires all dynamic SQL programs to pass through a code review checklist that verifies all five layers. Their audit found three programs that concatenated user input directly into SQL — all in report generators written before 2010, all still running in production.
12.5 CICS-DB2 Thread Management
How CICS Talks to DB2
When a CICS transaction issues a SQL statement, the request flows through the CICS-DB2 attachment facility, which manages a pool of DB2 threads. Understanding this plumbing is essential for designing high-throughput CICS-DB2 applications.
The flow is:
- CICS task issues EXEC SQL
- CICS-DB2 attachment facility intercepts the request
- Attachment facility obtains a DB2 thread from the thread pool
- SQL executes on the DB2 thread
- Results return to the CICS task
- Thread is returned to the pool (or held, depending on configuration)
Thread Types
DB2 provides two thread types for CICS:
Pool threads are shared. A CICS task gets a thread from the pool for the duration of one SQL call (or one unit of work, depending on configuration), then releases it. This is efficient for low-frequency SQL callers.
Entry threads are dedicated to specific CICS transactions or plans. A transaction configured for an entry thread gets a pre-allocated thread that's always available, eliminating the overhead of thread creation. This is appropriate for high-volume transactions.
DSNC TRANSACTION(ACCTINQ) PLAN(ACCTPLN) THREADS(5) PRIORITY(HIGH)
DSNC TRANSACTION(XFERTRN) PLAN(XFERPLN) THREADS(3) PRIORITY(HIGH)
DB2CONN Configuration
The CICS DB2CONN resource definition controls the attachment facility:
DEFINE DB2CONN(DB2PROD)
GROUP(DB2GROUP)
DB2ID(DB2P)
CONNECTERROR(SQLCODE)
MSGQUEUE1(CSSL)
NONTERMREL(YES)
PURGECYCLE(30)
RESYNCMEMBER(YES)
STANDBYMODE(NOCONNECT)
STATSQUEUE(CSMT)
TCBLIMIT(96)
THREADLIMIT(50)
THREADWAIT(YES)
ACCOUNTREC(TASK)
Key parameters:
- THREADLIMIT: Maximum concurrent DB2 threads. Set too low: transactions wait or abend. Set too high: DB2 resource consumption spikes. Start at 50, monitor, adjust.
- THREADWAIT(YES): When all threads are in use, new requests wait rather than receiving an error. Essential for production stability — without it, a brief spike causes transaction failures.
- TCBLIMIT: Maximum TCBs for DB2 work. Controls the CICS-side parallelism.
- ACCOUNTREC(TASK): DB2 accounting records are cut per CICS task. This gives you per-transaction performance data.
Thread Reuse Patterns
Thread creation is expensive — it involves DB2 allocating a service task, loading the plan, and establishing authorization context. For high-volume transactions, thread reuse is critical.
Protected threads remain allocated to a transaction after the task ends, waiting for the next task with the same transaction ID. Configure this with:
DEFINE DB2ENTRY(ACCTENT)
DB2CONN(DB2PROD)
PLAN(ACCTPLN)
TRANSID(ACCT)
THREADLIMIT(8)
PROTECTNUM(4)
PROTECTNUM(4) means DB2 keeps four threads protected (pre-allocated and waiting) even when no ACCT transactions are running. The next ACCT transaction gets a pre-warmed thread instantly.
The cost: Protected threads consume DB2 resources (EDM pool memory, working storage) even when idle. Don't protect threads for infrequent transactions.
Sandra Miller at Federal Benefits tuned their protected thread configuration by analyzing transaction volume by hour:
06:00-08:00 ACCT transactions/hour: 200 -> PROTECTNUM(2)
08:00-17:00 ACCT transactions/hour: 3,500 -> PROTECTNUM(8)
17:00-22:00 ACCT transactions/hour: 800 -> PROTECTNUM(4)
22:00-06:00 ACCT transactions/hour: 50 -> PROTECTNUM(1)
They wrote a CICS PLT (Program List Table) program that adjusts PROTECTNUM at each boundary using EXEC CICS SET DB2ENTRY, reducing idle thread resource consumption by 40% during off-hours.
Thread Accounting and Performance Monitoring
Understanding thread behavior requires instrumentation. DB2 provides detailed thread accounting through DB2 accounting trace (class 1, 2, and 3 records) that expose how threads are actually being used — not how you think they're being used.
Key accounting fields for thread analysis:
| Field | What It Tells You | Action Threshold |
|---|---|---|
| QWACRINV | Number of SQL calls per thread | > 500 suggests the transaction is doing too much per UOW |
| QWACWAIT | Thread wait time (all causes) | > 20% of elapsed time indicates contention |
| QWACPOOL | Time waiting for pool thread allocation | > 100ms means THREADLIMIT is too low |
| QWACATCH | Thread creation time (non-protected) | > 50ms justifies protected thread configuration |
| QWACSPNS | Suspend time (DB2 internal) | High values indicate buffer pool or lock contention |
Ahmad at Pinnacle Health built a daily thread utilization report from DB2 accounting traces. The report showed that their patient lookup transaction (PTLKUP) was creating and destroying 8,000 threads per hour because it was configured as a pool thread. Thread creation averaged 35ms each — totaling 280 seconds of pure thread overhead per hour. After configuring PTLKUP as a protected entry thread with PROTECTNUM(6), thread creation time dropped to near zero and transaction response time improved by 12%.
Thread high-water mark monitoring is equally important. DB2 tracks the maximum concurrent threads reached during any interval. If your peak thread count regularly hits 90% of THREADLIMIT, you are one traffic spike away from transactions waiting for threads or, worse, getting AEY9 abends (no thread available, THREADWAIT(NO)). CNB monitors thread high-water marks hourly and generates an alert when any DB2ENTRY exceeds 80% of its THREADLIMIT for three consecutive intervals.
Connection Pooling Anti-Patterns in CICS
The "one plan per program" anti-pattern: Each COBOL program bound to its own DB2 plan. With 200 programs, that's 200 plans, 200 potential package sets, and thread pool fragmentation because each plan needs its own threads. The solution is package collections — bind programs into packages grouped into a small number of plans by functional area:
Plan ACCTPLN: packages for ACCTINQ, ACCTUPD, ACCTXFR, ACCTRPT
Plan CLMPLN: packages for CLMINQ, CLMUPD, CLMADJ, CLMPAY
Now the thread pool serves plans, not programs, and thread reuse rates improve dramatically.
The "autocommit after every SQL" anti-pattern: CICS transactions that issue SYNCPOINT after every SQL statement. This forces a thread release and reacquire for each SQL call. Instead, batch related SQL operations into a single unit of work:
* WRONG: Commit after each SQL
EXEC SQL SELECT ... END-EXEC
EXEC CICS SYNCPOINT END-EXEC
EXEC SQL UPDATE ... END-EXEC
EXEC CICS SYNCPOINT END-EXEC
EXEC SQL INSERT ... END-EXEC
EXEC CICS SYNCPOINT END-EXEC
* RIGHT: Single unit of work
EXEC SQL SELECT ... END-EXEC
EXEC SQL UPDATE ... END-EXEC
EXEC SQL INSERT ... END-EXEC
EXEC CICS SYNCPOINT END-EXEC
12.6 Data Architecture Patterns
Table Partitioning Strategies
Partitioning is not a performance trick — it's a data management architecture. The partitioning decision affects:
- Batch performance: Partition-aligned scans avoid cross-partition overhead
- Utility performance: REORG, RUNSTATS, COPY run per partition, enabling parallelism
- Availability: You can take one partition offline while others remain accessible
- Archival: Dropping a partition is instantaneous; deleting millions of rows is not
Range partitioning by date is the most common pattern for transactional data:
CREATE TABLESPACE TXNS_TS
IN TXNDB
USING STOGROUP PRODSG
NUMPARTS 12
(PART 1 VALUES('2025-01-31'),
PART 2 VALUES('2025-02-28'),
PART 3 VALUES('2025-03-31'),
PART 4 VALUES('2025-04-30'),
PART 5 VALUES('2025-05-31'),
PART 6 VALUES('2025-06-30'),
PART 7 VALUES('2025-07-31'),
PART 8 VALUES('2025-08-31'),
PART 9 VALUES('2025-09-30'),
PART 10 VALUES('2025-10-31'),
PART 11 VALUES('2025-11-30'),
PART 12 VALUES('2025-12-31'));
Range partitioning by key for account-based systems where you want to parallelize batch processing across account ranges:
CREATE TABLESPACE ACCTS_TS
IN ACCTDB
NUMPARTS 8
(PART 1 VALUES('19999999'),
PART 2 VALUES('39999999'),
PART 3 VALUES('59999999'),
PART 4 VALUES('79999999'),
PART 5 VALUES('99999999'),
PART 6 VALUES('BZ999999'),
PART 7 VALUES('DZ999999'),
PART 8 VALUES('ZZZZZZZZ'));
History Table Patterns
Every transactional system eventually needs history. The question is not whether to implement it but which pattern to use:
Pattern 1: Triggered history. An AFTER trigger copies the old row to a history table on every UPDATE or DELETE. Simple, automatic, but adds overhead to every modification.
CREATE TRIGGER ACCT_HIST_TRG
AFTER UPDATE ON ACCOUNTS
REFERENCING OLD AS O
FOR EACH ROW
INSERT INTO ACCOUNTS_HISTORY
(ACCOUNT_KEY, ACCOUNT_BAL, MODIFIED_TS, MODIFIED_BY,
CHANGE_TYPE, HIST_TS)
VALUES
(O.ACCOUNT_KEY, O.ACCOUNT_BAL, O.MODIFIED_TS,
O.MODIFIED_BY, 'U', CURRENT TIMESTAMP);
Pattern 2: Application-managed history. The COBOL program explicitly inserts a history record before updating the current row. More control, more code, no trigger overhead:
3000-UPDATE-WITH-HISTORY.
* Save current state to history
EXEC SQL
INSERT INTO ACCOUNTS_HISTORY
(ACCOUNT_KEY, ACCOUNT_BAL, MODIFIED_TS,
MODIFIED_BY, CHANGE_TYPE, HIST_TS)
SELECT ACCOUNT_KEY, ACCOUNT_BAL, MODIFIED_TS,
MODIFIED_BY, 'U', CURRENT TIMESTAMP
FROM ACCOUNTS
WHERE ACCOUNT_KEY = :WS-ACCOUNT-KEY
END-EXEC
* Now update current row
EXEC SQL
UPDATE ACCOUNTS
SET ACCOUNT_BAL = :WS-NEW-BAL,
MODIFIED_TS = CURRENT TIMESTAMP,
MODIFIED_BY = :WS-PROGRAM-ID
WHERE ACCOUNT_KEY = :WS-ACCOUNT-KEY
END-EXEC.
Pattern 3: Temporal tables (DB2 12+). DB2 manages history automatically using system-period temporal tables. This is the modern answer:
CREATE TABLE ACCOUNTS
(ACCOUNT_KEY CHAR(10) NOT NULL,
ACCOUNT_BAL DECIMAL(15,2),
SYS_START TIMESTAMP(12) NOT NULL
GENERATED ALWAYS AS ROW BEGIN,
SYS_END TIMESTAMP(12) NOT NULL
GENERATED ALWAYS AS ROW END,
TRANS_START TIMESTAMP(12)
GENERATED ALWAYS AS TRANSACTION START ID,
PERIOD SYSTEM_TIME(SYS_START, SYS_END))
IN ACCTDB.ACCTS_TS;
CREATE TABLE ACCOUNTS_HISTORY LIKE ACCOUNTS IN HISTDB.HIST_TS;
ALTER TABLE ACCOUNTS
ADD VERSIONING USE HISTORY TABLE ACCOUNTS_HISTORY;
Now every UPDATE and DELETE automatically maintains history, and you can query any point in time:
SELECT * FROM ACCOUNTS
FOR SYSTEM_TIME AS OF '2025-06-15-12.00.00'
WHERE ACCOUNT_KEY = '1234567890';
Marcus Taylor at Federal Benefits adopted temporal tables for their benefits calculation system, eliminating 3,000 lines of hand-written history management code and enabling auditors to query any past state with a simple AS OF clause.
Archival Patterns
Data growth is the silent killer of batch performance. Tables that grow without bound eventually overwhelm even well-designed batch programs. Archival is not optional — it's a design requirement.
The rolling partition pattern: Create more partitions than you need. As new partitions are added at the high end, old partitions are detached and archived:
-- Add new partition for next month
ALTER TABLE TRANSACTIONS
ALTER PARTITION 13
ENDING AT ('2026-01-31');
-- Detach oldest partition (now offline, data preserved)
ALTER TABLE TRANSACTIONS
DETACH PARTITION 1
INTO TABLE TRANSACTIONS_202501_ARCHIVE;
Partition detach is a metadata-only operation — it completes in milliseconds regardless of the partition's size. Compare this to DELETE FROM TRANSACTIONS WHERE TXN_DATE < '2025-02-01', which generates millions of log records and holds locks for the duration.
The archive-on-demand pattern: Batch programs that archive data based on business rules rather than simple age. For example, CNB archives completed loan records only after the loan has been fully paid and all regulatory hold periods have expired:
4000-ARCHIVE-COMPLETED-LOANS.
EXEC SQL
DECLARE ARCHIVE-CURSOR CURSOR WITH HOLD FOR
SELECT LOAN_ID, LOAN_DATA
FROM LOANS
WHERE LOAN_STATUS = 'PAID'
AND PAYOFF_DATE < CURRENT DATE - 7 YEARS
AND REGULATORY_HOLD = 'N'
ORDER BY LOAN_ID
END-EXEC
EXEC SQL OPEN ARCHIVE-CURSOR END-EXEC
PERFORM UNTIL WS-END-OF-DATA
EXEC SQL
FETCH ARCHIVE-CURSOR
INTO :WS-LOAN-ID, :WS-LOAN-DATA
END-EXEC
IF SQLCODE = 0
PERFORM 4100-INSERT-ARCHIVE
PERFORM 4200-DELETE-ACTIVE
ADD 1 TO WS-COMMIT-COUNTER
IF WS-COMMIT-COUNTER >= WS-COMMIT-INTERVAL
PERFORM 5000-COMMIT-CHECKPOINT
END-IF
ELSE IF SQLCODE = 100
SET WS-END-OF-DATA TO TRUE
ELSE
PERFORM 8000-SQL-ERROR
END-IF
END-PERFORM.
12.7 Anti-Patterns: The Patterns That Work Until They Don't
Every anti-pattern in this section was once a reasonable decision that became a production crisis. They're listed here not to shame anyone but because you will encounter them in legacy code and need to recognize them before they bite.
Anti-Pattern 1: The Universal Cursor
A single cursor with dozens of optional predicates designed to handle every possible query:
SELECT * FROM ACCOUNTS
WHERE (:WS-ACCT = '' OR ACCOUNT_KEY = :WS-ACCT)
AND (:WS-NAME = '' OR CUSTOMER_NAME LIKE :WS-NAME)
AND (:WS-REGION = '' OR REGION = :WS-REGION)
AND (:WS-TYPE = '' OR ACCOUNT_TYPE = :WS-TYPE)
AND (:WS-MIN-BAL = 0 OR ACCOUNT_BAL >= :WS-MIN-BAL)
AND (:WS-STATUS = '' OR STATUS = :WS-STATUS)
ORDER BY ACCOUNT_KEY;
Why it fails: The DB2 optimizer sees this query once, at BIND time, and must choose a single access path that works for all combinations. It typically chooses a tablespace scan because no index can efficiently serve all variants. A query that should use an index on ACCOUNT_KEY does a full scan instead because the optimizer couldn't assume ACCOUNT_KEY would be provided.
The fix: Multiple cursors, each optimized for its access path (the cursor pool pattern from section 12.3).
Anti-Pattern 2: The Uncommitted Marathon
A batch program that processes millions of rows without committing, either because the developer forgot or because "it's simpler without restart logic":
* The program that brought down CNB's Tuesday night batch
PERFORM UNTIL END-OF-FILE
EXEC SQL FETCH CUR INTO :WS-ROW END-EXEC
IF SQLCODE = 0
PERFORM PROCESS-ROW
END-IF
END-PERFORM
EXEC SQL COMMIT END-EXEC
Why it fails spectacularly: 1. Lock escalation occurs around row 1,000 — now holding a table lock that blocks all other access 2. Active log fills up around row 500,000 — DB2 forces an archive switch, stalling all applications 3. If the program abends at row 4,999,999 out of 5,000,000, everything rolls back and you start over 4. UNDO processing for a rollback of 5 million rows can take longer than the original processing
The fix: The commit-checkpoint pattern from section 12.2. Always. No exceptions.
Anti-Pattern 3: Dynamic SQL String Concatenation
Building SQL by concatenating user input without parameter markers:
* Found in SecureFirst's legacy claims system - 2009 vintage
STRING 'SELECT CLAIM_ID, AMOUNT FROM CLAIMS '
'WHERE CLAIMANT_NAME = '''
WS-USER-INPUT-NAME
''' AND STATUS = ''OPEN'''
DELIMITED BY SIZE
INTO WS-SQL-STMT
END-STRING
If WS-USER-INPUT-NAME contains O'Brien, the SQL breaks. If it contains ' OR '1'='1' --, every open claim is returned. If it contains '; DROP TABLE CLAIMS; --, well, that depends on DYNAMICRULES, but you shouldn't be testing that in production.
The fix: Parameter markers, input validation, and audit logging — the five-layer strategy from section 12.4.
Anti-Pattern 4: The Cursor That Ate the Workfile
An unrestricted scrollable cursor or a cursor with an ORDER BY on a non-indexed column that forces DB2 to materialize the entire result set in a workfile:
DECLARE MONSTER-CURSOR SCROLL CURSOR FOR
SELECT * FROM TRANSACTIONS
WHERE TXN_DATE BETWEEN '2025-01-01' AND '2025-12-31'
ORDER BY AMOUNT DESC;
On a table with 200 million rows, this materializes tens of gigabytes into DB2 workfiles, consuming DASD, stalling other workfile users, and potentially filling the workfile database entirely.
The fix: Always limit result sets (FETCH FIRST n ROWS ONLY), ensure ORDER BY columns are indexed, and avoid SCROLL on large result sets.
Anti-Pattern 5: The Thread Hog
A CICS transaction that acquires a DB2 thread and holds it while performing non-DB2 work (VSAM I/O, MQ messaging, external service calls):
* This holds a DB2 thread during the entire MQ exchange
EXEC SQL SELECT ... INTO ... END-EXEC
EXEC CICS WRITEQ TS ... END-EXEC
PERFORM COMPLEX-CALCULATION
EXEC CICS LINK PROGRAM('MQSEND') ... END-EXEC
EXEC CICS LINK PROGRAM('MQRECV') ... END-EXEC
EXEC SQL UPDATE ... END-EXEC
EXEC CICS SYNCPOINT END-EXEC
Why it fails: The DB2 thread is held from the first SQL until SYNCPOINT, including all the non-DB2 processing. If MQRECV waits 500ms for a response, that's 500ms of wasted thread capacity. Multiply by 2,000 concurrent transactions and you exhaust the thread pool.
The fix: Restructure to minimize the window between first SQL and SYNCPOINT. Do non-DB2 work first, then enter a tight SQL sequence:
* Do non-DB2 work first
EXEC CICS LINK PROGRAM('MQSEND') ... END-EXEC
EXEC CICS LINK PROGRAM('MQRECV') ... END-EXEC
PERFORM COMPLEX-CALCULATION
* Tight DB2 window
EXEC SQL SELECT ... INTO ... END-EXEC
EXEC SQL UPDATE ... END-EXEC
EXEC CICS SYNCPOINT END-EXEC
Anti-Pattern 6: The Orphaned Prepared Statement
Dynamic SQL programs that PREPARE a statement inside a loop without reusing the prepared statement handle:
* New PREPARE every iteration — 50,000 catalog lookups
PERFORM VARYING WS-IDX FROM 1 BY 1
UNTIL WS-IDX > WS-REGION-COUNT
STRING 'UPDATE ACCOUNTS SET FLAG = ''Y'' '
'WHERE REGION = ?'
DELIMITED BY SIZE
INTO WS-SQL-TEXT
END-STRING
EXEC SQL PREPARE DYNSTMT FROM :WS-SQL-TEXT END-EXEC
EXEC SQL EXECUTE DYNSTMT USING :WS-REGION(WS-IDX)
END-EXEC
END-PERFORM
Why it fails: Each PREPARE consumes DB2 EDM pool storage for the statement cache entry. In a loop, you create thousands of identical prepared statements, exhausting the EDM pool and forcing expensive page replacements. The DB2 catalog is accessed for authorization checking on each PREPARE. For a loop of 50,000 iterations, this adds 50,000 unnecessary catalog lookups and can saturate the EDM pool, degrading every other DB2 application sharing the subsystem.
The fix: Prepare once outside the loop, execute many times inside:
* Prepare ONCE, execute MANY
MOVE 'UPDATE ACCOUNTS SET FLAG = ''Y'' WHERE REGION = ?'
TO WS-SQL-TEXT
EXEC SQL PREPARE DYNSTMT FROM :WS-SQL-TEXT END-EXEC
PERFORM VARYING WS-IDX FROM 1 BY 1
UNTIL WS-IDX > WS-REGION-COUNT
EXEC SQL EXECUTE DYNSTMT USING :WS-REGION(WS-IDX)
END-EXEC
END-PERFORM
Carlos at SecureFirst found this anti-pattern in a monthly regulatory reporting program that prepared the same SELECT statement 12,000 times — once per report row. After refactoring to prepare-once/execute-many, the program's elapsed time dropped from 47 minutes to 9 minutes, and the DB2 EDM pool hit ratio for the entire subsystem improved by 8%.
Anti-Pattern 7: FETCH Without OPTIMIZE FOR
Batch programs that declare cursors without the OPTIMIZE FOR clause, causing DB2 to use single-row prefetch instead of sequential prefetch:
-- DB2 assumes one row at a time (default optimization)
DECLARE BATCH-CUR CURSOR WITH HOLD FOR
SELECT ACCOUNT_KEY, ACCOUNT_BAL
FROM ACCOUNTS
WHERE PROCESS_FLAG = 'P'
ORDER BY ACCOUNT_KEY;
When DB2's optimizer sees no hint about how many rows will be fetched, it may choose an access path optimized for fetching a few rows (nested loop join, no list prefetch). For a batch program that will fetch millions of rows, this is catastrophically wrong.
The fix: Tell DB2 how many rows you expect:
DECLARE BATCH-CUR CURSOR WITH HOLD FOR
SELECT ACCOUNT_KEY, ACCOUNT_BAL
FROM ACCOUNTS
WHERE PROCESS_FLAG = 'P'
ORDER BY ACCOUNT_KEY
OPTIMIZE FOR 5000 ROWS;
OPTIMIZE FOR 5000 ROWS does not limit the result set — you can still fetch every matching row. It tells the optimizer to choose an access path that is efficient for retrieving many rows: sequential prefetch, merge scan join, and larger sort work areas. At CNB, adding OPTIMIZE FOR to three batch cursors that processed the full ACCOUNTS table reduced total batch I/O by 35% because DB2 switched from single-page reads to sequential prefetch reading 32 pages at a time.
Anti-Pattern 8: Ignoring SQLCODE -911 and -913
These are timeout and deadlock SQLCODEs. The anti-pattern is treating them as fatal errors:
* The program that sent 200 abend pages to operations
IF SQLCODE NOT = 0
DISPLAY 'SQL ERROR: ' SQLCODE
EXEC CICS ABEND ABCODE('SQER') END-EXEC
END-IF
Timeouts and deadlocks are normal in high-concurrency environments. The correct response is to retry:
8000-SQL-ERROR.
EVALUATE SQLCODE
WHEN -911
WHEN -913
* Timeout/deadlock - retry up to 3 times
ADD 1 TO WS-RETRY-COUNT
IF WS-RETRY-COUNT <= 3
EXEC SQL ROLLBACK END-EXEC
PERFORM 1500-WAIT-AND-RETRY
ELSE
PERFORM 8500-FATAL-ERROR
END-IF
WHEN -904
* Resource unavailable - log and retry later
PERFORM 8100-RESOURCE-UNAVAIL
WHEN OTHER
PERFORM 8500-FATAL-ERROR
END-EVALUATE.
12.8 Spaced Review: Connecting Back
Before we proceed to the project checkpoint, let's connect this chapter's patterns back to foundational concepts from earlier chapters.
From Chapter 6 (The Optimizer): Every cursor declaration in this chapter has optimizer implications. The FOR FETCH ONLY clause on read-only cursors enables list prefetch and parallelism. The partition-aware cursor with key range predicates enables partition pruning. The universal cursor anti-pattern defeats the optimizer because it cannot assume which predicates will be supplied. When you design a cursor, think about what access path the optimizer will choose — run EXPLAIN before committing to a pattern.
From Chapter 7 (Advanced SQL): The MERGE statement in the commit-checkpoint pattern (section 12.2) is a direct application of Chapter 7's set-based operations. The INSERT ... SELECT in the history pattern (section 12.6) uses a subselect pattern. Dynamic SQL's PREPARE/EXECUTE echoes the distinction between static and dynamic SQL introduced in Chapter 7.
From Chapter 8 (Locking): Every commit interval decision is a locking decision. WITH HOLD cursors maintain positioning locks across commits. Positioned updates (WHERE CURRENT OF) use update locks. The uncommitted marathon anti-pattern is a lock escalation trigger. The thread hog anti-pattern extends lock duration unnecessarily. If you don't have Chapter 8's locking model internalized, every pattern in this chapter is a gamble.
12.9 HA Banking System: DB2 Architecture Checkpoint
It's time to apply these patterns to the HA (High Availability) Banking Transaction Processing System you've been building throughout Parts 1 and 2.
Requirements Recap
The HA system must: - Process 2 million account transactions per nightly batch window (3.5 hours) - Support 5,000 concurrent online inquiries during business hours - Maintain full transaction history for 7 years (regulatory requirement) - Survive single-component failures without data loss - Support restart from any point in the batch cycle
DB2 Architecture Decisions
Table Partitioning. The TRANSACTIONS table is range-partitioned by month (12 active partitions). The ACCOUNTS table is range-partitioned by account key (8 partitions) to enable parallel batch processing.
Batch Architecture. The nightly cycle uses the commit-checkpoint pattern with a 2,000-row interval. Eight parallel jobs process one ACCOUNTS partition each. Each job writes to a shared BATCH_RESTART table with its partition number as part of the key.
01 WS-HA-BATCH-CONFIG.
05 WS-PARTITION-NUM PIC S9(4) COMP.
05 WS-COMMIT-INTERVAL PIC S9(9) COMP VALUE 2000.
05 WS-PROGRAM-ID PIC X(8) VALUE 'HABATCH'.
05 WS-RESTART-KEY.
10 WS-RST-PART PIC 9(4).
10 WS-RST-ACCT PIC X(10).
Online Inquiry. CICS transactions use the cursor pool pattern with four cursors: by account number, by customer name, by SSN hash, and by date range. DB2ENTRY is configured with PROTECTNUM based on time-of-day volume analysis.
History Management. The TRANSACTIONS table uses DB2 temporal tables with a 7-year SYSTEM_TIME retention. Archival uses monthly partition detach — at the start of each month, the oldest partition (now 7 years + 1 month old) is detached to an archive table and eventually offloaded to tape.
Dynamic SQL. The HA system's report generator uses parameterized dynamic SQL with all five security layers. Reports are generated during the batch window using read-only cursors with FETCH FIRST 50000 ROWS ONLY to prevent workfile exhaustion.
Your Checkpoint Task
For the project checkpoint (see code/project-checkpoint.md), you'll implement:
- The batch processing mainline for one partition, including commit-checkpoint logic and restart capability
- The CICS inquiry program with cursor pool pattern
- The DDL for temporal history tables
- The batch restart table and its associated logic
This is the capstone of Part 2's DB2 content. Everything from Chapters 6 through 12 converges here.
Chapter Summary
This chapter covered the structural patterns that separate DB2-COBOL programs that work in development from those that survive production:
-
Batch patterns center on the commit-checkpoint architecture, where commit frequency, cursor lifecycle, and restart logic form a unified strategy. The optimal commit interval balances lock escalation, log consumption, restart granularity, and predictable elapsed time.
-
Cursor management at scale requires deliberate choices about WITH HOLD, positioned vs. searched updates, cursor pools for CICS, and careful use of scrollable cursors. The WITH HOLD drain lock is a hidden constraint that demands cursor lifecycle management.
-
Dynamic SQL in COBOL uses PREPARE/EXECUTE with parameter markers as the foundational security mechanism. The five-layer injection prevention strategy — parameter markers, input validation, DYNAMICRULES BIND, audit logging, and prepared statement caching — provides defense in depth.
-
CICS-DB2 thread management governs the connection between CICS transactions and DB2 resources. Thread types, DB2CONN configuration, protected threads, and package collection design all affect throughput.
-
Data architecture patterns — partitioning, history tables (especially temporal tables), and archival via partition detach — are structural decisions that determine whether a system can maintain performance as data grows over years.
-
Anti-patterns — the universal cursor, uncommitted marathon, string concatenation in dynamic SQL, unbounded scrollable cursors, thread hogs, and fatal-error-on-deadlock — are the patterns that work in development and fail in production. Recognize them in legacy code and eliminate them.
Every pattern in this chapter is a structural decision, not a coding trick. Make these decisions during design, not during a 2 AM production crisis. And when you inherit code that doesn't follow these patterns — and you will, because every enterprise COBOL shop has decades of legacy — use the anti-pattern catalog to diagnose the risk and the pattern catalog to plan the remediation. The programs that survive at scale are not the cleverest ones; they are the ones built on sound structural foundations.
Next chapter: Chapter 13 takes you into DB2 stored procedures and user-defined functions in COBOL — moving logic from the application into the database engine, with all the performance implications and governance challenges that entails.