Chapter 22: Embedded SQL and DB2 Fundamentals -- Key Takeaways

Chapter Summary

Embedded SQL is the mechanism by which COBOL programs interact with IBM's DB2 relational database management system, and it represents one of the most critical skills in mainframe enterprise development. This chapter introduced the fundamental concepts of embedding SQL statements within COBOL source code, exchanging data between COBOL data items and DB2 tables through host variables, and managing the precompile-compile-bind process that transforms a COBOL-DB2 program into an executable that can access relational data.

Every SQL statement in a COBOL program is delimited by EXEC SQL and END-EXEC markers, which tell the DB2 precompiler to process the enclosed SQL and generate the necessary COBOL code to communicate with the DB2 subsystem at runtime. Host variables are COBOL data items declared within an EXEC SQL INCLUDE section or in WORKING-STORAGE that serve as the bridge between COBOL's data structures and DB2's relational columns. We covered DCLGEN (Declarations Generator), which automatically generates COBOL host variable declarations from DB2 table definitions, ensuring that COBOL data types correctly match the corresponding DB2 column types.

The SQLCA (SQL Communication Area) is the structure through which DB2 reports the outcome of every SQL operation back to the COBOL program. The SQLCODE field within the SQLCA is the primary diagnostic indicator: a value of 0 means success, a positive value indicates a warning condition (with +100 meaning "not found"), and a negative value signals an error. The chapter covered singleton SELECT for retrieving a single row, cursor processing for handling multi-row result sets through DECLARE CURSOR, OPEN, FETCH, and CLOSE, and the INCLUDE statement for incorporating DCLGEN output and the SQLCA into the program. Finally, we walked through the DB2 precompile, compile, link, and bind process that produces both the load module and the DB2 package or plan.

Key Concepts

EXEC SQL and END-EXEC delimit every SQL statement embedded in a COBOL program; the DB2 precompiler processes everything between these markers.
Host variables are COBOL data items prefixed with a colon (:) when referenced within SQL statements; they serve as input parameters and output receivers for SQL operations.
DCLGEN (Declarations Generator) reads DB2 catalog information for a table and generates COBOL copybook-style declarations that match each column's data type and length.
The SQLCA (SQL Communication Area) is included via EXEC SQL INCLUDE SQLCA END-EXEC and contains the SQLCODE field that reports the result of each SQL operation.
SQLCODE = 0 indicates successful execution, SQLCODE = +100 indicates no row found (or end of cursor), and negative SQLCODE values indicate errors.
A singleton SELECT INTO retrieves exactly one row from DB2 into host variables; if zero rows or more than one row match, SQLCODE indicates the error.
Cursor processing handles multi-row result sets: DECLARE CURSOR defines the query, OPEN executes the query, FETCH retrieves one row at a time into host variables, and CLOSE releases the cursor resources.
The DECLARE CURSOR statement is a compile-time declaration that does not execute at runtime; the OPEN statement triggers the actual query execution.
NULL-capable DB2 columns require indicator variables in COBOL, declared as PIC S9(4) COMP (halfword binary), which are set to -1 when the corresponding column value is NULL.
The EXEC SQL INCLUDE statement is functionally similar to COPY but is processed by the DB2 precompiler rather than the COBOL compiler, and is used to include DCLGEN output and the SQLCA.
The DB2 precompile step processes the COBOL source, extracts SQL statements into a DBRM (Database Request Module), and replaces SQL statements with COBOL CALL statements to the DB2 runtime interface.
The BIND process takes the DBRM and creates a DB2 package or plan, which contains the optimized access paths for the SQL statements and the authorization to execute them.
COMMIT and ROLLBACK control transaction boundaries; a COMMIT makes all changes permanent and a ROLLBACK undoes all changes since the last commit point.
The WHENEVER statement provides declarative error handling: EXEC SQL WHENEVER SQLERROR GO TO error-paragraph END-EXEC directs control to an error-handling paragraph when any SQL error occurs.

Common Pitfalls

Forgetting to check SQLCODE after every SQL statement: Failing to test SQLCODE after each SQL operation means errors go undetected, potentially causing incorrect data processing or silent data corruption.
Singleton SELECT returning multiple rows: If a SELECT INTO matches more than one row, DB2 returns SQLCODE = -811. Ensure the WHERE clause uniquely identifies a single row, or use a cursor.
Ignoring NULL indicator variables: When a DB2 column can contain NULL values, omitting the indicator variable causes a runtime error (SQLCODE = -305). Always declare and check indicator variables for nullable columns.
Not closing cursors: Failing to close a cursor wastes DB2 resources and can cause "resource unavailable" conditions in long-running programs or when the cursor is reopened.
DCLGEN type mismatches: Manually coding host variable declarations instead of using DCLGEN risks data type mismatches between COBOL and DB2, leading to truncation, conversion errors, or incorrect results.
Confusing precompiler INCLUDE with COBOL COPY: EXEC SQL INCLUDE is processed by the DB2 precompiler, while COPY is processed by the COBOL compiler. DCLGEN members and SQLCA must use INCLUDE, not COPY.
Committing too frequently or too infrequently: Committing after every row creates excessive overhead; never committing risks lock escalation and long rollback times. Commit at logical business transaction boundaries.
Using WHENEVER without understanding its scope: EXEC SQL WHENEVER applies to all subsequent SQL statements in the source, not just the next one. It remains in effect until overridden by another WHENEVER for the same condition.

Quick Reference

      * Include SQLCA and DCLGEN declarations
           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC SQL INCLUDE DCUSTMR  END-EXEC.

      * Host variable declarations (or from DCLGEN)
       01  HV-CUSTOMER.
           05  HV-CUST-ID        PIC X(10).
           05  HV-CUST-NAME      PIC X(30).
           05  HV-CUST-BAL       PIC S9(7)V99 COMP-3.
       01  HV-IND-BAL            PIC S9(4) COMP.

      * Singleton SELECT
           EXEC SQL
               SELECT CUST_NAME, CUST_BALANCE
               INTO :HV-CUST-NAME,
                    :HV-CUST-BAL :HV-IND-BAL
               FROM CUSTOMER
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC
           EVALUATE SQLCODE
               WHEN 0     CONTINUE
               WHEN +100  DISPLAY "NOT FOUND"
               WHEN OTHER PERFORM 9000-SQL-ERROR
           END-EVALUATE

      * Cursor processing
           EXEC SQL
               DECLARE CSR-CUST CURSOR FOR
               SELECT CUST_ID, CUST_NAME
               FROM CUSTOMER
               WHERE CUST_STATUS = 'A'
               ORDER BY CUST_NAME
           END-EXEC

           EXEC SQL OPEN CSR-CUST END-EXEC
           PERFORM UNTIL SQLCODE = +100
               EXEC SQL
                   FETCH CSR-CUST
                   INTO :HV-CUST-ID, :HV-CUST-NAME
               END-EXEC
               IF SQLCODE = 0
                   PERFORM 3000-PROCESS-CUSTOMER
               END-IF
           END-PERFORM
           EXEC SQL CLOSE CSR-CUST END-EXEC

      * Transaction control
           EXEC SQL COMMIT END-EXEC
           EXEC SQL ROLLBACK END-EXEC

What's Next

Chapter 23 advances into sophisticated DB2 programming techniques including dynamic SQL, scrollable cursors, stored procedures written in COBOL, multi-row FETCH with ROWSET positioning, large object (LOB) handling, and DB2 performance tuning. These advanced features build directly on the embedded SQL fundamentals covered here, enabling COBOL programs to handle complex data access patterns and large-scale data processing requirements found in enterprise applications.