What is embedded SQL in COBOL?

Embedded SQL is SQL code written directly inside a COBOL program, delimited by EXEC SQL ... END-EXEC markers. When the program is compiled, a DB2 precompiler processes the EXEC SQL blocks first — it strips them out, replaces them with host-language calls, and generates a Database Request Module (DBRM). The DBRM is then bound to create a DB2 package or plan that DB2 uses at runtime to execute the SQL.

What is SQLCODE in DB2 COBOL programs?

SQLCODE is a numeric field in the SQLCA (SQL Communication Area) that DB2 sets after every SQL statement. SQLCODE = 0 means success; SQLCODE = +100 means no row found (end of cursor or singleton SELECT returned nothing); any negative SQLCODE is an error. COBOL programs must check SQLCODE after every EXEC SQL statement to handle errors correctly.

What is the difference between a singleton SELECT and a cursor in DB2 COBOL?

A singleton SELECT retrieves exactly one row directly into host variables: EXEC SQL SELECT SALARY INTO :WS-SALARY FROM EMPLOYEE WHERE EMPNO = :WS-EMPNO END-EXEC. If the query returns zero or more than one row, SQLCODE is set to +100 or -811 respectively. A cursor is used when a query can return multiple rows — you DECLARE the cursor, OPEN it, FETCH rows in a loop, then CLOSE it.

What is the BIND process in DB2 COBOL?

BIND is the process that converts a DBRM (Database Request Module — produced by the DB2 precompiler) into a DB2 package or plan. During BIND, DB2 validates the SQL, checks authorization, and produces an optimised access plan. The resulting package is stored in the DB2 catalog and executed at runtime when the COBOL program calls DB2. Packages must be rebound after RUNSTATS or schema changes to use the latest statistics.

DB2 COBOL Embedded SQL: Complete Guide

In the mainframe world, the dominant pattern for accessing DB2 is embedded SQL in COBOL — SQL statements written directly inside COBOL source code. This is how millions of lines of production banking, insurance, and government code interact with DB2 every day. Understanding how embedded SQL works, how to handle results and errors, and how the compile/precompile/bind workflow fits together is essential for any mainframe developer.

How Embedded SQL Works

Embedded SQL in COBOL follows a well-defined compilation pipeline:

text

COBOL Source (with EXEC SQL blocks)
        ↓
DB2 Precompiler (DSNHPC)
        ↓
Modified COBOL Source + DBRM (Database Request Module)
        ↓
COBOL Compiler (IGYCRCTL)          BIND (DSNHBND)
        ↓                                ↓
Object Module                      DB2 Package/Plan (in Catalog)
        ↓
Link-Edit (IEWL)
        ↓
Load Module
        ↓
Execute (program + DB2 plan cooperate at runtime)

At runtime, when the program reaches an EXEC SQL statement, control passes to DB2, which executes the precompiled access plan and returns results to the host variables.

The SQLCA — SQL Communication Area

Before writing any SQL, include the SQL Communication Area in your WORKING-STORAGE SECTION:

cobol

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

The SQLCA is a set of fields DB2 populates after every SQL statement. The most important fields:

Field	Type	Meaning
SQLCODE	PIC S9(9) COMP	0=success, +100=not found, negative=error
SQLSTATE	PIC X(5)	ANSI standard 5-char state code
SQLERRM	VARCHAR 70	Error message text
SQLERRD(3)	PIC S9(9) COMP	Row count affected by last DML

After every EXEC SQL block, test SQLCODE:

cobol

       EVALUATE SQLCODE
           WHEN 0
               PERFORM 1000-PROCESS-ROW
           WHEN +100
               MOVE 'Y' TO WS-EOF-FLAG
           WHEN OTHER
               PERFORM 9900-SQL-ERROR
       END-EVALUATE.

Host Variables

Host variables are COBOL data items used to pass values to and receive values from SQL statements. They are prefixed with a colon (:) inside EXEC SQL blocks:

cobol

       WORKING-STORAGE SECTION.
       01  WS-EMPNO          PIC X(6).
       01  WS-LASTNAME       PIC X(15).
       01  WS-SALARY         PIC S9(7)V99 COMP-3.
       01  WS-WORKDEPT       PIC X(3).

       PROCEDURE DIVISION.
       1000-FETCH-EMPLOYEE.
           MOVE '000010' TO WS-EMPNO

           EXEC SQL
               SELECT LASTNAME, SALARY, WORKDEPT
               INTO  :WS-LASTNAME, :WS-SALARY, :WS-WORKDEPT
               FROM   EMPLOYEE
               WHERE  EMPNO = :WS-EMPNO
           END-EXEC

           EVALUATE SQLCODE
               WHEN 0
                   DISPLAY 'Found: ' WS-LASTNAME
               WHEN +100
                   DISPLAY 'Employee not found'
               WHEN OTHER
                   PERFORM 9900-SQL-ERROR
           END-EVALUATE.

NULL Indicator Variables

When a DB2 column can contain NULL, you must provide a null indicator variable alongside the host variable:

cobol

       01  WS-BONUS          PIC S9(7)V99 COMP-3.
       01  WS-BONUS-IND      PIC S9(4) COMP.   * -1=NULL, 0=not null

           EXEC SQL
               SELECT BONUS
               INTO  :WS-BONUS :WS-BONUS-IND
               FROM   EMPLOYEE
               WHERE  EMPNO = :WS-EMPNO
           END-EXEC

           IF WS-BONUS-IND = -1
               DISPLAY 'No bonus assigned'
           ELSE
               DISPLAY 'Bonus: ' WS-BONUS
           END-IF.

A null indicator value of -1 means the column was NULL. 0 means not NULL. Positive values indicate truncation (for character data).

Singleton SELECT — One Row

Use singleton SELECT when your WHERE clause guarantees exactly one row:

cobol

       1000-GET-DEPT-NAME.
           EXEC SQL
               SELECT DEPTNAME, MGRNO
               INTO  :WS-DEPTNAME, :WS-MGRNO :WS-MGRNO-IND
               FROM   DEPARTMENT
               WHERE  DEPTNO = :WS-DEPT
           END-EXEC

           IF SQLCODE = +100
               MOVE 'UNKNOWN' TO WS-DEPTNAME
           ELSE IF SQLCODE NOT = 0
               PERFORM 9900-SQL-ERROR
           END-IF.

If the SELECT returns more than one row, DB2 raises SQLCODE -811 (multiple rows returned for singleton SELECT). Use a cursor when multiple rows are expected.

Cursors — Processing Multiple Rows

A cursor is a named pointer into a result set. You DECLARE it, OPEN it (which executes the query), FETCH rows one at a time in a loop, and CLOSE it when done.

Step 1 — DECLARE the Cursor

cobol

       WORKING-STORAGE SECTION.
       EXEC SQL
           DECLARE EMP-CURSOR CURSOR FOR
           SELECT EMPNO, LASTNAME, SALARY, WORKDEPT
           FROM   EMPLOYEE
           WHERE  WORKDEPT = :WS-DEPT
           ORDER BY SALARY DESC
       END-EXEC.

The DECLARE is a declarative — it does not execute the query. Place it in WORKING-STORAGE or after the PROCEDURE DIVISION header.

Step 2 — OPEN the Cursor

cobol

       2000-OPEN-CURSOR.
           MOVE 'A00' TO WS-DEPT
           EXEC SQL
               OPEN EMP-CURSOR
           END-EXEC
           IF SQLCODE NOT = 0
               PERFORM 9900-SQL-ERROR
           END-IF.

OPEN executes the SELECT and positions the cursor before the first row.

Step 3 — FETCH in a Loop

cobol

       2100-FETCH-LOOP.
           PERFORM UNTIL WS-EOF = 'Y'
               EXEC SQL
                   FETCH EMP-CURSOR
                   INTO :WS-EMPNO,
                        :WS-LASTNAME,
                        :WS-SALARY,
                        :WS-WORKDEPT
               END-EXEC

               EVALUATE SQLCODE
                   WHEN 0
                       PERFORM 2200-PROCESS-EMPLOYEE
                   WHEN +100
                       MOVE 'Y' TO WS-EOF
                   WHEN OTHER
                       PERFORM 9900-SQL-ERROR
               END-EVALUATE
           END-PERFORM.

Step 4 — CLOSE the Cursor

cobol

       2300-CLOSE-CURSOR.
           EXEC SQL
               CLOSE EMP-CURSOR
           END-EXEC.

Always CLOSE cursors when done — open cursors consume DB2 thread resources.

INSERT, UPDATE, DELETE in COBOL

DML statements use host variables for values:

cobol

       3000-INSERT-EMPLOYEE.
           EXEC SQL
               INSERT INTO EMPLOYEE
                   (EMPNO, FIRSTNME, MIDINIT, LASTNAME,
                    WORKDEPT, EDLEVEL, SEX, SALARY)
               VALUES
                   (:WS-EMPNO, :WS-FIRSTNME, :WS-MIDINIT, :WS-LASTNAME,
                    :WS-WORKDEPT, :WS-EDLEVEL, :WS-SEX, :WS-SALARY)
           END-EXEC

           IF SQLCODE = 0
               EXEC SQL COMMIT END-EXEC
           ELSE IF SQLCODE = -803
               DISPLAY 'Duplicate employee number: ' WS-EMPNO
               EXEC SQL ROLLBACK END-EXEC
           ELSE
               PERFORM 9900-SQL-ERROR
               EXEC SQL ROLLBACK END-EXEC
           END-IF.

       3100-UPDATE-SALARY.
           EXEC SQL
               UPDATE EMPLOYEE
               SET    SALARY = :WS-NEW-SALARY
               WHERE  EMPNO  = :WS-EMPNO
           END-EXEC

           IF SQLCODE = 0
               DISPLAY 'Updated ' SQLERRD(3) ' rows'
           ELSE
               PERFORM 9900-SQL-ERROR
           END-IF.

SQLERRD(3) gives the number of rows affected by the last INSERT/UPDATE/DELETE.

Commit and Rollback

Transaction control in embedded SQL COBOL:

cobol

       EXEC SQL COMMIT   END-EXEC.   * Commit all changes since last commit
       EXEC SQL ROLLBACK END-EXEC.   * Undo all changes since last commit

In batch programs, commit frequently enough to avoid holding locks for extended periods. A common pattern is to commit every N records (e.g., every 1,000 rows processed).

Error Handling Routine

A standard SQL error routine:

cobol

       9900-SQL-ERROR.
           DISPLAY '*** DB2 ERROR ***'
           DISPLAY 'SQLCODE : ' SQLCODE
           DISPLAY 'SQLSTATE: ' SQLSTATE
           DISPLAY 'SQLERRM : ' SQLERRM
           EXEC SQL ROLLBACK END-EXEC
           MOVE 8 TO RETURN-CODE
           STOP RUN.

The JCL Compile-Precompile-Bind Workflow

A typical three-step JCL procedure for COBOL DB2 programs:

jcl

//STEP1    EXEC PGM=DSNHPC,PARM='HOST(COBOL),SOURCE'  * DB2 Precompile
//DBRMLIB  DD DSN=MY.DBRMLIB(EMPRPT),DISP=SHR
//SYSCIN   DD DSN=&&MODCBL,DISP=(NEW,PASS)
//SYSIN    DD DSN=MY.SOURCE(EMPRPT),DISP=SHR

//STEP2    EXEC PGM=IGYCRCTL,PARM='RENT,NODECK,OBJECT' * COBOL Compile
//SYSLIN   DD DSN=&&OBJ,DISP=(NEW,PASS)
//SYSIN    DD DSN=&&MODCBL,DISP=(OLD,DELETE)

//STEP3    EXEC PGM=IEWL,PARM='RENT,LIST'              * Link-Edit
//SYSLIB   DD DSN=DSN.SDSNLOAD,DISP=SHR
//SYSLIN   DD DSN=&&OBJ,DISP=(OLD,DELETE)
//SYSLMOD  DD DSN=MY.LOADLIB(EMPRPT),DISP=SHR

//STEP4    EXEC PGM=IKJEFT01                           * BIND
//SYSTSIN  DD *
  DSN SYSTEM(DSN1)
  BIND PACKAGE(EMPPKG) MEMBER(EMPRPT) ACTION(REPLACE) ISOLATION(CS)
  END
/*

Next Steps

With DB2 embedded SQL in COBOL mastered, you are ready for online transaction processing. Read the DB2 CICS Transactions guide to learn how CICS programs access DB2 in an OLTP environment. For performance tuning your SQL, see the DB2 EXPLAIN and Optimization guide. Full learning path at the DB2 Mastery course hub.

DB2 COBOL Embedded SQL: Complete Guide with Examples

DB2 COBOL Embedded SQL: Complete Guide

How Embedded SQL Works

The SQLCA — SQL Communication Area

Host Variables

NULL Indicator Variables

Singleton SELECT — One Row

Cursors — Processing Multiple Rows

Step 1 — DECLARE the Cursor

Step 2 — OPEN the Cursor

Step 3 — FETCH in a Loop

Step 4 — CLOSE the Cursor

INSERT, UPDATE, DELETE in COBOL

Commit and Rollback

Error Handling Routine

The JCL Compile-Precompile-Bind Workflow

Next Steps