Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 38 ยท Modern COBOL and Mainframes

Embedded SQL and DB2

Learn how COBOL programs talk to relational databases such as DB2 - EXEC SQL blocks, host variables, SQLCA and SQLCODE, singleton SELECT, cursors with FETCH, NULL indicators, updates and commit.

Indexed files are fine for one key, but business questions soon need joins, filters and ad-hoc reports. Most large COBOL shops therefore keep data in a relational database, on z/OS usually Db2, and COBOL talks to it with embedded SQL: SQL statements written directly inside the program between EXEC SQL and END-EXEC. A precompiler (or a coprocessor built into the compiler) turns them into calls to the database runtime before the real compilation.

You need to know basic SQL for this lesson (the SQL track teaches it). Here we focus on the COBOL side: how data goes in and out and how errors are reported.

The shape of a Db2 program

COBOL
       WORKING-STORAGE SECTION.
           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC SQL INCLUDE EMPREC END-EXEC.
       01  HV-EMP-ID      PIC S9(9) COMP.
       01  HV-EMP-NAME    PIC X(30).
       01  HV-SALARY      PIC S9(7)V99 COMP-3.
       01  HV-SALARY-IND  PIC S9(4) COMP.
  • INCLUDE SQLCA adds the SQL Communication Area, a group of fields the database fills after every statement. The key field is SQLCODE.
  • Host variables are ordinary COBOL data items used in SQL, written with a leading colon: :HV-EMP-ID. Their types must match the column types: INTEGER maps to PIC S9(9) COMP, DECIMAL(9,2) to PIC S9(7)V99 COMP-3, CHAR(30) to PIC X(30), VARCHAR to a group with a length and text field.
  • INCLUDE EMPREC pulls in a declaration generated by DCLGEN from the table definition, so host structures stay in step with the table (the same idea as a copybook).

SQLCODE: did it work?

After each EXEC SQL check SQLCODE:

SQLCODE Meaning
0 Success
+100 No row found, or no more rows from a cursor
negative Error: -803 duplicate key, -811 the singleton returned more than one row, -904 resource unavailable, -911 deadlock or timeout (rolled back), -305 NULL returned without an indicator
positive (not 100) Warning

You can have the precompiler generate checks with WHENEVER, but modern style prefers explicit tests with an EVALUATE after each statement.

COBOL
           EVALUATE SQLCODE
               WHEN 0     CONTINUE
               WHEN +100  PERFORM NOT-FOUND
               WHEN OTHER PERFORM SQL-ERROR
           END-EVALUATE

The SQL-ERROR paragraph typically displays SQLCODE, SQLERRMC (message tokens) and calls DSNTIAR to format a readable message, then sets the return code (error handling).

Reading one row: singleton SELECT

When the query returns at most one row, select straight into host variables with INTO:

COBOL
           MOVE 1042 TO HV-EMP-ID
           EXEC SQL
               SELECT NAME, SALARY
                 INTO :HV-EMP-NAME, :HV-SALARY
                 FROM EMPLOYEE
                WHERE EMP_ID = :HV-EMP-ID
           END-EXEC

If more than one row matches, SQLCODE is -811. Zero rows gives +100.

NULL values and indicators

SQL columns may be NULL, which COBOL data cannot represent. An indicator variable (a 2-byte binary field) follows the host variable: :HV-SALARY :HV-SALARY-IND. After the statement, the indicator is 0 if the value is valid, a negative number if NULL. Without an indicator, a NULL gives SQLCODE -305. To send NULL into the database, move -1 to the indicator before the statement.

Many rows: cursors

A query returning many rows needs a cursor, which is how a set-based answer is read one row at a time, much like a sequential file. It has four statements:

COBOL
           EXEC SQL
               DECLARE EMP-CUR CURSOR FOR
                   SELECT EMP_ID, NAME, SALARY
                     FROM EMPLOYEE
                    WHERE DEPT = :HV-DEPT
                    ORDER BY NAME
           END-EXEC
           EXEC SQL OPEN EMP-CUR END-EXEC
           PERFORM UNTIL SQLCODE NOT = 0
               EXEC SQL
                   FETCH EMP-CUR
                    INTO :HV-EMP-ID, :HV-EMP-NAME, :HV-SALARY
               END-EXEC
               IF SQLCODE = 0
                   PERFORM SHOW-EMPLOYEE
               END-IF
           END-PERFORM
           IF SQLCODE NOT = +100
               PERFORM SQL-ERROR
           END-IF
           EXEC SQL CLOSE EMP-CUR END-EXEC

DECLARE is not executable (it just describes the query); OPEN runs it; each FETCH returns the next row until SQLCODE becomes +100; CLOSE frees resources. Add FOR UPDATE OF SALARY to the cursor if you want UPDATE ... WHERE CURRENT OF EMP-CUR. WITH HOLD keeps a cursor open across a commit.

flowchart LR
    D["DECLARE cursor"] --> O["OPEN"]
    O --> F["FETCH"]
    F -->|"SQLCODE 0"| P["Process row"]
    P --> F
    F -->|"SQLCODE +100"| C["CLOSE"]

The cursor loop mirrors the read-ahead file loop.

Changing data and transactions

COBOL
           EXEC SQL
               UPDATE EMPLOYEE
                  SET SALARY = SALARY * 1.03
                WHERE DEPT = :HV-DEPT
           END-EXEC
           IF SQLCODE = 0
               EXEC SQL COMMIT END-EXEC
           ELSE
               EXEC SQL ROLLBACK END-EXEC
           END-IF

A unit of work is the set of changes between commits. COMMIT makes them permanent and releases locks; ROLLBACK undoes them. Batch programs that update millions of rows commit every few thousand rows so they never hold locks for hours and can restart from a checkpoint. Online programs (CICS) commit as part of syncpoint processing (CICS). INSERT and DELETE follow the same pattern; SQLERRD(3) holds the number of rows affected.

Preparing and running a Db2 program

  1. The DB2 precompiler (or coprocessor) extracts the SQL into a database request module (DBRM) and replaces statements by call code.
  2. The COBOL compiler and binder produce the load module as usual.
  3. BIND compiles the DBRM into a package or plan and checks authorizations and table names.
  4. In batch the program runs under the TSO attach through IKJEFT01 with DSN SYSTEM(DB2P) and RUN PROGRAM(...) (JCL).

Performance matters more than syntax: select only the columns you need, filter in the WHERE clause (not in COBOL), and never run a singleton SELECT in a loop where a join or a cursor would do.

Trying the logic with SQLite

The mainframe database is not available here, so this Python stand-in uses SQLite, which has the same relational ideas. cursor.fetchone() plays the part of FETCH, and a None answer is SQLCODE +100.

Python Stand-in: cursor loop, SQLCODE +100 and NULL indicators (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Python Stand-in: unit of work with COMMIT and ROLLBACK

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first block prints the three ENG employees in name order (Ada, Bob with NULL and ind=-1, Di) and then SQLCODE +100. The second shows that the over-large transfer fails the CHECK constraint, is rolled back, and leaves the balances at 70.0 and 80.0 from the first transfer.

Common mistakes

  1. Not testing SQLCODE after every statement.
  2. A column that can be NULL with no indicator variable (-305).
  3. Host variables whose type or length differs from the column, causing conversion errors or truncation.
  4. Singleton SELECT on a non-unique condition (-811): use a cursor or FETCH FIRST 1 ROW ONLY.
  5. Using SELECT * into a host structure: when someone adds a column the program breaks.
  6. Holding a cursor and locks open for long without committing.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does SQLCODE +100 mean?

    +100 signals no data: a singleton SELECT that finds nothing or a FETCH past the last row.
  2. 2. How do you write a host variable in an SQL statement?

    A colon tells the precompiler the name is a COBOL data item.
  3. 3. Why use a NULL indicator variable?

    An indicator of -1 means NULL; without it Db2 returns SQLCODE -305 for a NULL.
  4. 4. Which order of statements reads rows with a cursor?

    Declare the query, open it, fetch until +100, then close.

Summary

  • Embedded SQL lives between EXEC SQL and END-EXEC, with COBOL host variables prefixed by a colon.
  • SQLCODE reports success (0), no data (+100) or errors (negative); check it after every statement.
  • Singleton SELECT ... INTO reads one row; cursors (DECLARE, OPEN, FETCH, CLOSE) read many.
  • Indicators represent NULL; COMMIT and ROLLBACK define units of work, and batch jobs commit periodically.

Next: CICS and online transactions