Embedded SQL, precompile and BIND

On z/OS, SQL inside a COBOL program is not sent to the database as text at run time. It is extracted at compile time, prepared in advance, and stored in the database as a package. This is the single biggest difference from what you may be used to.

What the program looks like

Embedded SQL in COBOL

       WORKING-STORAGE SECTION.
           EXEC SQL INCLUDE SQLCA END-EXEC.
       01  WS-ACCT-ID       PIC X(10).
       01  WS-BALANCE       PIC S9(11)V99 COMP-3.
       01  WS-BAL-IND       PIC S9(4) COMP.

       PROCEDURE DIVISION.
           EXEC SQL
               SELECT BALANCE
                 INTO :WS-BALANCE :WS-BAL-IND
                 FROM PAYPROD.ACCOUNT
                WHERE ACCT_ID = :WS-ACCT-ID
                 WITH CS
           END-EXEC

           EVALUATE SQLCODE
               WHEN 0     PERFORM 3000-PROCESS
               WHEN +100  PERFORM 3100-NOT-FOUND
               WHEN OTHER PERFORM 9000-SQL-ERROR
           END-EVALUATE

Host variables are COBOL fields prefixed with a colon inside SQL.
Null indicators — the second variable — tell you whether the column was null. Without one, a null column causes an error.
SQLCA is a communication area DB2 fills in after every statement. SQLCODE is its most important field.
WITH CS names the isolation level for this statement — see the locking lesson.

The build pipeline

How SQL gets prepared ahead of time

SourceCOBOL with EXEC SQL

Precompile / coprocessorSQL removed, DBRM produced

Compile & bind programNormal COBOL build

BIND PACKAGEDBRM → package with an access path

RunProgram finds its package via a plan

Thing	What it is
DBRM	The extracted SQL from one program
Package	A bound DBRM: SQL plus the access path DB2 chose
Plan	What a program runs under; it lists the package collections to search
Collection	A named group of packages, often one per environment
Access path	DB2's chosen strategy: which index, what join order

The consequences you will actually feel

Change the SQL, and you must rebind. Otherwise the old package runs and your change appears to do nothing.
An access path can change under you. A REBIND after new statistics may pick a different index, and a job that ran in ten minutes now takes two hours. This is a real and common production incident.
-805 means the package was not found. Almost always a missing bind, or the wrong collection in the plan for the environment you are running in.
Statistics matter. DB2 chooses access paths from RUNSTATS data. Stale statistics on a table that has grown tenfold produce bad plans.

Example, line by line

COBOL + SQLWhat it means

EXEC SQL

Start of embedded SQL. The DB2 precompiler replaces this block with calls to DB2.

SELECT BALANCE INTO :WS-BAL

The result goes into the COBOL field WS-BAL — a host variable, marked with a colon.

FROM ACCOUNTS

The table being read.

WHERE ACCT_ID = :WS-ACCT-ID

The key comes from another host variable.

END-EXEC

End of the SQL statement.

IF SQLCODE = 100

No row matched: handle 'not found' instead of using an empty WS-BAL.

Try it yourself

TRY IT YOURSELF

Which SQLCODE means the query found no row?

Show a hint

It is positive: a warning, not an error.

Show the solution

+100 — not found. Negative codes are errors; 0 is success.