Key Takeaways: Advanced DB2 Programming

  1. Dynamic SQL enables runtime flexibility but introduces security risks and runtime failure modes. Use EXECUTE IMMEDIATE for one-time non-SELECT statements, PREPARE/EXECUTE for repeated parameterized statements, and PREPARE with CURSOR for dynamic queries that return result sets.

  2. Parameter markers (?) are non-negotiable for any dynamic SQL that incorporates external values. They prevent SQL injection and enable DB2 plan cache reuse. Never concatenate user-supplied values directly into SQL strings.

  3. DCLGEN bridges DB2 and COBOL by generating host variable declarations that match table definitions. Always use EXEC SQL INCLUDE (not COPY), regenerate after every DDL change, and never hand-edit DCLGEN output.

  4. Indicator variables are mandatory for nullable columns. Without them, a NULL value causes SQLCODE -305 and program failure. Set indicator to -1 to store NULL, check for -1 on retrieval, and watch for -2 (truncation).

  5. Scrollable cursors enable bidirectional navigation with FETCH FIRST, LAST, PRIOR, NEXT, ABSOLUTE, and RELATIVE. Use them for interactive browse screens, but prefer forward-only cursors for batch processing due to lower overhead.

  6. Multi-row FETCH and INSERT dramatically reduce overhead in batch programs by minimizing context switches between COBOL and DB2. A rowset size of 100 typically provides the best cost-benefit ratio.

  7. Stored procedures centralize business logic on the database server, reducing network round trips and enforcing rules regardless of which application accesses the data. Understand parameter modes (IN, OUT, INOUT) and result set handling.

  8. Performance tuning starts with EXPLAIN. Watch for tablespace scans (ACCESSTYPE='R'), low MATCHCOLS values, and unnecessary sorts. Design indexes to match your most common query predicates, with columns in left-to-right matching order.

  9. COMMIT strategy is a critical design decision. Commit-per-row is almost always wrong (excessive overhead). Find the sweet spot — typically every 100-1,000 rows — that balances lock duration, restart granularity, and log volume. Use WITH HOLD cursors to keep cursors open across COMMITs.

  10. Deadlock handling requires retry logic with limits. When DB2 returns SQLCODE -911, it has already rolled back your work. Retry the operation up to a defined limit, then fail gracefully. Use lock ordering (consistent lock acquisition sequence) to prevent deadlocks proactively.

  11. Isolation levels control the trade-off between concurrency and consistency. UR allows maximum concurrency but dirty reads. CS (the default) is appropriate for most online transactions. RS and RR provide stronger consistency at the cost of holding locks longer.

  12. The N+1 query problem is the most common performance anti-pattern in COBOL-DB2 programs: fetching rows from one table and performing individual lookups in another table inside the loop. The fix is almost always a JOIN.