Embedded SQL and Db2

The examples use one table. Each row is one checkout: who borrowed which book, when it is due, when it came back (null while the book is still out) and the fine assessed (null until one is). The table is the relational twin of the LOANREC record of the file examples; the nullable columns are there on purpose, to show indicator variables. The names and sizes are an original design for this reference.

On Db2 for z/OS a table with a primary key is marked unavailable until its primary index is created (unless the table space is created implicitly, in which case Db2 creates the index), which is why the second statement is there; table space and database clauses are left out (the installation’s defaults apply), and USERID stands for the schema, as USERID.CIRC.BOOKMAST does for data sets elsewhere in this section. The table is created once, outside the COBOL programs, usually by a DBA or a deployment job.

Source: Db2 13 for z/OS: CREATE TABLE statement

An SQL statement in a COBOL program is a block that starts with EXEC SQL and ends with END-EXEC, placed where a COBOL statement or a data description entry is allowed. The Programming Guide gives the rules that matter in practice:

The statements fall in three groups. Declarative statements such as DECLARE CURSOR, DECLARE TABLE, INCLUDE and WHENEVER produce no call; they only inform the compiler or the preparation step. Executable statements (SELECT INTO, OPEN, FETCH, CLOSE, INSERT, UPDATE, DELETE, COMMIT, ROLLBACK, GET DIAGNOSTICS) become calls to Db2. And dynamic SQL (PREPARE, EXECUTE IMMEDIATE, built at run time) builds the statement text in a character host variable; it is outside the scope of this page. The static form used here is checked and optimised at bind time, which is why a batch program runs the same access path every night.

Source: Enterprise COBOL for z/OS 6.5 Programming Guide: Coding SQL statements in COBOL; Db2 13 for z/OS: COBOL applications that issue SQL statements; Db2 13 for z/OS: Embedded SQL programming

A host variable is a COBOL data item that an SQL statement reads or sets. Inside the SQL text its name carries a colon prefix, :WS-WANTED-BOOK, so that Db2 can tell it from a column; outside the SQL text it is an ordinary COBOL name and has no colon. The rules, from the Db2 13 documentation:

An indicator variable is a 2-byte binary integer that travels with a host variable and carries what a COBOL item cannot: whether the column value is null. It follows the host variable in the SQL text, with or without a colon before it: :FINE-AMOUNT :WS-FINE-IND. For a value coming from Db2, a negative indicator means the column is null and the host variable is left unchanged; zero or more means the host variable holds a real value; and -2 means the value was null because of an arithmetic or conversion error that Db2 turned into a null. A positive indicator after a string FETCH or SELECT INTO means the string was truncated and holds the original length, with a warning in the communication area. For a value going to Db2, the program sets the indicator: -1 stores a null (the host variable is ignored) and 0 stores the host variable.

Reading a nullable column without an indicator is an error, SQLCODE -305 (SQLSTATE 22002): the statement fails and nothing is retrieved. This also applies to aggregates such as MAX and SUM, which are null when no row qualified. The practical rule is to give an indicator to every host variable that can receive a column that is not NOT NULL, and every aggregate.

Writing the host variables by hand for a table of thirty columns is a good way to introduce mismatches. DCLGEN (declarations generator) reads the Db2 catalog and writes a source member with two parts: an EXEC SQL DECLARE ... TABLE statement that lists the columns, and a COBOL record description with one level-10 item per column, with a name made from the column name with underscores turned into hyphens. The coprocessor checks table and column references against the DECLARE TABLE text at compile time and against the catalog at bind time, so generating the member keeps the program and the table in step.

DCLGEN is a DSN subcommand, run from DB2I or in a batch step; its options choose the table, the target library member and the language, the string delimiter (APOST or QUOTE), a prefix for the field names (NAMES) and the structure name, whether to append the column names (COLSUFFIX), and whether to generate an indicator array (INDVAR(YES)) with one PIC S9(4) USAGE COMP element per column. The output for LOAN looks like the following. The banner comments and the exact picture clauses depend on the options and the Db2 level, so treat the listing as the shape of the output, not as a byte-for-byte copy (z/OS; not run here).

The program brings the member in with EXEC SQL INCLUDE LOANDCL END-EXEC.. INCLUDE member-name copies a member of a partitioned data set into the source while the program is prepared; it is not an executable statement, the member cannot contain another INCLUDE, and the name must be a valid SQL identifier (up to eight characters here, because it is a PDS member). With the coprocessor the include is handled like a COPY statement: the member is searched in the SYSLIB concatenation of the compile step, and the period after END-EXEC is required. The separate precompiler makes that period optional. EXEC SQL INCLUDE SQLCA END-EXEC. is the other form: it adds the standard communication area, once per program, in WORKING-STORAGE or LINKAGE.

Source: Db2 13 for z/OS: DCLGEN (declarations generator); Db2 13 for z/OS: DCLGEN subcommand (DSN); Db2 13 for z/OS: INCLUDE statement; Enterprise COBOL for z/OS 6.5 Programming Guide: Differences between the Db2 precompiler and coprocessor

After every SQL statement Db2 reports the outcome in the SQL communication area (SQLCA). EXEC SQL INCLUDE SQLCA END-EXEC. defines it. The fields a program uses are:

A program that does not want the whole area can declare SQLCODE as PIC S9(9) COMP-5 and SQLSTATE as PIC X(5) itself, inside a declare section, and compile with STDSQL(YES); then no SQLCA may be included. Both forms are legal, but the INCLUDE SQLCA form is the common one and is used here. SQLSTATE cannot be an element of a structure.

The codes that a batch program meets most often are in the table. Every code in it comes from the Db2 13 for z/OS codes reference.

Source: Db2 13 for z/OS: Defining the SQL communications area, SQLSTATE and SQLCODE in COBOL; Db2 13 codes: SQLCODE -811; SQLCODE -305; SQLCODE -803; SQLCODE -501; SQLCODE -508; SQLCODE -805; SQLCODE -818; SQLCODE -911

WHENEVER is a non-executable statement that tells the compiler what code to generate after each following SQL statement. There are three conditions: NOT FOUND (SQLCODE +100, SQLSTATE 02000), SQLERROR (any negative SQLCODE) and SQLWARNING (SQLWARN0 is W, or a positive SQLCODE other than +100). There are two actions: CONTINUE, and GO TO a label, which in COBOL can be a section name or an unqualified paragraph name (GOTO is accepted too).

The scope is the order of the statements in the source listing, not the order of execution. Each SQL statement is governed by the nearest WHENEVER of each condition above it in the source; with none above it, CONTINUE applies. That makes WHENEVER easy to misread in a program with many paragraphs, and it hides an unstructured jump (the generated code is a GO TO) behind SQL text, which is why modern standards prefer an explicit test of SQLCODE after each statement, as the examples on this page do. If WHENEVER SQLERROR GO TO is used, the error paragraph must begin with WHENEVER SQLERROR CONTINUE; otherwise a failing statement inside the error paragraph jumps to the paragraph again and the program loops. The error paragraph of the cursor example below shows that line.

Source: Db2 13 for z/OS: WHENEVER statement

SQLCODE and SQLSTATE say what happened; GET DIAGNOSTICS says more. It returns information about the previous SQL statement (not counting itself) from the diagnostics area in three groups: statement items (ROW_COUNT, the rows affected, and NUMBER, the number of conditions), condition items for each warning or error (MESSAGE_TEXT, RETURNED_SQLSTATE, DB2_RETURNED_SQLCODE, DB2_ROW_NUMBER, CURSOR_NAME), and connection items after a CONNECT. A condition is selected by number, GET DIAGNOSTICS CONDITION 1 .... The statement is most useful after a multi-row INSERT written NOT ATOMIC CONTINUE ON SQLEXCEPTION, where the SQLCODE can be zero while some rows failed, and for MESSAGE_TEXT, which turns the code into readable text. Retrieve NUMBER first when you loop over conditions; it is at least one. The host variable for MESSAGE_TEXT is a VARCHAR-style group with level-49 items, as in the table of types above; the older alternative is to call the DSNTIAR routine, which formats the communication area into lines of 72 to 240 characters, but the Programming Guide for Db2 says programs that need long message tokens should use GET DIAGNOSTICS. The cursor example below calls it in its error paragraph.

Source: Db2 13 for z/OS: GET DIAGNOSTICS statement; Db2 13 for z/OS: Checking the execution of SQL statements by using GET DIAGNOSTICS; Db2 13 for z/OS: Handling SQL error codes in COBOL applications

A singleton SELECT returns at most one row and puts the columns straight into host variables with INTO. It is the statement for a lookup by a unique key. If no row qualifies, the host variables are left unchanged and the result is SQLCODE +100; if more than one qualifies, the statement fails with -811 (unless a FETCH FIRST 1 ROW ONLY or an offset clause limits the result). A null column needs an indicator, as described above.

The program below looks up one loan by its key and shows the three outcomes (z/OS; not run here). DUE-DATE, RETURN-DATE and FINE-AMOUNT come from the DCLGEN member; RETURN_DATE and FINE_AMOUNT are nullable, so each has an indicator.

The WHERE clause names the whole primary key, so at most one row can qualify and -811 cannot occur; leaving CHECKOUT_DATE out would make it possible, because a member can borrow the same book more than once. DUE-DATE has no indicator because the column is NOT NULL; if it were nullable and null, the statement would end with -305 and the host variable would stay unchanged. The program shows no output here, because it was not run; against a table that holds the loan of the Circulation scenario (member 100, book 1, checked out on 2026-10-20 and still out) it would show the due date, RETURNED (still out) and FINE none assessed.

Source: Db2 13 for z/OS: SELECT INTO statement; SQLCODE -811

A query that can return many rows needs a cursor: a named pointer that walks the result table one row (or one rowset) at a time. The lifecycle has four statements, and the diagram shows how they interact with the program.

The figure follows LOANFINE, the program below. DECLARE CURSOR defines the cursor and its SELECT; it must appear in the source before the first statement that uses the cursor. OPEN evaluates the SELECT, with the current special registers and the values the host variables have at that moment, and positions the cursor before the first row; Db2 does not report an empty result at OPEN, only at the first FETCH. FETCH moves to the next row and copies it into the INTO host variables; SQLCODE +100 means no more rows. CLOSE ends the cursor and frees its resources; opening a cursor that is already open is an error, so a cursor used several times is closed in between.

Cursor names are SQL identifiers, so they cannot contain a hyphen (an SQL name with a hyphen is a subtraction). The COBOL habit of OVERDUE-CSR does not carry over: write OVERDUE_CSR. Host variable names in the SQL text, in contrast, may use hyphens.

A cursor declared WITH ROWSET POSITIONING can return a rowset with one FETCH: the columns of up to 32,767 rows go into host-variable arrays, which are ordinary level-01 items with OCCURS. One call to Db2 replaces many, which is the main reason to use it in a batch program that reads thousands of rows. The clause is FETCH NEXT ROWSET FROM cursor FOR n ROWS INTO :array, ...; the first fetch can be FETCH FIRST ROWSET. Each result column needs its own array, with one element per row of the rowset; a nullable column needs an indicator array. If the FOR n ROWS clause is left out of a later fetch, the size of the previous rowset fetch is used. SQLERRD(3) holds the number of rows actually returned: when the result table runs out inside a rowset, Db2 returns the rows that remain with SQLCODE +100 (and SQLSTATE 02000), and the program must process SQLERRD(3) rows before it stops. Copy SQLERRD(3) to a work item at once, because the next SQL statement overwrites it.

The program counts the loans still out, 100 rows at a time (z/OS; not run here):

The cursor has an ORDER BY, so it is read-only, which is fine for a report. After a failed fetch the contents of SQLERRD(3) mean nothing, which is why the error branch sets WS-GOT to zero before the rows are processed. Positioned UPDATE and DELETE are also available for rowsets (FOR ROW n OF ROWSET selects one row of the current rowset), with an updatable cursor.

Source: Db2 13 for z/OS: FETCH statement; Db2 13 for z/OS: Using host-variable arrays in SQL statements

A COBOL program with EXEC SQL is not ready after a compile. Db2 must receive the SQL statements and the host variable descriptions, check them against the catalog, choose access paths and keep the result. The path from the source to a running program is:

The compile turns each SQL block into COBOL calls to the Db2 language interface and produces a database request module (DBRM) that holds the SQL text and the host variable information. The link step produces the load module, which includes the language interface module of the environment (DSNELI for a program run under the DSN command processor; other modules exist for CICS, RRSAF and CAF). The DBRM goes through BIND PACKAGE, which validates the tables, views and columns against the catalog, checks authorisation, selects access paths and stores the result as a package in a collection. A plan lists the packages, or whole collections, that an application may use. At run time Db2 matches the program to its package by name, collection and a consistency token stamped into both the load module and the DBRM.

GnuCOBOL has no embedded SQL. Compiling a program with an EXEC SQL block, such as the next one, gives an error because the compiler sees EXEC as an unknown statement:

Compiled with cobc -x -Wall nosql.cob it prints (a real GnuCOBOL 3.2 run on 2026-10-20):

Embedded SQL under GnuCOBOL needs an ESQL preprocessor that translates the EXEC SQL blocks into plain CALL statements to a runtime library before cobc runs. Two open-source tools do this. Both are community projects that are not part of GnuCOBOL and not supported by IBM or the GnuCOBOL maintainers; they were looked at on 2026-10-20 only through their repository pages, and nothing here was installed or run:

What is different compared with Db2 on z/OS, whatever the tool: there is no DCLGEN (host variables are declared by hand, or generated by a script), no DBRM, no BIND, no package or plan and no collection, because the preprocessor emits calls that send the SQL text to the database at run time; connections are explicit CONNECT statements; and the SQL dialect is the database’s own, so CURRENT DATE, DAYS() and FETCH FIRST ... ROWS ONLY in the programs above would be CURRENT_DATE, date subtraction and LIMIT for PostgreSQL. The data type mapping follows each tool’s documentation, not the Db2 table above. The statement set also differs: multi-row FETCH into arrays, GET DIAGNOSTICS and WITH HOLD should be checked against the tool before relying on them. Programs written for one environment are therefore ported by editing, not by recompiling.

Source: GixSQL repository (community tool); Open COBOL ESQL repository (community tool); GnuCOBOL 3.2 manual