Chapter 27 Key Takeaways

  1. EXEC SQL ... END-EXEC encloses every SQL statement in a COBOL program. Each block contains exactly one SQL statement. SQL syntax applies inside the block; COBOL syntax applies outside.

  2. Host variables bridge COBOL and SQL. They are ordinary COBOL data items prefixed with a colon (:) when used in SQL statements. COMP-3 maps to DECIMAL, COMP maps to INTEGER, PIC X maps to CHAR.

  3. Always check SQLCODE after every SQL statement. SQLCODE 0 = success, +100 = not found, negative = error. This is non-negotiable defensive programming.

  4. The SQLCA (SQL Communication Area) is populated after every SQL statement. Beyond SQLCODE, SQLERRD(3) tells you how many rows were affected, and SQLERRMC contains the error message text.

  5. Use DCLGEN to generate host variable declarations from DB2 tables. Never manually code what DCLGEN can generate. When tables change, regenerate the DCLGEN once rather than updating declarations in every program.

  6. Null indicator variables (PIC S9(4) COMP) are required for nullable columns. A negative indicator means the column value is NULL. Forgetting the indicator causes SQLCODE -305.

  7. SELECT INTO retrieves exactly one row. Use it for primary key lookups. It returns -811 if multiple rows match.

  8. Cursors (DECLARE, OPEN, FETCH, CLOSE) handle multi-row result sets. Always CLOSE cursors when done — open cursors consume DB2 resources. Close them in error handlers too.

  9. FOR UPDATE OF in cursor declarations acquires update locks for positioned UPDATE/DELETE operations using WHERE CURRENT OF. Without it, concurrent transactions can cause lost updates.

  10. COMMIT and ROLLBACK control transaction boundaries. In batch programs, commit periodically (every N records) to balance performance and recovery. Never commit only at end-of-job for large batches.

  11. The precompiler process transforms EXEC SQL into COBOL CALL statements and produces a DBRM. The DBRM must be BOUND to DB2 to create an executable package. Timestamp mismatches between the load module and the package cause SQLCODE -818.

  12. WHENEVER provides automatic error branching but uses lexical (not dynamic) scoping. Use it as a safety net, but prefer explicit SQLCODE checking for precise error handling.

  13. Think in sets, not records. A single UPDATE with a WHERE clause is almost always better than fetching rows one at a time and updating each one. Resist the temptation to write "VSAM with SQL syntax."