Db2 Embedded SQL
Db2 for z/OS (current release: Db2 13) is the main relational database on the mainframe.
36.1 Concepts
Db2 for z/OS (current release: Db2 13) is the main relational database on the mainframe. COBOL programs embed SQL between EXEC SQL and END-EXEC:
Host variable COBOL data item used in SQL, prefixed with : (:HV-EMPNO) Indicator variable
PIC S9(4) COMP; negative = column is NULL SQL Communication Area; SQLCODE 0 = OK, +100 = no (more) rows, negative = error; SQLSTATE is the standard 5-character code
Tool that generates a COBOL copybook (host-variable structure + DECLARE TABLE) from a table definition
Iterates over a multi-row result set (DECLARE, OPEN, FETCH, CLOSE) Precompile / coprocessor
Separates SQL from COBOL, producing a DBRM; with Enterprise COBOL the integrated coprocessor (SQL compiler option) is standard
Optimizes the DBRM's SQL into a package; packages are grouped into a plan that the program runs under
36.2 Example: list employees of a department
Uses the Db2 sample table EMP (EMPNO CHAR(6), LASTNAME VARCHAR(15), WORKDEPT CHAR(3), SALARY DECIMAL(9,2), BONUS DECIMAL(9,2)).
Other statements:
VARCHAR maps to a group with two level-49 items (length + text).
A singleton SELECT INTO that finds more than one row returns SQLCODE -811; common others: -803 duplicate key, -805/-818 package/timestamp mismatch (re-bind), -904 resource unavailable, -911/-913 deadlock or timeout.
EXEC SQL WHENEVER SQLERROR GO TO 9000-SQL-ERROR END-EXEC exists but hides control flow; explicit SQLCODE checks are preferred.
Use DSNTIAR to format SQLCA error messages; use multi-row FETCH ... FOR n ROWS with host-variable arrays for high-volume batch.
IDENTIFICATION DIVISION.
PROGRAM-ID. EMPLIST.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
01 WS-DEPT PIC X(3) VALUE "D11".
01 HV-EMPNO PIC X(6).
01 HV-LASTNAME.
49 HV-LASTNAME-LEN PIC S9(4) COMP.
49 HV-LASTNAME-TXT PIC X(15).
01 HV-SALARY PIC S9(7)V99 COMP-3.
01 HV-BONUS PIC S9(7)V99 COMP-3.
01 IND-BONUS PIC S9(4) COMP.
01 WS-ROWS PIC 9(5) VALUE 0.
01 ED-SALARY PIC Z,ZZZ,ZZ9.99.
01 ED-SQLCODE PIC -(8)9.
EXEC SQL
DECLARE EMPCUR CURSOR FOR
SELECT EMPNO, LASTNAME, SALARY, BONUS
FROM EMP
WHERE WORKDEPT = :WS-DEPT
ORDER BY SALARY DESC
END-EXEC.
PROCEDURE DIVISION.
0000-MAIN.
EXEC SQL OPEN EMPCUR END-EXEC
IF SQLCODE NOT = 0
PERFORM 9000-SQL-ERROR
END-IF
PERFORM UNTIL SQLCODE = 100
EXEC SQL
FETCH EMPCUR
…EXEC SQL
SELECT LASTNAME, SALARY INTO :HV-LASTNAME, :HV-SALARY
FROM EMP WHERE EMPNO = :HV-EMPNO
END-EXEC
EXEC SQL
UPDATE EMP SET SALARY = SALARY * 1.05
WHERE WORKDEPT = :WS-DEPT
END-EXEC
EXEC SQL COMMIT END-EXEC36.3 Build and run on z/OS
Compile with the Db2 coprocessor (SQL option) → object module + DBRM.
Link-edit with the Db2 language interface (DSNELI for TSO batch, DSNCLI for CICS, DSNRLI/DSNALI for others).
BIND the DBRM:
Run under the TSO terminal monitor program in batch: an EXEC PGM=IKJEFT01 step whose SYSTSIN contains DSN SYSTEM(DB2P), RUN PROGRAM(EMPLIST) PLAN(EMPPLAN), and END, with your load library in STEPLIB. (The DBRMLIB DD for the bind and the Db2 load libraries are site-specific.)
Off the mainframe: GnuCOBOL can use embedded SQL through open-source precompilers such as GixSQL or Open COBOL ESQL (ocesql) with PostgreSQL, MySQL, or ODBC databases; Visual COBOL includes OpenESQL and Db2 support.
//BIND EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB2P)
BIND PACKAGE(EMPCOLL) MEMBER(EMPLIST) ACTION(REPLACE) -
ISOLATION(CS) VALIDATE(BIND)
BIND PLAN(EMPPLAN) PKLIST(EMPCOLL.*) ACTION(REPLACE)
END
/*