Key Takeaways: DB2 Application Patterns

Batch DB2 Patterns

  1. The commit-checkpoint architecture is the foundational batch pattern. Commit frequency, cursor lifecycle, and restart logic are not separate concerns — they form a unified strategy. Design them together before writing the first line of COBOL.

  2. Optimal commit intervals balance four factors: lock escalation threshold, active log consumption, restart granularity, and predictable elapsed time. For most transactional batch programs, 500-5,000 rows per commit is the sweet spot.

  3. The restart key must be the cursor's ordering key. Store it in a DB2 table (not a flat file) so the restart state and the data changes are in the same commit scope. Use MERGE for the restart table to handle both first-run and subsequent-commit cases atomically.

  4. Cursor-based processing beats set-based at extreme volumes when you need procedural logic, commit checkpointing, row-level error handling, or lock containment. Set-based wins when the transformation is pure SQL and the volume fits within lock and log constraints.

  5. Partition-aware batch processing is the single most effective technique for reducing elapsed time. Eight partitions processed in parallel reduce wall-clock time by up to 8x while containing lock scope to individual partitions.

Cursor Management at Scale

  1. WITH HOLD is an architectural decision, not a syntax detail. It enables commit-checkpoint processing but imposes drain locks that block utilities. Manage the cursor lifecycle — periodic close-and-reopen releases drain locks without losing the commit-checkpoint architecture.

  2. Positioned updates (WHERE CURRENT OF) save an index probe per row but restrict the optimizer to update-compatible access paths. For programs that update a high percentage of fetched rows, positioned updates win. For selective updates (updating 10% of fetched rows), a read-only cursor with searched updates allows prefetch and parallelism.

  3. The cursor pool pattern replaces the universal cursor. Multiple cursors, each optimized for a specific access path, outperform a single cursor with optional predicates. The optimizer can choose the right index for each cursor because the predicates are fixed at BIND time.

  4. Scrollable cursors materialize the result set. Always limit result sets with FETCH FIRST n ROWS ONLY. Never use scrollable cursors on unbounded queries — the workfile consumption will degrade the entire DB2 subsystem.

Dynamic SQL Security

  1. Parameter markers are the primary defense against SQL injection. They are bound as typed values, never interpolated as text. They cannot contain SQL syntax or modify query structure. Every user-supplied value must go through a parameter marker — no exceptions.

  2. The five-layer defense strategy provides depth:

    • Layer 1: Parameter markers for all values
    • Layer 2: Input validation (format, range, whitelist)
    • Layer 3: DYNAMICRULES(BIND) to restrict authorization scope
    • Layer 4: Audit logging of all dynamic SQL executions
    • Layer 5: Prepared statement caching to fix SQL structure after first preparation

  3. Never concatenate user input into SQL text. Not for sort columns, not for table names, not for WHERE clause fragments. Use whitelists (EVALUATE statements that map user choices to hardcoded SQL fragments) for structural elements that cannot use parameter markers.

  4. Audit dynamic SQL execution, not just errors. Log every PREPARE with the SQL text. Log every EXECUTE with parameter values. Secure the audit table against modification. The absence of detected attacks means nothing if you have no detection capability.

CICS-DB2 Thread Management

  1. Thread type selection matters for throughput. Pool threads are efficient for low-frequency SQL callers. Entry threads with protected threads (PROTECTNUM) eliminate thread creation overhead for high-volume transactions. Size PROTECTNUM based on time-of-day volume analysis — don't waste resources during off-hours.

  2. Minimize DB2 thread hold time in CICS transactions. Perform non-DB2 work (MQ messaging, VSAM I/O, complex calculations) before entering the DB2 sequence. Keep the window between first SQL and SYNCPOINT as tight as possible.

  3. Consolidate plans to improve thread reuse. Group related programs into packages under a small number of plans. Thread pools serve plans, not programs — fewer plans means better thread reuse rates.

Data Architecture Patterns

  1. Partition by the dimension you query, archive, and parallelize. Date-range partitioning enables monthly archival via instant partition detach. Key-range partitioning enables parallel batch processing. Choose based on your primary operational need.

  2. Temporal tables are the modern answer to history management. They eliminate application-level history code across all programs that modify the table. Point-in-time queries (FOR SYSTEM_TIME AS OF) satisfy auditors without custom reporting code.

  3. Partition detach is the only acceptable archival mechanism at scale. Mass DELETE generates millions of log records, holds locks for the duration, and leaves dead space. Partition detach is a metadata-only operation that completes in milliseconds.

Anti-Patterns to Recognize and Eliminate

  1. Six patterns that work until they don't:
    • The universal cursor (defeats the optimizer)
    • The uncommitted marathon (triggers lock escalation and log exhaustion)
    • Dynamic SQL string concatenation (SQL injection vector)
    • Unbounded scrollable cursors (workfile exhaustion)
    • The thread hog (holds DB2 threads during non-DB2 work)
    • Fatal-error-on-deadlock (turns normal concurrency events into production incidents)

Every one of these works in development. Every one fails at production scale. Recognize them in legacy code and replace them with the patterns from this chapter.