Embedded SQL

Embedded SQL means SQL statements written directly inside a host language program such as COBOL. The DB2 precompiler reads the COBOL source, pulls out the SQL, and replaces it with calls that DB2 understands at runtime.

What is embedded SQL

• Embedded SQL is SQL coded inside a COBOL program instead of being typed interactively in SPUFI or QMF.
• Each SQL statement starts with EXEC SQL and ends with END-EXEC. Everything between them is SQL, not COBOL.
• The precompiler only processes what is inside EXEC SQL ... END-EXEC. The rest of the source goes to the COBOL compiler unchanged.
• One EXEC SQL ... END-EXEC block holds exactly one SQL statement. Do not code two SQL statements in one block.

Where embedded SQL can be coded

• EXEC SQL blocks can appear in the DATA DIVISION (for INCLUDE SQLCA, INCLUDE of DCLGEN copybooks, and DECLARE CURSOR) and in the PROCEDURE DIVISION (for executable statements like SELECT, INSERT, OPEN, FETCH, CLOSE, COMMIT).
• DECLARE CURSOR is not executable: it must sit in the DATA DIVISION (WORKING-STORAGE SECTION), not in the PROCEDURE DIVISION.
• INCLUDE SQLCA is normally the first EXEC SQL statement in WORKING-STORAGE, so that SQLCODE is available to the whole program.
• SQL statements must be coded in Area B (columns 8 to 72). EXEC SQL itself may start in column 8; continuation follows normal COBOL continuation rules.

Rules of embedded SQL in COBOL

• Terminate every block with END-EXEC followed by a period, because the block sits inside COBOL sentences.
• COBOL comment lines (* in column 7) are not allowed inside an EXEC SQL block. Keep comments outside the block.
• Host variables inside SQL must be prefixed with a colon (:), for example WHERE CUSTNO = :WS-CUSTNO.
• Table and column names follow DB2 naming rules; COBOL variable names follow COBOL rules. Do not use SQL reserved words as host variable names.
• Write SQL keywords in uppercase. Some older precompilers reject lowercase SQL.

Example showing where the EXEC SQL blocks sit in a program:-