Chapter 22: Embedded SQL and DB2 Fundamentals

IBM Enterprise COBOL

Introduction: COBOL Meets the Relational Database

For the first several decades of mainframe computing, COBOL programs stored and retrieved data exclusively through file systems -- sequential files, indexed files (VSAM KSDS), and relative files (VSAM RRDS). Every program that needed access to customer records, account balances, or transaction histories had to manage its own file I/O, its own record locking, its own index structures, and its own data integrity. If two programs needed to access the same data simultaneously, the application programmers had to coordinate that access themselves, a task that was error-prone and often led to data corruption.

IBM's DB2 relational database management system, first released in 1983, fundamentally changed this model. DB2 manages data storage, indexing, concurrent access, recovery, and integrity centrally. COBOL programs access DB2 data not through READ, WRITE, and REWRITE statements, but through SQL statements embedded directly in the COBOL source code. The DB2 precompiler translates these embedded SQL statements into CALL statements that invoke DB2's runtime services, making the COBOL-DB2 integration seamless.

Today, the majority of COBOL programs on IBM mainframes access DB2 databases. Banking systems, insurance policy administration, government benefits processing, and airline reservation systems all use COBOL-DB2 programs as their primary data access layer. Understanding embedded SQL in COBOL is not optional for enterprise COBOL programmers -- it is a core professional skill.

This chapter covers the fundamentals of embedded SQL in COBOL: the syntax for embedding SQL statements, host variables for data exchange, the SQLCA for error detection, single-row operations, cursor-based multi-row processing, indicator variables for NULL handling, and the precompile/bind process that prepares a COBOL-DB2 program for execution.

22.1 DB2 Overview

DB2 for z/OS is IBM's relational database management system for the mainframe platform. It stores data in tables (rows and columns), enforces data integrity through constraints and triggers, provides concurrent access through locking and isolation levels, and supports recovery through logging and backup facilities.

Key DB2 Concepts for COBOL Programmers

DB2 Data Types and COBOL Equivalents

22.2 EXEC SQL...END-EXEC Syntax

All SQL statements embedded in COBOL are delimited by the EXEC SQL and END-EXEC keywords:

           EXEC SQL
               SELECT CUST_NAME, CUST_BALANCE
               INTO :HV-CUST-NAME, :HV-CUST-BALANCE
               FROM CUSTOMER
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC

Rules for Embedded SQL

1. EXEC SQL begins the statement: It tells the precompiler that SQL follows.
2. END-EXEC terminates the statement: It tells the precompiler that the SQL statement is complete. Note: no period after END-EXEC unless it ends a COBOL sentence.
3. Host variables are prefixed with colon (:): When you reference a COBOL variable inside SQL, you prefix it with a colon. Outside SQL, you use the variable name without the colon.
4. SQL statements can span multiple lines: SQL does not need to fit on one line. Continue on subsequent lines within the EXEC SQL...END-EXEC delimiters.
5. SQL is not case-sensitive: SELECT, Select, and select are all equivalent in SQL (though uppercase is conventional).
6. COBOL comments are allowed between EXEC SQL and END-EXEC in most implementations.

Where SQL Statements Can Appear

Embedded SQL statements can appear in:

• WORKING-STORAGE SECTION: For INCLUDE statements (SQLCA, DCLGEN)
• PROCEDURE DIVISION: For all executable SQL statements (SELECT, INSERT, UPDATE, DELETE, OPEN, FETCH, CLOSE, COMMIT, ROLLBACK)

       WORKING-STORAGE SECTION.

      * Include the SQLCA
           EXEC SQL
               INCLUDE SQLCA
           END-EXEC

      * Include the DCLGEN for the CUSTOMER table
           EXEC SQL
               INCLUDE DCLCUST
           END-EXEC

       PROCEDURE DIVISION.
       2000-PROCESS.
      * Executable SQL in the PROCEDURE DIVISION
           EXEC SQL
               SELECT CUST_NAME
               INTO :HV-CUST-NAME
               FROM CUSTOMER
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC
           .

22.3 Host Variables

Host variables are COBOL data items that exchange data between the COBOL program and DB2. They serve two purposes: they provide input values to SQL statements (parameters for WHERE clauses, values for INSERT and UPDATE), and they receive output values from SQL statements (columns from SELECT).

Declaring Host Variables

Host variables are declared in the WORKING-STORAGE SECTION, optionally within a DECLARE SECTION:

       WORKING-STORAGE SECTION.

           EXEC SQL BEGIN DECLARE SECTION END-EXEC.

       01  HV-CUST-ID            PIC S9(9) COMP.
       01  HV-CUST-NAME          PIC X(30).
       01  HV-CUST-BALANCE       PIC S9(7)V99 COMP-3.
       01  HV-CUST-STATUS        PIC X(01).
       01  HV-TRANS-DATE         PIC X(10).
       01  HV-TRANS-AMOUNT       PIC S9(9)V99 COMP-3.

           EXEC SQL END DECLARE SECTION END-EXEC.

The BEGIN DECLARE SECTION and END DECLARE SECTION delimiters are optional in DB2 for z/OS but recommended for clarity and portability. They explicitly identify which variables are intended for use as host variables.

Naming Conventions

Host variables are commonly prefixed with HV- or H- to distinguish them from regular WORKING-STORAGE variables:

      * GOOD: Clear prefix identifies host variables
       01  HV-CUSTOMER-ID     PIC S9(9) COMP.
       01  HV-CUSTOMER-NAME   PIC X(30).

      * ALSO ACCEPTABLE: DCLGEN-generated names
       01  DCUST-ID            PIC S9(9) COMP.
       01  DCUST-NAME          PIC X(30).

The Colon Prefix in SQL

Inside an EXEC SQL block, host variables are identified by a colon (:) prefix. Outside SQL, the colon is not used:

      * Set the host variable (COBOL -- no colon)
           MOVE 12345 TO HV-CUST-ID

      * Use it in SQL (colon prefix required)
           EXEC SQL
               SELECT CUST_NAME, CUST_BALANCE
               INTO :HV-CUST-NAME, :HV-CUST-BALANCE
               FROM CUSTOMER
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC

      * Use the results (COBOL -- no colon)
           DISPLAY 'CUSTOMER: ' HV-CUST-NAME
           DISPLAY 'BALANCE:  ' HV-CUST-BALANCE

Data Type Mapping Rules

When declaring host variables, the COBOL data type must be compatible with the DB2 column data type:

      * DB2 INTEGER -> COBOL COMP (fullword binary)
       01  HV-CUST-ID         PIC S9(9) COMP.

      * DB2 SMALLINT -> COBOL COMP (halfword binary)
       01  HV-DEPT-CODE       PIC S9(4) COMP.

      * DB2 DECIMAL(9,2) -> COBOL COMP-3 (packed decimal)
       01  HV-SALARY          PIC S9(7)V99 COMP-3.

      * DB2 CHAR(30) -> COBOL PIC X
       01  HV-LAST-NAME       PIC X(30).

      * DB2 DATE -> COBOL PIC X(10) (format: YYYY-MM-DD)
       01  HV-HIRE-DATE       PIC X(10).

      * DB2 TIMESTAMP -> COBOL PIC X(26)
      * Format: YYYY-MM-DD-HH.MM.SS.NNNNNN
       01  HV-LAST-UPDATE     PIC X(26).

DCLGEN: Automatic Host Variable Generation

IBM provides the DCLGEN (Declaration Generator) utility, which examines a DB2 table definition and generates the corresponding COBOL host variable declarations and an SQL DECLARE TABLE statement. This ensures that your host variables exactly match the table definition:

      * Generated by DCLGEN utility:
           EXEC SQL DECLARE CUSTOMER TABLE
           ( CUST_ID           INTEGER         NOT NULL,
             CUST_NAME         CHAR(30)        NOT NULL,
             CUST_ADDRESS      VARCHAR(100),
             CUST_BALANCE      DECIMAL(9,2)    NOT NULL,
             CUST_STATUS       CHAR(1)         NOT NULL,
             LAST_UPDATE       TIMESTAMP       NOT NULL
           ) END-EXEC.

      * COBOL declaration for CUSTOMER
       01  DCLCUSTOMER.
           10  DCUST-ID        PIC S9(9) COMP.
           10  DCUST-NAME      PIC X(30).
           10  DCUST-ADDRESS.
               49  DCUST-ADDRESS-LEN PIC S9(4) COMP.
               49  DCUST-ADDRESS-TEXT PIC X(100).
           10  DCUST-BALANCE   PIC S9(7)V99 COMP-3.
           10  DCUST-STATUS    PIC X(1).
           10  DCUST-LAST-UPD  PIC X(26).

22.4 The SQLCA: SQL Communication Area

The SQLCA (SQL Communication Area) is a data structure that DB2 populates after every SQL statement execution. It contains the return code, error messages, and diagnostic information that your program uses to determine whether the SQL statement succeeded or failed.

Including the SQLCA

       WORKING-STORAGE SECTION.
           EXEC SQL
               INCLUDE SQLCA
           END-EXEC

Key SQLCA Fields

SQLCODE Values

The SQLCODE field is the primary mechanism for determining the outcome of an SQL statement:

Checking SQLCODE After Every SQL Statement

       2200-READ-CUSTOMER.
           EXEC SQL
               SELECT CUST_NAME, CUST_BALANCE
               INTO :HV-CUST-NAME, :HV-CUST-BALANCE
               FROM CUSTOMER
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC

           EVALUATE SQLCODE
               WHEN 0
                   SET WS-CUSTOMER-FOUND TO TRUE
               WHEN +100
                   SET WS-CUSTOMER-NOT-FOUND TO TRUE
               WHEN -811
                   DISPLAY 'DUPLICATE CUSTOMER IDS: '
                       HV-CUST-ID
                   SET WS-DATA-ERROR TO TRUE
               WHEN OTHER
                   DISPLAY 'SQL ERROR: ' SQLCODE
                   DISPLAY 'SQLSTATE:  ' SQLSTATE
                   PERFORM 9100-SQL-ERROR
           END-EVALUATE
           .

Centralized SQL Error Handler

       9100-SQL-ERROR.
           DISPLAY '*** SQL ERROR ***'
           DISPLAY 'SQLCODE:   ' SQLCODE
           DISPLAY 'SQLSTATE:  ' SQLSTATE
           DISPLAY 'SQLERRMC:  ' SQLERRMC(1:SQLERRML)
           DISPLAY 'PARAGRAPH: ' WS-CURRENT-PARAGRAPH
           DISPLAY 'ROWS:      ' SQLERRD(3)
           MOVE 16 TO RETURN-CODE
           PERFORM 9900-ABORT
           .

22.5 VARCHAR Host Variables

DB2 VARCHAR columns store variable-length character data. In COBOL, VARCHAR host variables are represented using a special two-part structure with a 49-level layout:

       01  HV-CUSTOMER-ADDRESS.
           49  HV-CUST-ADDR-LEN  PIC S9(4) COMP.
           49  HV-CUST-ADDR-TEXT PIC X(100).

The 49-level structure tells the DB2 precompiler that this is a VARCHAR host variable. The first field (LEN) contains the actual length of the data. The second field (TEXT) contains the data itself, padded with spaces to its declared length.

Using VARCHAR Host Variables

      * Sending a VARCHAR value to DB2
           MOVE 42 TO HV-CUST-ADDR-LEN
           MOVE '123 Main Street, Springfield, IL 62701'
               TO HV-CUST-ADDR-TEXT

           EXEC SQL
               UPDATE CUSTOMER
               SET CUST_ADDRESS = :HV-CUSTOMER-ADDRESS
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC

      * Receiving a VARCHAR value from DB2
           EXEC SQL
               SELECT CUST_ADDRESS
               INTO :HV-CUSTOMER-ADDRESS
               FROM CUSTOMER
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC

           DISPLAY 'ADDRESS LENGTH: ' HV-CUST-ADDR-LEN
           DISPLAY 'ADDRESS: '
               HV-CUST-ADDR-TEXT(1:HV-CUST-ADDR-LEN)

22.6 Single-Row Operations: SELECT INTO

The SELECT INTO statement retrieves a single row from a DB2 table into host variables. It is used when you know that the query will return exactly zero or one row (typically when querying by primary key).

Basic SELECT INTO

       2200-GET-CUSTOMER.
           MOVE WS-INPUT-CUST-ID TO HV-CUST-ID

           EXEC SQL
               SELECT CUST_NAME,
                      CUST_BALANCE,
                      CUST_STATUS,
                      LAST_UPDATE
               INTO :HV-CUST-NAME,
                    :HV-CUST-BALANCE,
                    :HV-CUST-STATUS,
                    :HV-LAST-UPDATE
               FROM CUSTOMER
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC

           EVALUATE SQLCODE
               WHEN 0
                   PERFORM 2210-CUSTOMER-FOUND
               WHEN +100
                   PERFORM 2220-CUSTOMER-NOT-FOUND
               WHEN -811
                   DISPLAY 'MULTIPLE ROWS FOR KEY: '
                       HV-CUST-ID
                   PERFORM 9100-SQL-ERROR
               WHEN OTHER
                   PERFORM 9100-SQL-ERROR
           END-EVALUATE
           .

SELECT INTO with Expressions and Functions

      * Using SQL functions
           EXEC SQL
               SELECT COUNT(*),
                      SUM(TRANS_AMOUNT),
                      MAX(TRANS_DATE)
               INTO :HV-TRANS-COUNT,
                    :HV-TOTAL-AMOUNT,
                    :HV-LAST-TRANS-DATE
               FROM TRANSACTION
               WHERE ACCT_ID = :HV-ACCT-ID
                 AND TRANS_DATE >= :HV-START-DATE
           END-EXEC

22.7 INSERT, UPDATE, and DELETE

INSERT

       2300-ADD-CUSTOMER.
           MOVE WS-NEW-CUST-ID   TO HV-CUST-ID
           MOVE WS-NEW-CUST-NAME TO HV-CUST-NAME
           MOVE 0                 TO HV-CUST-BALANCE
           MOVE 'A'               TO HV-CUST-STATUS

           EXEC SQL
               INSERT INTO CUSTOMER
                   (CUST_ID, CUST_NAME, CUST_BALANCE,
                    CUST_STATUS, LAST_UPDATE)
               VALUES
                   (:HV-CUST-ID, :HV-CUST-NAME,
                    :HV-CUST-BALANCE, :HV-CUST-STATUS,
                    CURRENT TIMESTAMP)
           END-EXEC

           EVALUATE SQLCODE
               WHEN 0
                   ADD 1 TO CT-INSERTS
               WHEN -803
                   DISPLAY 'DUPLICATE CUSTOMER: '
                       HV-CUST-ID
                   ADD 1 TO CT-DUPLICATES
               WHEN OTHER
                   PERFORM 9100-SQL-ERROR
           END-EVALUATE
           .

UPDATE

       2400-UPDATE-BALANCE.
           EXEC SQL
               UPDATE CUSTOMER
               SET CUST_BALANCE = CUST_BALANCE
                                  + :HV-TRANS-AMOUNT,
                   LAST_UPDATE = CURRENT TIMESTAMP
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC

           EVALUATE SQLCODE
               WHEN 0
                   IF SQLERRD(3) = 0
                       DISPLAY 'NO ROW UPDATED FOR: '
                           HV-CUST-ID
                   ELSE
                       ADD 1 TO CT-UPDATES
                   END-IF
               WHEN OTHER
                   PERFORM 9100-SQL-ERROR
           END-EVALUATE
           .

DELETE

       2500-DELETE-CUSTOMER.
           EXEC SQL
               DELETE FROM CUSTOMER
               WHERE CUST_ID = :HV-CUST-ID
                 AND CUST_STATUS = 'I'
           END-EXEC

           EVALUATE SQLCODE
               WHEN 0
                   MOVE SQLERRD(3) TO WS-ROWS-DELETED
                   IF WS-ROWS-DELETED = 0
                       DISPLAY 'NO INACTIVE CUSTOMER: '
                           HV-CUST-ID
                   ELSE
                       ADD WS-ROWS-DELETED TO CT-DELETES
                   END-IF
               WHEN OTHER
                   PERFORM 9100-SQL-ERROR
           END-EVALUATE
           .

22.8 Cursors: Multi-Row Processing

When an SQL query returns multiple rows, you cannot use SELECT INTO (which expects exactly one row). Instead, you use a cursor -- a mechanism that lets you process the result set one row at a time, similar to reading a sequential file.

Cursor Lifecycle

The cursor lifecycle parallels file processing:

DECLARE CURSOR

The DECLARE CURSOR statement defines the SQL query associated with the cursor. It is a non-executable statement and is typically placed in WORKING-STORAGE:

       WORKING-STORAGE SECTION.
           EXEC SQL
               DECLARE CUST-CURSOR CURSOR FOR
               SELECT CUST_ID, CUST_NAME, CUST_BALANCE
               FROM CUSTOMER
               WHERE CUST_STATUS = :HV-STATUS-FILTER
               ORDER BY CUST_NAME
           END-EXEC

OPEN, FETCH, and CLOSE

       2000-PROCESS-CUSTOMERS.
           MOVE 'A' TO HV-STATUS-FILTER

           EXEC SQL
               OPEN CUST-CURSOR
           END-EXEC
           IF SQLCODE NOT = 0
               DISPLAY 'CURSOR OPEN ERROR: ' SQLCODE
               PERFORM 9100-SQL-ERROR
           END-IF

           PERFORM 2100-FETCH-CUSTOMER
           PERFORM UNTIL SQLCODE = +100
               PERFORM 2200-PROCESS-CUSTOMER
               PERFORM 2100-FETCH-CUSTOMER
           END-PERFORM

           EXEC SQL
               CLOSE CUST-CURSOR
           END-EXEC
           IF SQLCODE NOT = 0
               DISPLAY 'CURSOR CLOSE ERROR: ' SQLCODE
           END-IF
           .

       2100-FETCH-CUSTOMER.
           EXEC SQL
               FETCH CUST-CURSOR
               INTO :HV-CUST-ID,
                    :HV-CUST-NAME,
                    :HV-CUST-BALANCE
           END-EXEC

           EVALUATE SQLCODE
               WHEN 0
                   ADD 1 TO CT-ROWS-FETCHED
               WHEN +100
                   CONTINUE
               WHEN OTHER
                   PERFORM 9100-SQL-ERROR
           END-EVALUATE
           .

       2200-PROCESS-CUSTOMER.
           DISPLAY HV-CUST-ID ' '
                   HV-CUST-NAME ' '
                   HV-CUST-BALANCE
           .

WITH HOLD Cursor

A normal cursor is automatically closed when a COMMIT is issued. A WITH HOLD cursor remains open across COMMIT operations, which is essential for long-running batch programs that commit periodically:

           EXEC SQL
               DECLARE BATCH-CURSOR CURSOR WITH HOLD FOR
               SELECT CUST_ID, CUST_NAME, CUST_BALANCE
               FROM CUSTOMER
               WHERE CUST_STATUS = 'A'
               ORDER BY CUST_ID
           END-EXEC

FOR UPDATE Cursor

If you intend to update or delete rows as you process them through a cursor, declare the cursor FOR UPDATE:

           EXEC SQL
               DECLARE UPD-CURSOR CURSOR FOR
               SELECT CUST_ID, CUST_NAME, CUST_BALANCE
               FROM CUSTOMER
               WHERE CUST_STATUS = 'A'
               FOR UPDATE OF CUST_BALANCE, LAST_UPDATE
           END-EXEC

       2300-UPDATE-CURRENT-ROW.
           EXEC SQL
               UPDATE CUSTOMER
               SET CUST_BALANCE = CUST_BALANCE *
                                  (1 + :HV-INTEREST-RATE),
                   LAST_UPDATE = CURRENT TIMESTAMP
               WHERE CURRENT OF UPD-CURSOR
           END-EXEC

           IF SQLCODE NOT = 0
               PERFORM 9100-SQL-ERROR
           END-IF
           .

22.9 Indicator Variables for NULL Handling

In DB2, a column can contain a NULL value, meaning "no data" or "unknown." COBOL has no concept of NULL; every variable always contains some value. Indicator variables bridge this gap by telling the COBOL program whether a host variable contains a real value or represents a NULL.

Declaring Indicator Variables

Indicator variables are declared as PIC S9(4) COMP:

       01  HV-CUST-ADDRESS    PIC X(100).
       01  IND-CUST-ADDRESS   PIC S9(4) COMP.

       01  HV-PHONE-NUMBER    PIC X(15).
       01  IND-PHONE-NUMBER   PIC S9(4) COMP.

Using Indicator Variables in SELECT

An indicator variable follows its host variable in the INTO clause, separated by no comma (some shops use a colon prefix on the indicator as well):

           EXEC SQL
               SELECT CUST_NAME,
                      CUST_ADDRESS,
                      PHONE_NUMBER
               INTO :HV-CUST-NAME,
                    :HV-CUST-ADDRESS :IND-CUST-ADDRESS,
                    :HV-PHONE-NUMBER :IND-PHONE-NUMBER
               FROM CUSTOMER
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC

           IF SQLCODE = 0
               IF IND-CUST-ADDRESS < 0
                   MOVE 'NO ADDRESS ON FILE'
                       TO WS-DISPLAY-ADDRESS
               ELSE
                   MOVE HV-CUST-ADDRESS
                       TO WS-DISPLAY-ADDRESS
               END-IF

               IF IND-PHONE-NUMBER < 0
                   MOVE 'NO PHONE ON FILE'
                       TO WS-DISPLAY-PHONE
               ELSE
                   MOVE HV-PHONE-NUMBER
                       TO WS-DISPLAY-PHONE
               END-IF
           END-IF

Indicator Variable Values

Using Indicator Variables in INSERT/UPDATE

To insert or update a NULL value, set the indicator variable to -1:

      * Insert with a NULL address
           MOVE -1 TO IND-CUST-ADDRESS

           EXEC SQL
               INSERT INTO CUSTOMER
                   (CUST_ID, CUST_NAME, CUST_ADDRESS)
               VALUES
                   (:HV-CUST-ID, :HV-CUST-NAME,
                    :HV-CUST-ADDRESS :IND-CUST-ADDRESS)
           END-EXEC

      * Update to set a column to NULL
           MOVE -1 TO IND-PHONE-NUMBER

           EXEC SQL
               UPDATE CUSTOMER
               SET PHONE_NUMBER =
                   :HV-PHONE-NUMBER :IND-PHONE-NUMBER
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC

SQLCODE -305: Missing Indicator Variable

If a SELECT returns a NULL value and you did not provide an indicator variable, DB2 returns SQLCODE -305. This is a common error:

      * This will fail with -305 if CUST_ADDRESS is NULL:
           EXEC SQL
               SELECT CUST_ADDRESS
               INTO :HV-CUST-ADDRESS
               FROM CUSTOMER
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC
      * SQLCODE = -305 if the value is NULL

      * FIX: Always use an indicator variable for nullable columns
           EXEC SQL
               SELECT CUST_ADDRESS
               INTO :HV-CUST-ADDRESS :IND-CUST-ADDRESS
               FROM CUSTOMER
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC

22.10 Indicator Variable Arrays and Group Host Variables

Indicator Variable Arrays

When working with multiple nullable columns, you can define an indicator variable array instead of individual indicator variables:

       01  HV-CUSTOMER-DATA.
           05  HV-CUST-ID        PIC S9(9) COMP.
           05  HV-CUST-NAME      PIC X(30).
           05  HV-CUST-ADDRESS   PIC X(100).
           05  HV-CUST-PHONE     PIC X(15).
           05  HV-CUST-EMAIL     PIC X(50).

       01  IND-CUSTOMER-DATA.
           05  IND-CUST-ID       PIC S9(4) COMP.
           05  IND-CUST-NAME     PIC S9(4) COMP.
           05  IND-CUST-ADDRESS  PIC S9(4) COMP.
           05  IND-CUST-PHONE    PIC S9(4) COMP.
           05  IND-CUST-EMAIL    PIC S9(4) COMP.

You can then use the group indicator variable in the INTO clause:

           EXEC SQL
               SELECT CUST_ID, CUST_NAME, CUST_ADDRESS,
                      CUST_PHONE, CUST_EMAIL
               INTO :HV-CUSTOMER-DATA :IND-CUSTOMER-DATA
               FROM CUSTOMER
               WHERE CUST_ID = :HV-SEARCH-ID
           END-EXEC

When you specify a group indicator variable, DB2 maps the individual indicator fields to the corresponding host variable fields in order. The first indicator maps to the first host variable, the second to the second, and so on.

Working with Indicator Variables in Practice

A common pattern is to write a utility paragraph that checks all indicator variables and sets display values:

       2250-FORMAT-CUSTOMER-DISPLAY.
           IF IND-CUST-ADDRESS < 0
               MOVE '(NO ADDRESS ON FILE)'
                   TO WS-DISPLAY-ADDRESS
           ELSE
               MOVE HV-CUST-ADDRESS TO WS-DISPLAY-ADDRESS
           END-IF

           IF IND-CUST-PHONE < 0
               MOVE '(NO PHONE)'
                   TO WS-DISPLAY-PHONE
           ELSE
               MOVE HV-CUST-PHONE TO WS-DISPLAY-PHONE
           END-IF

           IF IND-CUST-EMAIL < 0
               MOVE '(NO EMAIL)'
                   TO WS-DISPLAY-EMAIL
           ELSE
               MOVE HV-CUST-EMAIL TO WS-DISPLAY-EMAIL
           END-IF
           .

Truncation Detection with Indicator Variables

When a host variable is too small to hold the retrieved column value, DB2 truncates the data and sets the indicator variable to the original length of the data before truncation. This allows you to detect truncation:

       01  HV-SHORT-NAME      PIC X(10).
       01  IND-SHORT-NAME     PIC S9(4) COMP.

           EXEC SQL
               SELECT CUST_NAME
               INTO :HV-SHORT-NAME :IND-SHORT-NAME
               FROM CUSTOMER
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC

           IF SQLCODE = 0
               IF IND-SHORT-NAME > 10
                   DISPLAY 'NAME WAS TRUNCATED'
                   DISPLAY 'ORIGINAL LENGTH: '
                       IND-SHORT-NAME
                   DISPLAY 'TRUNCATED VALUE: '
                       HV-SHORT-NAME
               ELSE
                   DISPLAY 'NAME: ' HV-SHORT-NAME
               END-IF
           END-IF

22.11 Transaction Control: COMMIT and ROLLBACK

DB2 operations are grouped into transactions (units of work). A transaction begins implicitly with the first SQL statement and ends with either a COMMIT (making all changes permanent) or a ROLLBACK (undoing all changes since the last commit).

COMMIT

           EXEC SQL COMMIT END-EXEC

           IF SQLCODE NOT = 0
               DISPLAY 'COMMIT FAILED: ' SQLCODE
               PERFORM 9100-SQL-ERROR
           END-IF

ROLLBACK

           EXEC SQL ROLLBACK END-EXEC

           IF SQLCODE NOT = 0
               DISPLAY 'ROLLBACK FAILED: ' SQLCODE
      *        A failed ROLLBACK is a serious problem
               DISPLAY 'CONTACT DATABASE ADMINISTRATOR'
               MOVE 16 TO RETURN-CODE
               STOP RUN
           END-IF

When to COMMIT in Batch Programs

Batch programs that process large volumes of data must commit periodically. Without periodic commits, the entire run is a single transaction, which means that a failure near the end would roll back all the work done at the beginning. Additionally, DB2's log would fill up, and locks would accumulate, blocking other programs.

A common pattern commits every N rows:

       01  WS-COMMIT-INTERVAL PIC S9(5) COMP VALUE 500.
       01  WS-UNCOMMITTED     PIC S9(5) COMP VALUE 0.

       2000-PROCESS-TRANSACTION.
      *    ... perform business logic and SQL updates ...

           ADD 1 TO WS-UNCOMMITTED

           IF WS-UNCOMMITTED >= WS-COMMIT-INTERVAL
               EXEC SQL COMMIT END-EXEC
               IF SQLCODE = 0
                   MOVE 0 TO WS-UNCOMMITTED
                   ADD 1 TO CT-COMMITS
               ELSE
                   PERFORM 9100-SQL-ERROR
               END-IF
           END-IF
           .

       3000-TERMINATE.
      *    Final commit for remaining uncommitted work
           IF WS-UNCOMMITTED > 0
               EXEC SQL COMMIT END-EXEC
               ADD 1 TO CT-COMMITS
           END-IF
           .

SAVEPOINT

DB2 supports savepoints, which allow partial rollback within a transaction. This is useful when a batch of operations contains some that can fail without requiring the entire unit of work to be rolled back:

           EXEC SQL SAVEPOINT SP1 ON ROLLBACK
               RETAIN CURSORS
           END-EXEC

      *    Try an operation that might fail
           EXEC SQL
               INSERT INTO AUDIT_LOG ...
           END-EXEC

           IF SQLCODE < 0
      *        Roll back to the savepoint, not the entire
      *        transaction
               EXEC SQL ROLLBACK TO SAVEPOINT SP1
               END-EXEC
               DISPLAY 'AUDIT INSERT ROLLED BACK'
           END-IF

      *    Continue with the rest of the transaction
           EXEC SQL COMMIT END-EXEC

22.12 The WHENEVER Statement

The WHENEVER statement provides a declarative way to handle SQL errors without explicit SQLCODE checking after every statement. It directs the precompiler to generate automatic error-checking code:

      * On any SQL error, go to the error handler
           EXEC SQL
               WHENEVER SQLERROR
               GO TO 9100-SQL-ERROR
           END-EXEC

      * When no rows found, continue (do not jump)
           EXEC SQL
               WHENEVER NOT FOUND
               CONTINUE
           END-EXEC

      * On any SQL warning, continue
           EXEC SQL
               WHENEVER SQLWARNING
               CONTINUE
           END-EXEC

WHENEVER Options

Caution with WHENEVER

WHENEVER is a precompiler directive, not a runtime statement. It applies to all subsequent SQL statements in the source code until another WHENEVER for the same condition overrides it. This can cause unexpected behavior:

      * Set up error handling
           EXEC SQL
               WHENEVER SQLERROR GO TO 9100-SQL-ERROR
           END-EXEC

      * All SQL statements below here will GO TO 9100-SQL-ERROR
      * on any SQL error -- even if you intend to handle specific
      * errors differently

      * To handle errors inline for a specific statement,
      * temporarily disable WHENEVER:
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC

           EXEC SQL
               INSERT INTO CUSTOMER ...
           END-EXEC

      * Handle -803 (duplicate) specifically
           IF SQLCODE = -803
               PERFORM 2310-HANDLE-DUPLICATE
           ELSE IF SQLCODE < 0
               PERFORM 9100-SQL-ERROR
           END-IF

      * Re-enable WHENEVER
           EXEC SQL
               WHENEVER SQLERROR GO TO 9100-SQL-ERROR
           END-EXEC

Most experienced COBOL-DB2 programmers prefer explicit SQLCODE checking after every statement rather than relying on WHENEVER, because explicit checking gives more control and is easier to understand.

22.11 The DB2 Precompile and Bind Process

Unlike regular COBOL programs that go through compile and link-edit only, COBOL-DB2 programs require additional steps: precompile and bind.

The Process Flow

COBOL Source with SQL
        |
        v
  DB2 PRECOMPILE (DSNHPC)
  - Extracts SQL statements
  - Replaces them with CALL statements
  - Produces modified COBOL source
  - Produces a DBRM (Database Request Module)
        |
        +-----> Modified COBOL source
        |           |
        |           v
        |       COBOL COMPILE (IGYCRCTL)
        |           |
        |           v
        |       LINK-EDIT (IEWL)
        |           |
        |           v
        |       Load Module
        |
        +-----> DBRM
                    |
                    v
                DB2 BIND (DSNTIAD or IKJEFT01)
                    |
                    v
                Package or Plan
                    (contains access paths)

JCL for DB2 Precompile, Compile, Link, and Bind

//*------------------------------------------------------------
//*  STEP 1: DB2 PRECOMPILE
//*------------------------------------------------------------
//PC      EXEC PGM=DSNHPC,
//        PARM='HOST(COB2),APOST,APOSTSQL'
//STEPLIB  DD DSN=DSN.V13R1.SDSNLOAD,DISP=SHR
//DBRMLIB  DD DSN=USER.DBRMLIB(CUSTUPDT),DISP=SHR
//SYSCIN   DD DSN=&&DSNHOUT,DISP=(NEW,PASS),
//            UNIT=SYSDA,SPACE=(800,(500,500))
//SYSIN    DD DSN=USER.SRCLIB(CUSTUPDT),DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSUT1   DD UNIT=SYSDA,SPACE=(800,(500,500))
//SYSUT2   DD UNIT=SYSDA,SPACE=(800,(500,500))
//*
//*------------------------------------------------------------
//*  STEP 2: COBOL COMPILE
//*------------------------------------------------------------
//COB     EXEC PGM=IGYCRCTL,
//        PARM='LIB,RENT,LIST,MAP,XREF'
//STEPLIB  DD DSN=IGY.V6R4M0.SIGYCOMP,DISP=SHR
//SYSIN    DD DSN=&&DSNHOUT,DISP=(OLD,DELETE)
//SYSLIB   DD DSN=USER.COPYLIB,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSLIN   DD DSN=&&LOADSET,DISP=(NEW,PASS),
//            UNIT=SYSDA,SPACE=(800,(500,500))
//SYSUT1   DD UNIT=SYSDA,SPACE=(800,(500,500))
//SYSUT2   DD UNIT=SYSDA,SPACE=(800,(500,500))
//SYSUT3   DD UNIT=SYSDA,SPACE=(800,(500,500))
//SYSUT4   DD UNIT=SYSDA,SPACE=(800,(500,500))
//*
//*------------------------------------------------------------
//*  STEP 3: LINK-EDIT
//*------------------------------------------------------------
//LKED    EXEC PGM=IEWL,
//        PARM='LIST,MAP,RENT,AMODE=31,RMODE=ANY'
//SYSLIB   DD DSN=CEE.SCEELKED,DISP=SHR
//         DD DSN=DSN.V13R1.SDSNLOAD,DISP=SHR
//SYSLMOD  DD DSN=USER.LOADLIB(CUSTUPDT),DISP=SHR
//SYSLIN   DD DSN=&&LOADSET,DISP=(OLD,DELETE)
//         DD *
  INCLUDE SYSLIB(DSNELI)
  NAME CUSTUPDT(R)
/*
//SYSPRINT DD SYSOUT=*
//*
//*------------------------------------------------------------
//*  STEP 4: DB2 BIND
//*------------------------------------------------------------
//BIND    EXEC PGM=IKJEFT01
//STEPLIB  DD DSN=DSN.V13R1.SDSNLOAD,DISP=SHR
//DBRMLIB  DD DSN=USER.DBRMLIB,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(DB2P)
  BIND PACKAGE(CUSTCOLL) -
       MEMBER(CUSTUPDT) -
       ACT(REP) -
       ISO(CS) -
       VALIDATE(BIND) -
       RELEASE(COMMIT)
  END
/*

22.12 Common DB2 Programming Errors and Solutions

This section documents the most frequently encountered DB2 programming errors in COBOL and how to prevent or resolve them.

SQLCODE -818: Timestamp Mismatch

This is probably the most common DB2 error encountered by COBOL-DB2 developers. It means the load module was compiled with a DBRM that has a different timestamp than the one that was bound into the package.

Cause: The program was recompiled (which creates a new DBRM with a new timestamp) but the package was not rebound, or vice versa. This can happen when a developer recompiles but forgets to rebind, or when the bind job runs before the compile job completes.

Fix: Rebind the package using the DBRM from the most recent precompile, then ensure the load module from that same compile is deployed:

DSN SYSTEM(DB2P)
  BIND PACKAGE(CUSTCOLL) MEMBER(CUSTUPDT) ACT(REP)
END

Prevention: Always compile and bind together in the same JCL job stream, with the compile step preceding the bind step. Never compile without binding, and never bind without compiling.

SQLCODE -811: SELECT INTO Returns Multiple Rows

This error occurs when a SELECT INTO query returns more than one row. SELECT INTO expects exactly zero or one row.

      * This will produce -811 if multiple customers have
      * the same last name:
           EXEC SQL
               SELECT CUST_ID, CUST_NAME
               INTO :HV-CUST-ID, :HV-CUST-NAME
               FROM CUSTOMER
               WHERE CUST_LAST_NAME = :HV-SEARCH-NAME
           END-EXEC

      * FIX: Use a cursor when multiple rows are possible
      * Or add FETCH FIRST 1 ROW ONLY if you only need one:
           EXEC SQL
               SELECT CUST_ID, CUST_NAME
               INTO :HV-CUST-ID, :HV-CUST-NAME
               FROM CUSTOMER
               WHERE CUST_LAST_NAME = :HV-SEARCH-NAME
               FETCH FIRST 1 ROW ONLY
           END-EXEC

SQLCODE -803: Duplicate Key on INSERT

This error occurs when an INSERT statement tries to create a row with a primary key (or unique index key) that already exists.

      * Handle duplicate key gracefully
           EXEC SQL
               INSERT INTO CUSTOMER
                   (CUST_ID, CUST_NAME, CUST_BALANCE)
               VALUES
                   (:HV-CUST-ID, :HV-CUST-NAME, 0)
           END-EXEC

           EVALUATE SQLCODE
               WHEN 0
                   ADD 1 TO CT-INSERTED
               WHEN -803
                   DISPLAY 'CUSTOMER ALREADY EXISTS: '
                       HV-CUST-ID
                   ADD 1 TO CT-DUPLICATES
               WHEN OTHER
                   PERFORM 9100-SQL-ERROR
           END-EVALUATE

SQLCODE -305: NULL Value Without Indicator Variable

As discussed in Section 22.9, this error occurs when DB2 attempts to place a NULL value into a host variable that has no indicator variable. It is one of the most common "new programmer" errors.

Prevention: Always use indicator variables for columns defined as nullable in DB2. When in doubt, use an indicator variable -- having an unnecessary indicator variable causes no harm, but missing one causes a runtime error.

SQLCODE -501: Cursor Not Open

This error occurs when a FETCH is attempted on a cursor that is not open, or that has been closed:

      * Common mistake: Forgetting to OPEN the cursor
           EXEC SQL
               FETCH CUST-CURSOR
               INTO :HV-CUST-ID, :HV-CUST-NAME
           END-EXEC
      * SQLCODE = -501 because OPEN was never issued

      * Another common mistake: FETCH after cursor was closed
      * by a COMMIT (cursor was not declared WITH HOLD)

Prevention: Always pair DECLARE/OPEN/FETCH/CLOSE. Use WITH HOLD if the cursor must survive a COMMIT.

SQLCODE -530 and -532: Referential Integrity Violations

These errors occur when an INSERT or UPDATE would violate a foreign key constraint (-530) or when a DELETE would leave orphan rows in a child table (-532):

           EVALUATE SQLCODE
               WHEN -530
                   DISPLAY 'PARENT ROW DOES NOT EXIST'
                   DISPLAY 'CHECK FOREIGN KEY VALUE: '
                       HV-PARENT-KEY
               WHEN -532
                   DISPLAY 'CHILD ROWS EXIST'
                   DISPLAY 'DELETE CHILD ROWS FIRST'
           END-EVALUATE

Performance-Related SQLCODEs

22.13 Best Practices for COBOL-DB2 Programs

Program Structure for DB2 Batch

A well-structured DB2 batch program follows this pattern:

       0000-MAIN.
           PERFORM 1000-INITIALIZE
           PERFORM 2000-PROCESS UNTIL FL-EOF
           PERFORM 3000-TERMINATE
           STOP RUN
           .

       1000-INITIALIZE.
      *    Open files
      *    Initialize counters
      *    Read first input record (if file-driven)
      *    Check for restart from checkpoint table
           .

       2000-PROCESS.
      *    Read/validate input
      *    Perform DB2 operations
      *    Check commit threshold
      *    Read next input record
           .

       3000-TERMINATE.
      *    Final COMMIT
      *    Close files
      *    Display summary statistics
      *    Set RETURN-CODE
           .

Error Recovery Strategy

Every DB2 batch program should have a clear error recovery strategy documented in the program header:

      *========================================================*
      * ERROR RECOVERY STRATEGY:                                *
      *   - SQL errors: Log error, skip record, continue        *
      *   - Deadlocks: Retry 3 times with 2-second delay        *
      *   - Timeouts: Same as deadlock                          *
      *   - Max errors: 1000 (configurable)                     *
      *   - Restart: Via CHECKPOINT_TABLE, key-based restart     *
      *   - File errors: Abort with RC=16                       *
      *========================================================*

Separation of SQL and Business Logic

Keep SQL operations in dedicated paragraphs, separate from business logic. This makes the program easier to test, debug, and modify:

      * GOOD: SQL isolated in dedicated paragraphs
       2200-PROCESS-TRANSACTION.
           PERFORM 2210-READ-CUSTOMER
           IF WS-CUSTOMER-FOUND
               PERFORM 2220-CALCULATE-INTEREST
               PERFORM 2230-UPDATE-CUSTOMER
           END-IF
           .

       2210-READ-CUSTOMER.
           EXEC SQL
               SELECT CUST_NAME, CUST_BALANCE
               INTO :HV-CUST-NAME, :HV-CUST-BALANCE
               FROM CUSTOMER
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC
           EVALUATE SQLCODE
               WHEN 0     SET WS-CUSTOMER-FOUND TO TRUE
               WHEN +100  SET WS-CUSTOMER-NOT-FOUND TO TRUE
               WHEN OTHER PERFORM 9100-SQL-ERROR
           END-EVALUATE
           .

       2220-CALCULATE-INTEREST.
      *    Pure COBOL business logic -- no SQL here
           COMPUTE WS-INTEREST =
               HV-CUST-BALANCE * WS-ANNUAL-RATE / 12
               ON SIZE ERROR
                   DISPLAY 'INTEREST CALC OVERFLOW'
                   MOVE 0 TO WS-INTEREST
           END-COMPUTE
           .

       2230-UPDATE-CUSTOMER.
           COMPUTE HV-NEW-BALANCE =
               HV-CUST-BALANCE + WS-INTEREST
           EXEC SQL
               UPDATE CUSTOMER
               SET CUST_BALANCE = :HV-NEW-BALANCE,
                   LAST_UPDATE = CURRENT TIMESTAMP
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC
           EVALUATE SQLCODE
               WHEN 0     ADD 1 TO CT-UPDATED
               WHEN OTHER PERFORM 9100-SQL-ERROR
           END-EVALUATE
           .

22.14 GnuCOBOL ODBC Alternative

GnuCOBOL does not support embedded SQL with DB2 directly, but it can access relational databases through ODBC (Open Database Connectivity) using the CALL interface or through the ESQL preprocessor (ocesql) that supports a subset of embedded SQL syntax.

Using ocesql with GnuCOBOL

The ocesql preprocessor provides embedded SQL support for GnuCOBOL with ODBC-compliant databases (PostgreSQL, MySQL, SQLite, etc.):

       IDENTIFICATION DIVISION.
       PROGRAM-ID. GNUDB.

       DATA DIVISION.
       WORKING-STORAGE SECTION.

           EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01  HV-CUST-ID        PIC 9(9).
       01  HV-CUST-NAME      PIC X(30).
       01  HV-CUST-BALANCE   PIC S9(7)V99.
       01  DBNAME             PIC X(30)
                              VALUE 'mydb'.
       01  DBUSER             PIC X(30)
                              VALUE 'appuser'.
       01  DBPASSWD           PIC X(30)
                              VALUE 'password'.
           EXEC SQL END DECLARE SECTION END-EXEC.

           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       0000-MAIN.
           EXEC SQL
               CONNECT :DBUSER
               IDENTIFIED BY :DBPASSWD
               USING :DBNAME
           END-EXEC

           IF SQLCODE NOT = 0
               DISPLAY 'CONNECTION FAILED: ' SQLCODE
               STOP RUN
           END-IF

           MOVE 1001 TO HV-CUST-ID

           EXEC SQL
               SELECT CUST_NAME, CUST_BALANCE
               INTO :HV-CUST-NAME, :HV-CUST-BALANCE
               FROM CUSTOMER
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC

           IF SQLCODE = 0
               DISPLAY 'NAME:    ' HV-CUST-NAME
               DISPLAY 'BALANCE: ' HV-CUST-BALANCE
           END-IF

           EXEC SQL COMMIT WORK END-EXEC

           EXEC SQL DISCONNECT ALL END-EXEC

           STOP RUN
           .

Compiling with ocesql:

# Preprocess the COBOL source
ocesql program.cob program.cbl

# Compile the preprocessed source
cobc -x program.cbl -lodbc

22.13 Complete COBOL-DB2 Program Example

The following program demonstrates a complete COBOL-DB2 batch program that reads a transaction file and updates customer balances in a DB2 table:

       IDENTIFICATION DIVISION.
       PROGRAM-ID. CUSTUPDT.
      *========================================================*
      * PROGRAM:  CUSTUPDT                                      *
      * PURPOSE:  Update customer balances from transaction file *
      * INPUT:    TRANFILE - Transaction records (sequential)    *
      * DB2:      CUSTOMER table (read/update)                  *
      * OUTPUT:   ERRFILE  - Rejected transactions              *
      *========================================================*

       ENVIRONMENT DIVISION.
       INPUT-OUTPUT SECTION.
       FILE-CONTROL.
           SELECT TRANSACTION-FILE
               ASSIGN TO TRANFILE
               ORGANIZATION IS SEQUENTIAL
               FILE STATUS IS WS-TRAN-STATUS.
           SELECT ERROR-FILE
               ASSIGN TO ERRFILE
               ORGANIZATION IS SEQUENTIAL
               FILE STATUS IS WS-ERR-STATUS.

       DATA DIVISION.
       FILE SECTION.
       FD  TRANSACTION-FILE
           RECORDING MODE IS F
           RECORD CONTAINS 80 CHARACTERS.
       01  TRAN-RECORD.
           05  TRAN-CUST-ID       PIC 9(9).
           05  TRAN-AMOUNT        PIC S9(7)V99.
           05  TRAN-TYPE          PIC X.
           05  TRAN-DATE          PIC X(10).
           05  FILLER             PIC X(51).

       FD  ERROR-FILE
           RECORDING MODE IS F
           RECORD CONTAINS 130 CHARACTERS.
       01  ERROR-RECORD           PIC X(130).

       WORKING-STORAGE SECTION.

       01  WS-FILE-STATUSES.
           05  WS-TRAN-STATUS     PIC XX.
           05  WS-ERR-STATUS      PIC XX.

       01  FL-FLAGS.
           05  FL-EOF-FLAG        PIC 9 VALUE 0.
               88  FL-NOT-EOF     VALUE 0.
               88  FL-EOF         VALUE 1.

       01  CT-COUNTERS.
           05  CT-READ            PIC 9(7) COMP VALUE 0.
           05  CT-UPDATED         PIC 9(7) COMP VALUE 0.
           05  CT-NOT-FOUND       PIC 9(7) COMP VALUE 0.
           05  CT-ERRORS          PIC 9(7) COMP VALUE 0.
           05  CT-COMMITS         PIC 9(7) COMP VALUE 0.

       01  WS-COMMIT-FREQ        PIC 9(5) COMP VALUE 500.
       01  WS-SINCE-COMMIT       PIC 9(5) COMP VALUE 0.
       01  WS-CURRENT-PARAGRAPH  PIC X(30).

       01  WS-ERROR-RECORD.
           05  WER-CUST-ID       PIC 9(9).
           05  FILLER            PIC X VALUE ' '.
           05  WER-AMOUNT        PIC S9(7)V99.
           05  FILLER            PIC X VALUE ' '.
           05  WER-ERROR-MSG     PIC X(50).
           05  FILLER            PIC X(60).

           EXEC SQL INCLUDE SQLCA END-EXEC.

           EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01  HV-CUST-ID            PIC S9(9) COMP.
       01  HV-CUST-NAME          PIC X(30).
       01  HV-CUST-BALANCE       PIC S9(7)V99 COMP-3.
       01  HV-TRANS-AMOUNT       PIC S9(7)V99 COMP-3.
           EXEC SQL END DECLARE SECTION END-EXEC.

       PROCEDURE DIVISION.
       0000-MAIN.
           PERFORM 1000-INITIALIZE
           PERFORM 2000-PROCESS
               UNTIL FL-EOF
           PERFORM 3000-TERMINATE
           STOP RUN
           .

       1000-INITIALIZE.
           MOVE '1000-INITIALIZE' TO WS-CURRENT-PARAGRAPH

           OPEN INPUT  TRANSACTION-FILE
           IF WS-TRAN-STATUS NOT = '00'
               DISPLAY 'CANNOT OPEN TRAN FILE: '
                   WS-TRAN-STATUS
               MOVE 16 TO RETURN-CODE
               STOP RUN
           END-IF

           OPEN OUTPUT ERROR-FILE
           IF WS-ERR-STATUS NOT = '00'
               DISPLAY 'CANNOT OPEN ERROR FILE: '
                   WS-ERR-STATUS
               MOVE 16 TO RETURN-CODE
               STOP RUN
           END-IF

           PERFORM 2100-READ-TRANSACTION
           .

       2000-PROCESS.
           MOVE '2000-PROCESS' TO WS-CURRENT-PARAGRAPH

           MOVE TRAN-CUST-ID TO HV-CUST-ID
           MOVE TRAN-AMOUNT  TO HV-TRANS-AMOUNT

           EXEC SQL
               UPDATE CUSTOMER
               SET CUST_BALANCE = CUST_BALANCE
                                  + :HV-TRANS-AMOUNT,
                   LAST_UPDATE = CURRENT TIMESTAMP
               WHERE CUST_ID = :HV-CUST-ID
           END-EXEC

           EVALUATE SQLCODE
               WHEN 0
                   IF SQLERRD(3) > 0
                       ADD 1 TO CT-UPDATED
                       ADD 1 TO WS-SINCE-COMMIT
                       IF WS-SINCE-COMMIT >= WS-COMMIT-FREQ
                           PERFORM 2500-COMMIT
                       END-IF
                   ELSE
                       MOVE TRAN-CUST-ID TO WER-CUST-ID
                       MOVE TRAN-AMOUNT  TO WER-AMOUNT
                       MOVE 'CUSTOMER NOT FOUND (NO ROWS)'
                           TO WER-ERROR-MSG
                       PERFORM 2900-WRITE-ERROR
                       ADD 1 TO CT-NOT-FOUND
                   END-IF
               WHEN OTHER
                   MOVE TRAN-CUST-ID TO WER-CUST-ID
                   MOVE TRAN-AMOUNT  TO WER-AMOUNT
                   STRING 'SQL ERROR: ' SQLCODE
                       DELIMITED BY SIZE
                       INTO WER-ERROR-MSG
                   END-STRING
                   PERFORM 2900-WRITE-ERROR
                   ADD 1 TO CT-ERRORS
           END-EVALUATE

           PERFORM 2100-READ-TRANSACTION
           .

       2100-READ-TRANSACTION.
           READ TRANSACTION-FILE
               AT END
                   SET FL-EOF TO TRUE
               NOT AT END
                   ADD 1 TO CT-READ
           END-READ
           .

       2500-COMMIT.
           EXEC SQL COMMIT END-EXEC
           IF SQLCODE NOT = 0
               DISPLAY 'COMMIT ERROR: ' SQLCODE
           ELSE
               ADD 1 TO CT-COMMITS
               MOVE 0 TO WS-SINCE-COMMIT
           END-IF
           .

       2900-WRITE-ERROR.
           WRITE ERROR-RECORD FROM WS-ERROR-RECORD
           INITIALIZE WS-ERROR-RECORD
           .

       3000-TERMINATE.
           PERFORM 2500-COMMIT

           CLOSE TRANSACTION-FILE
           CLOSE ERROR-FILE

           DISPLAY '=============================='
           DISPLAY 'CUSTUPDT PROCESSING SUMMARY'
           DISPLAY '=============================='
           DISPLAY 'TRANSACTIONS READ:  ' CT-READ
           DISPLAY 'CUSTOMERS UPDATED:  ' CT-UPDATED
           DISPLAY 'NOT FOUND:          ' CT-NOT-FOUND
           DISPLAY 'SQL ERRORS:         ' CT-ERRORS
           DISPLAY 'COMMITS:            ' CT-COMMITS
           DISPLAY '=============================='

           EVALUATE TRUE
               WHEN CT-ERRORS > 0
                   MOVE 8 TO RETURN-CODE
               WHEN CT-NOT-FOUND > 0
                   MOVE 4 TO RETURN-CODE
               WHEN OTHER
                   MOVE 0 TO RETURN-CODE
           END-EVALUATE
           .

       9100-SQL-ERROR.
           DISPLAY '*** SQL ERROR ***'
           DISPLAY 'SQLCODE:   ' SQLCODE
           DISPLAY 'SQLSTATE:  ' SQLSTATE
           DISPLAY 'PARAGRAPH: ' WS-CURRENT-PARAGRAPH
           MOVE 16 TO RETURN-CODE
           EXEC SQL ROLLBACK END-EXEC
           CLOSE TRANSACTION-FILE
           CLOSE ERROR-FILE
           STOP RUN
           .

Summary

This chapter covered the fundamentals of COBOL-DB2 programming:

• DB2 is IBM's relational database for z/OS, providing centralized data management with SQL as the data access language.
• EXEC SQL...END-EXEC delimiters wrap all SQL statements embedded in COBOL source code.
• Host variables are COBOL data items that exchange data with DB2, prefixed with a colon (:) inside SQL statements and declared with compatible COBOL data types. DCLGEN automates host variable generation.
• The SQLCA (SQL Communication Area) provides the SQLCODE return code after every SQL operation: 0 for success, +100 for not found, and negative values for errors.
• SELECT INTO retrieves a single row into host variables and should always be followed by SQLCODE checking.
• INSERT, UPDATE, and DELETE modify DB2 data. Check SQLERRD(3) to determine how many rows were affected.
• Cursors (DECLARE, OPEN, FETCH, CLOSE) provide sequential access to multi-row result sets. WITH HOLD keeps cursors open across commits. FOR UPDATE enables positioned updates and deletes.
• Indicator variables (PIC S9(4) COMP) handle NULL values. A value of -1 indicates NULL; 0 indicates a valid value. Always use indicator variables for nullable columns.
• VARCHAR host variables use a special 49-level structure with a length field and a text field.
• WHENEVER provides declarative error handling but is less flexible than explicit SQLCODE checking.
• The precompile/bind process extracts SQL from COBOL, produces a DBRM, and binds it into a package that DB2 uses to determine access paths.
• GnuCOBOL can access databases through ODBC using the ocesql preprocessor.

Embedded SQL transforms COBOL from a file-processing language into a database application language. The combination of COBOL's data handling strengths with DB2's relational capabilities forms the backbone of enterprise data processing on IBM mainframes.

Advanced Topic: Multi-Table Queries

While the cursor examples in this chapter used single-table queries, production programs frequently join multiple tables. Here is how multi-table operations work with COBOL host variables:

Inner Join with Host Variables

           EXEC SQL
               DECLARE ORDER-CURSOR CURSOR FOR
               SELECT C.CUST_ID,
                      C.CUST_NAME,
                      O.ORDER_ID,
                      O.ORDER_DATE,
                      O.ORDER_AMOUNT
               FROM CUSTOMER C
                   INNER JOIN ORDERS O
                       ON C.CUST_ID = O.CUST_ID
               WHERE O.ORDER_DATE >= :HV-START-DATE
                 AND O.ORDER_DATE <= :HV-END-DATE
                 AND C.CUST_STATUS = 'A'
               ORDER BY C.CUST_NAME, O.ORDER_DATE
           END-EXEC

The cursor is fetched in exactly the same way as a single-table cursor. The host variables in the INTO clause correspond to the columns in the SELECT clause regardless of which table they come from:

       2100-FETCH-ORDER.
           EXEC SQL
               FETCH ORDER-CURSOR
               INTO :HV-CUST-ID,
                    :HV-CUST-NAME,
                    :HV-ORDER-ID,
                    :HV-ORDER-DATE,
                    :HV-ORDER-AMOUNT
           END-EXEC
           .

Subqueries

Subqueries in WHERE clauses work transparently with host variables:

      * Find customers whose balance exceeds the average
           EXEC SQL
               DECLARE HIGH-BAL-CURSOR CURSOR FOR
               SELECT CUST_ID, CUST_NAME, CUST_BALANCE
               FROM CUSTOMER
               WHERE CUST_BALANCE >
                   (SELECT AVG(CUST_BALANCE)
                    FROM CUSTOMER
                    WHERE CUST_STATUS = 'A')
                 AND CUST_STATUS = 'A'
               ORDER BY CUST_BALANCE DESC
           END-EXEC

Correlated Subqueries

Correlated subqueries reference the outer query and execute once for each row of the outer query:

      * Find customers with no orders in the last 30 days
           EXEC SQL
               DECLARE INACTIVE-CURSOR CURSOR FOR
               SELECT CUST_ID, CUST_NAME
               FROM CUSTOMER C
               WHERE NOT EXISTS
                   (SELECT 1
                    FROM ORDERS O
                    WHERE O.CUST_ID = C.CUST_ID
                      AND O.ORDER_DATE >=
                          CURRENT DATE - 30 DAYS)
                 AND C.CUST_STATUS = 'A'
               ORDER BY C.CUST_NAME
           END-EXEC

UNION Queries

When you need to combine results from multiple queries, UNION ALL combines them into a single result set:

           EXEC SQL
               DECLARE ACTIVITY-CURSOR CURSOR FOR
               SELECT CUST_ID,
                      'DEPOSIT' AS ACTIVITY_TYPE,
                      DEPOSIT_DATE AS ACTIVITY_DATE,
                      DEPOSIT_AMOUNT AS AMOUNT
               FROM DEPOSITS
               WHERE DEPOSIT_DATE >= :HV-START-DATE
               UNION ALL
               SELECT CUST_ID,
                      'WITHDRAWAL',
                      WITHDRAWAL_DATE,
                      WITHDRAWAL_AMOUNT
               FROM WITHDRAWALS
               WHERE WITHDRAWAL_DATE >= :HV-START-DATE
               ORDER BY CUST_ID, ACTIVITY_DATE
           END-EXEC

Exercises

1. Basic SELECT INTO: Write a COBOL-DB2 program that accepts a customer ID as input (from SYSIN or a parameter), retrieves the customer's name and balance from a CUSTOMER table, and displays the results. Handle SQLCODE 0, +100, and negative values.

2. Cursor Processing: Write a program that uses a cursor to read all customers with a balance greater than a specified threshold, displays each customer's information, and at the end displays the total number of customers and the sum of their balances.

3. INSERT with Duplicate Handling: Write a program that reads a sequential file of new customer records and inserts them into a CUSTOMER table. Handle SQLCODE -803 (duplicate key) by logging the duplicate and continuing with the next record.

4. NULL Handling: Write a program that reads customer records from DB2 where the PHONE_NUMBER column is nullable. Use indicator variables to detect NULLs and display "NO PHONE" for customers without phone numbers.

5. Cursor with UPDATE: Write a program that uses a FOR UPDATE cursor to read all customers with status 'I' (inactive) and updates their status to 'C' (closed) if their balance is zero. Commit every 100 updates using a WITH HOLD cursor.