Chapter 32 Key Takeaways

Core Concepts

  1. Embedded SQL embeds SQL statements directly in host-language programs (COBOL, C, PL/I, Fortran) using EXEC SQL ... END-EXEC delimiters. DB2's precompiler extracts the SQL and replaces it with runtime calls, producing a DBRM for binding and modified source for compilation.

  2. The lifecycle is: Precompile, Compile, Link-Edit, BIND. Each step produces a specific artifact. The DBRM and the load module are connected at runtime through the plan/package. A timestamp consistency token ensures they match.

  3. Host variables bridge the gap between COBOL data and SQL data. Declared within BEGIN/END DECLARE SECTION, they must map precisely to DB2 column types. VARCHAR requires a special level-49 two-part structure. Indicator variables (PIC S9(4) COMP) handle NULLs — always use them for nullable columns.

  4. The SQLCA is your diagnostic lifeline. SQLCODE tells you success (0), not-found (+100), or error (negative). SQLERRD(3) gives row counts. SQLSTATE provides portable error classification. Always check SQLCODE after every SQL statement.

  5. Cursors are essential for multi-row processing. The lifecycle is DECLARE, OPEN, FETCH (loop), CLOSE. Use WITH HOLD for cursors that must survive COMMIT. Use FOR UPDATE OF for positioned updates and deletes.

  6. Multi-row FETCH and INSERT dramatically improve batch performance by reducing the number of SQL calls. Rowset sizes of 100-1,000 are typical. SQLERRD(3) tells you how many rows were actually returned in the last FETCH.

  7. Static SQL is bound at BIND time — access paths, authorization, and validation are all predetermined. This eliminates runtime overhead and provides predictable performance, making it ideal for high-volume batch processing.

  8. Dynamic SQL is built and prepared at runtime — necessary when the SQL text varies (different tables, different WHERE columns). Use PREPARE once and EXECUTE many times when possible. Always use parameter markers to maximize dynamic statement cache hits.

  9. The BIND process is where optimization happens. Key options include ISOLATION (lock behavior), QUALIFIER (schema resolution), CURRENTDATA (block fetch eligibility), and RELEASE (lock duration). REBIND refreshes access paths after statistics changes.

  10. Packages provide modular binding. One DBRM per package, referenced by plans through package lists. This allows independent maintenance — rebind one package without affecting others.

Meridian Bank Patterns

  • End-of-day batch programs use WITH HOLD cursors, periodic commits (every 500-1,000 rows), and comprehensive SQLCODE checking.
  • Transaction posting combines file I/O with database updates, using individual commits per logical unit of work.
  • Interest calculation uses positioned UPDATE (WHERE CURRENT OF) for row-by-row accuracy with cursor-driven processing.

Common Pitfalls to Avoid

  • Forgetting indicator variables for nullable columns (causes SQLCODE -305)
  • Not checking SQLCODE after every SQL statement
  • Using WHENEVER GO TO without remembering it persists until overridden
  • Forgetting WITH HOLD on cursors in programs that commit periodically
  • Mismatched precompile and bind (causes SQLCODE -818)
  • Using EXECUTE IMMEDIATE in loops instead of PREPARE once / EXECUTE many