Appendix C: DB2 SQL Reference for COBOL

This appendix covers what COBOL programmers need to know about DB2 SQL — the statements you embed in programs, the host variable declarations that bridge COBOL and DB2 data types, the SQLCA fields you check after every call, and the SQLCODEs you will encounter in production. It is not a general SQL tutorial; it assumes you understand relational concepts and focuses on the COBOL-specific mechanics.

All examples target DB2 for z/OS (V12/V13). Syntax differences for DB2 LUW (Linux/Unix/Windows) are noted where they affect COBOL programmers.

Host Variable Declarations

Host variables are COBOL data items used in SQL statements, prefixed with a colon (:) inside EXEC SQL blocks. They must be declared in the Working-Storage or Local-Storage Section, within an EXEC SQL BEGIN DECLARE SECTION / EXEC SQL END DECLARE SECTION block (recommended but not strictly required with the DB2 coprocessor).

       WORKING-STORAGE SECTION.

       EXEC SQL BEGIN DECLARE SECTION END-EXEC.

       01  HV-EMPLOYEE-ID         PIC S9(09) COMP.
       01  HV-FIRST-NAME          PIC X(30).
       01  HV-LAST-NAME           PIC X(30).
       01  HV-SALARY              PIC S9(07)V99 COMP-3.
       01  HV-SALARY-NULL         PIC S9(04) COMP.
       01  HV-HIRE-DATE           PIC X(10).
       01  HV-TIMESTAMP-VAL       PIC X(26).
       01  HV-DESCRIPTION.
           49  HV-DESCRIPTION-LEN PIC S9(04) COMP.
           49  HV-DESCRIPTION-TXT PIC X(1000).

       EXEC SQL END DECLARE SECTION END-EXEC.

       EXEC SQL INCLUDE SQLCA END-EXEC.

DB2 to COBOL Data Type Mapping

VARCHAR Host Variables

DB2 VARCHAR columns map to a two-field structure at level 49:

       01  HV-ADDRESS.
           49  HV-ADDRESS-LEN     PIC S9(04) COMP.
           49  HV-ADDRESS-TXT     PIC X(200).

DB2 recognizes the level-49 structure as a VARCHAR host variable. On SELECT, DB2 sets HV-ADDRESS-LEN to the actual length of the data. On INSERT/UPDATE, you must set HV-ADDRESS-LEN before executing the SQL statement.

Null Indicator Variables

Every nullable column needs an indicator variable — a PIC S9(04) COMP field specified after the host variable with a colon separator:

           EXEC SQL
               SELECT SALARY
                 INTO :HV-SALARY :HV-SALARY-NULL
                 FROM EMPLOYEE
                WHERE EMPLOYEE_ID = :HV-EMPLOYEE-ID
           END-EXEC

Always check the indicator before using the host variable. Using a host variable when its indicator is -1 is a logic error — the data is garbage.

           IF HV-SALARY-NULL < 0
               MOVE 0 TO WS-DISPLAY-SALARY
           ELSE
               MOVE HV-SALARY TO WS-DISPLAY-SALARY
           END-IF

To insert or update a null value:

           MOVE -1 TO HV-SALARY-NULL
           EXEC SQL
               UPDATE EMPLOYEE
                  SET SALARY = :HV-SALARY :HV-SALARY-NULL
                WHERE EMPLOYEE_ID = :HV-EMPLOYEE-ID
           END-EXEC

DML Statements

SELECT INTO (Single-Row Fetch)

           EXEC SQL
               SELECT FIRST_NAME, LAST_NAME, SALARY,
                      HIRE_DATE
                 INTO :HV-FIRST-NAME,
                      :HV-LAST-NAME,
                      :HV-SALARY :HV-SALARY-NULL,
                      :HV-HIRE-DATE
                 FROM EMPLOYEE
                WHERE EMPLOYEE_ID = :HV-EMPLOYEE-ID
           END-EXEC

           EVALUATE SQLCODE
               WHEN 0
                   CONTINUE
               WHEN +100
                   SET WS-NOT-FOUND TO TRUE
               WHEN OTHER
                   PERFORM 9500-DB2-ERROR
           END-EVALUATE

SELECT INTO must return exactly one row. If it returns zero rows, SQLCODE = +100. If it returns more than one row, SQLCODE = -811. Both are common production conditions that your code must handle.

INSERT

      * Single-row INSERT
           EXEC SQL
               INSERT INTO EMPLOYEE
                   (EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
                    SALARY, HIRE_DATE)
               VALUES
                   (:HV-EMPLOYEE-ID,
                    :HV-FIRST-NAME,
                    :HV-LAST-NAME,
                    :HV-SALARY :HV-SALARY-NULL,
                    CURRENT DATE)
           END-EXEC

      * INSERT from SELECT
           EXEC SQL
               INSERT INTO EMPLOYEE_ARCHIVE
                   (EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
                    ARCHIVE_DATE)
               SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
                      CURRENT DATE
                 FROM EMPLOYEE
                WHERE TERMINATION_DATE < :HV-CUTOFF-DATE
           END-EXEC

After INSERT from SELECT, SQLERRD(3) in the SQLCA contains the number of rows inserted.

UPDATE

      * Positioned UPDATE (with cursor)
           EXEC SQL
               UPDATE EMPLOYEE
                  SET SALARY = :HV-NEW-SALARY
                WHERE CURRENT OF CSR-EMPLOYEE
           END-EXEC

      * Searched UPDATE
           EXEC SQL
               UPDATE EMPLOYEE
                  SET SALARY = SALARY * 1.05
                WHERE DEPARTMENT_ID = :HV-DEPT-ID
                  AND SALARY < :HV-SALARY-CAP
           END-EXEC

SQLERRD(3) contains the number of rows updated. If it is 0 and SQLCODE is 0, the WHERE clause matched no rows — this is not an error, but it may be a logic issue your program should detect.

DELETE

      * Positioned DELETE
           EXEC SQL
               DELETE FROM EMPLOYEE
                WHERE CURRENT OF CSR-EMPLOYEE
           END-EXEC

      * Searched DELETE
           EXEC SQL
               DELETE FROM EMPLOYEE_ARCHIVE
                WHERE ARCHIVE_DATE < :HV-PURGE-DATE
           END-EXEC

SQLERRD(3) contains the number of rows deleted.

MERGE (Upsert)

           EXEC SQL
               MERGE INTO CUSTOMER_MASTER AS T
               USING (VALUES (:HV-CUST-ID,
                              :HV-CUST-NAME,
                              :HV-CUST-ADDR))
                   AS S(CUST_ID, CUST_NAME, CUST_ADDR)
               ON T.CUSTOMER_ID = S.CUST_ID
               WHEN MATCHED THEN
                   UPDATE SET CUSTOMER_NAME = S.CUST_NAME,
                              CUSTOMER_ADDR = S.CUST_ADDR,
                              LAST_UPDATE   = CURRENT TIMESTAMP
               WHEN NOT MATCHED THEN
                   INSERT (CUSTOMER_ID, CUSTOMER_NAME,
                           CUSTOMER_ADDR, CREATED_DATE)
                   VALUES (S.CUST_ID, S.CUST_NAME,
                           S.CUST_ADDR, CURRENT DATE)
           END-EXEC

MERGE replaces the common pattern of "SELECT to check existence, then INSERT or UPDATE." It is atomic and significantly more efficient, especially in batch programs processing large volumes.

Cursors

Declaring Cursors

      * Basic read-only cursor
           EXEC SQL
               DECLARE CSR-EMPLOYEE CURSOR FOR
               SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
                      SALARY
                 FROM EMPLOYEE
                WHERE DEPARTMENT_ID = :HV-DEPT-ID
                ORDER BY LAST_NAME, FIRST_NAME
           END-EXEC

      * Cursor for update
           EXEC SQL
               DECLARE CSR-EMP-UPDATE CURSOR FOR
               SELECT EMPLOYEE_ID, SALARY
                 FROM EMPLOYEE
                WHERE DEPARTMENT_ID = :HV-DEPT-ID
                  AND SALARY < :HV-SALARY-CAP
                FOR UPDATE OF SALARY
           END-EXEC

      * WITH HOLD cursor (survives COMMIT)
           EXEC SQL
               DECLARE CSR-LARGE-BATCH CURSOR WITH HOLD FOR
               SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
                 FROM EMPLOYEE
                WHERE STATUS = 'A'
                ORDER BY EMPLOYEE_ID
           END-EXEC

      * Scrollable cursor
           EXEC SQL
               DECLARE CSR-SCROLL SCROLL CURSOR FOR
               SELECT EMPLOYEE_ID, LAST_NAME
                 FROM EMPLOYEE
                WHERE DEPARTMENT_ID = :HV-DEPT-ID
                ORDER BY LAST_NAME
           END-EXEC

DECLARE is declarative, not executable. It does not generate any runtime code. The cursor is not "created" at the DECLARE — it is opened at the OPEN. DECLARE must appear before the first reference to the cursor in the source, but it does not need to be in the Procedure Division at the point of execution.

Opening and Fetching

           EXEC SQL OPEN CSR-EMPLOYEE END-EXEC
           IF SQLCODE NOT = 0
               PERFORM 9500-DB2-ERROR
           END-IF

           PERFORM UNTIL SQLCODE = +100
               EXEC SQL
                   FETCH CSR-EMPLOYEE
                    INTO :HV-EMPLOYEE-ID,
                         :HV-FIRST-NAME,
                         :HV-LAST-NAME,
                         :HV-SALARY :HV-SALARY-NULL
               END-EXEC
               EVALUATE SQLCODE
                   WHEN 0
                       PERFORM 2100-PROCESS-EMPLOYEE
                   WHEN +100
                       CONTINUE
                   WHEN OTHER
                       PERFORM 9500-DB2-ERROR
               END-EVALUATE
           END-PERFORM

           EXEC SQL CLOSE CSR-EMPLOYEE END-EXEC

Multi-Row FETCH

       WORKING-STORAGE SECTION.
       01  WS-FETCH-COUNT         PIC S9(09) COMP VALUE 100.
       01  WS-ROWS-FETCHED        PIC S9(09) COMP.
       01  WS-EMP-ARRAY.
           05  WS-EMP-ROW OCCURS 100 TIMES.
               10  WS-ARR-EMP-ID  PIC S9(09) COMP.
               10  WS-ARR-LNAME   PIC X(30).
               10  WS-ARR-SALARY  PIC S9(07)V99 COMP-3.

      * In Procedure Division:
           EXEC SQL
               FETCH CSR-EMPLOYEE
               FOR :WS-FETCH-COUNT ROWS
               INTO :WS-ARR-EMP-ID,
                    :WS-ARR-LNAME,
                    :WS-ARR-SALARY
           END-EXEC
           MOVE SQLERRD(3) TO WS-ROWS-FETCHED

Multi-row FETCH retrieves up to N rows in a single DB2 call. This dramatically reduces the overhead of row-at-a-time processing. In batch programs processing millions of rows, multi-row FETCH with an array size of 100-1000 can reduce elapsed time by 30-50%.

SQLERRD(3) tells you how many rows were actually fetched. When the result set is exhausted, SQLCODE = +100 and SQLERRD(3) contains the count of the final (partial) batch.

Scrollable Cursor FETCH

           EXEC SQL FETCH FIRST CSR-SCROLL
               INTO :HV-EMPLOYEE-ID, :HV-LAST-NAME END-EXEC

           EXEC SQL FETCH NEXT CSR-SCROLL
               INTO :HV-EMPLOYEE-ID, :HV-LAST-NAME END-EXEC

           EXEC SQL FETCH PRIOR CSR-SCROLL
               INTO :HV-EMPLOYEE-ID, :HV-LAST-NAME END-EXEC

           EXEC SQL FETCH LAST CSR-SCROLL
               INTO :HV-EMPLOYEE-ID, :HV-LAST-NAME END-EXEC

           EXEC SQL FETCH ABSOLUTE :HV-ROW-NUM CSR-SCROLL
               INTO :HV-EMPLOYEE-ID, :HV-LAST-NAME END-EXEC

           EXEC SQL FETCH RELATIVE -5 CSR-SCROLL
               INTO :HV-EMPLOYEE-ID, :HV-LAST-NAME END-EXEC

Scrollable cursors are useful for paging interfaces (CICS BMS or web services) but carry significant overhead. DB2 materializes the entire result set. Use them only when you truly need bidirectional navigation.

COMMIT and ROLLBACK

           EXEC SQL COMMIT END-EXEC

           EXEC SQL ROLLBACK END-EXEC

Always check SQLCODE after COMMIT. A COMMIT failure (rare but possible, e.g., SQLCODE -911 deadlock during commit) means your changes did not persist.

Commit Frequency in Batch

       01  WS-COMMIT-COUNTER      PIC S9(09) COMP VALUE 0.
       01  WS-COMMIT-INTERVAL     PIC S9(09) COMP VALUE 1000.

      * In processing loop:
           ADD 1 TO WS-COMMIT-COUNTER
           IF WS-COMMIT-COUNTER >= WS-COMMIT-INTERVAL
               EXEC SQL COMMIT END-EXEC
               IF SQLCODE NOT = 0
                   PERFORM 9500-DB2-ERROR
               END-IF
               MOVE 0 TO WS-COMMIT-COUNTER
           END-IF

Committing every 500-5000 rows is typical for batch programs. Too infrequent: long-running locks, massive log usage, and long recovery times if the program abends. Too frequent: commit overhead becomes significant. The right interval depends on your workload — 1000 is a sensible starting point.

If using WITH HOLD cursors, the cursor position is preserved across COMMIT. Without WITH HOLD, all cursors are closed at COMMIT, and you must reposition.

EXPLAIN

           EXEC SQL
               EXPLAIN PLAN SET QUERYNO = 100 FOR
               SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPT_NAME
                 FROM EMPLOYEE E
                 JOIN DEPARTMENT D
                   ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
                WHERE E.SALARY > :HV-SALARY-THRESHOLD
           END-EXEC

EXPLAIN populates the PLAN_TABLE with the access path DB2 chose. In practice, you more commonly run EXPLAIN through tools (DB2 EXPLAIN, Data Studio, or SPUFI) rather than embedding it in COBOL. But understanding what EXPLAIN tells you — table scans vs. index access, sort operations, join methods — is essential for performance tuning.

Key SQL Functions

Scalar Functions

CASE Expression

SELECT EMPLOYEE_ID,
       CASE
           WHEN SALARY > 100000 THEN 'HIGH'
           WHEN SALARY > 50000  THEN 'MEDIUM'
           ELSE 'LOW'
       END AS SALARY_BAND
  FROM EMPLOYEE

Aggregate Functions

SQLCA Fields

The SQLCA (SQL Communication Area) is your window into what DB2 did. Include it with EXEC SQL INCLUDE SQLCA END-EXEC.

Standard SQLCODE Check Pattern

       9500-DB2-ERROR.
           MOVE SQLCODE     TO WS-DISP-SQLCODE
           MOVE SQLERRMC    TO WS-DISP-SQLERRMC
           MOVE SQLERRD(3)  TO WS-DISP-ROWS

           DISPLAY 'DB2 ERROR - SQLCODE: ' WS-DISP-SQLCODE
           DISPLAY 'SQLERRMC: ' WS-DISP-SQLERRMC
           DISPLAY 'ROWS AFFECTED: ' WS-DISP-ROWS

           EXEC SQL ROLLBACK END-EXEC

           MOVE 16 TO RETURN-CODE
           STOP RUN.

In production, replace DISPLAY with proper error logging — write to an error file, update an error table, or call a standard error-handling subprogram.

Common SQLCODEs

These are the SQLCODEs you will encounter most frequently in production COBOL programs. Learn them by heart.

Positive (Warnings / Expected Conditions)

Negative (Errors)

Additional Important SQLCODEs

SQLCODE vs. SQLSTATE

SQLCODE is IBM-specific and DB2-specific. SQLSTATE is SQL standard (ISO/ANSI) and portable across database vendors. In practice, most COBOL shops use SQLCODE because the codes are more specific and are deeply embedded in existing documentation and procedures. If you need portability, check SQLSTATE instead.

Static vs. Dynamic SQL

Static SQL

All SQL in the examples above is static — the SQL text is known at compile time, precompiled into a DBRM, and bound into a package. Static SQL has predictable performance (the access path is determined at BIND time) and is the standard for production batch and CICS programs.

Dynamic SQL

       01  HV-SQL-STMT            PIC X(500).
       01  HV-EMP-ID              PIC S9(09) COMP.
       01  HV-LAST-NAME           PIC X(30).

      * EXECUTE IMMEDIATE (no result set)
           MOVE 'DELETE FROM TEMP_TABLE WHERE STATUS = ''X'''
               TO HV-SQL-STMT
           EXEC SQL
               EXECUTE IMMEDIATE :HV-SQL-STMT
           END-EXEC

      * PREPARE and EXECUTE (with parameters)
           MOVE 'SELECT LAST_NAME FROM EMPLOYEE WHERE
      -         'EMPLOYEE_ID = ?'
               TO HV-SQL-STMT
           EXEC SQL
               PREPARE STMT1 FROM :HV-SQL-STMT
           END-EXEC
           EXEC SQL
               EXECUTE STMT1 INTO :HV-LAST-NAME
               USING :HV-EMP-ID
           END-EXEC

      * PREPARE with cursor
           EXEC SQL
               PREPARE STMT2 FROM :HV-SQL-STMT
           END-EXEC
           EXEC SQL
               DECLARE CSR-DYNAMIC CURSOR FOR STMT2
           END-EXEC
           EXEC SQL OPEN CSR-DYNAMIC USING :HV-EMP-ID
           END-EXEC

Dynamic SQL is necessary when the SQL text varies at runtime (ad-hoc queries, variable predicates, utility operations). The access path is determined at PREPARE time (not BIND time), so performance is less predictable. Use parameter markers (?) instead of string concatenation to prevent SQL injection and allow access path caching.

DCLGEN (Declaration Generator)

DCLGEN generates COBOL host variable declarations from DB2 table definitions:

//DCLGEN   EXEC PGM=DSNTIAD,PARM='...'

Or in SPUFI / Data Studio:

DCLGEN TABLE(EMPLOYEE)
       LIBRARY(MY.DCLGEN.LIB(EMPLOYEE))
       ACTION(REPLACE)
       LANGUAGE(COBOL)
       STRUCTURE(DCL-EMPLOYEE)

This produces a copybook with the table's column declarations mapped to COBOL host variables. Always use DCLGEN rather than hand-coding host variables — it guarantees that your declarations match the actual table structure.

Include the generated copybook:

           EXEC SQL INCLUDE EMPLOYEE END-EXEC

Multi-Row INSERT

       01  WS-INS-COUNT           PIC S9(09) COMP VALUE 100.
       01  WS-INS-ARRAY.
           05  WS-INS-ROW OCCURS 100 TIMES.
               10  WS-INS-EMP-ID  PIC S9(09) COMP.
               10  WS-INS-LNAME   PIC X(30).
               10  WS-INS-SALARY  PIC S9(07)V99 COMP-3.

           EXEC SQL
               INSERT INTO EMPLOYEE_STAGING
                   (EMPLOYEE_ID, LAST_NAME, SALARY)
               VALUES (:WS-INS-EMP-ID,
                       :WS-INS-LNAME,
                       :WS-INS-SALARY)
               FOR :WS-INS-COUNT ROWS
               NOT ATOMIC CONTINUE ON SQLEXCEPTION
           END-EXEC

NOT ATOMIC CONTINUE ON SQLEXCEPTION means DB2 attempts to insert all rows and reports failures individually rather than rolling back the entire batch. Check SQLERRD(3) for the number of successful inserts.

Multi-row INSERT, like multi-row FETCH, dramatically reduces the overhead of row-at-a-time processing. Use both in batch programs.

Useful DB2 Commands for COBOL Developers

These are not SQL — they are DB2 subsystem commands issued from the operator console, SPUFI, or DSN session:

Running RUNSTATS after significant data changes is essential. Without current statistics, the DB2 optimizer makes poor access path decisions, and your program runs ten times slower than it should.

SQLCA Display Subroutine

Every COBOL/DB2 shop should have a standard error-reporting subroutine. Here is a production-quality template:

       9500-DB2-ERROR.
           MOVE SQLCODE TO WS-DISP-SQLCODE
           STRING 'SQLCODE=' DELIMITED BY SIZE
                  WS-DISP-SQLCODE DELIMITED BY SIZE
                  ' SQLSTATE=' DELIMITED BY SIZE
                  SQLSTATE DELIMITED BY SIZE
                  ' SQLERRMC=' DELIMITED BY SIZE
                  SQLERRMC DELIMITED BY SPACES
               INTO WS-ERROR-MSG
               WITH POINTER WS-MSG-PTR
           END-STRING
           DISPLAY WS-ERROR-MSG
           DISPLAY 'SQLERRD(3)=' SQLERRD(3)
                   ' SQLERRD(5)=' SQLERRD(5)

           IF SQLWARN0 = 'W'
               DISPLAY 'WARNINGS: '
                   SQLWARN1 SQLWARN2 SQLWARN3
                   SQLWARN4 SQLWARN5 SQLWARN6
                   SQLWARN7
           END-IF

           EXEC SQL ROLLBACK END-EXEC

           MOVE 16 TO RETURN-CODE
           STOP RUN.

SQLERRD(5) gives the offset within the SQL statement where the error was detected — invaluable for debugging syntax errors in dynamic SQL or long static SQL statements.