Embedded SQL in CICS

Embedded SQL means writing SQL statements directly inside the COBOL program. CICS programs follow the same embedded SQL rules as batch programs, plus a few CICS-specific points.

Embedded SQL rules

• Each SQL statement starts with EXEC SQL and ends with END-EXEC.
• Code the lines of the SQL statement in columns 12 through 72 of the source line.
• For continuation, the first non-blank character on the continued line must be a quotation mark.
• Do not code an SQL statement inside a COBOL COPY member.
• When one SQL statement immediately follows another, always code a period after the END-EXEC of the first statement.
• COBOL variables used inside SQL are called host variables. Prefix them with a colon, for example :WS-CUST-NO.

Preparing a CICS DB2 program

• Translate the program if it contains EXEC CICS commands, turning them into calls the compiler understands.
• Run the DB2 precompile (or use the integrated SQL coprocessor). It pulls the SQL out of the source and builds a Database Request Module (DBRM).
• Compile the modified source with the COBOL compiler to get the object module.
• Bind the DBRM into a package, then bind the package into a plan. The plan holds the access strategy DB2 will use at run time.
• Link-edit the object module into a load module and place it in a CICS load library.

Host variables

• A host variable is an ordinary COBOL data item declared in WORKING-STORAGE and used inside an SQL statement with a colon prefix.
• The COBOL picture must be compatible with the DB2 column type, for example PIC X for CHAR and PIC S9(9) COMP for INTEGER.
• For a column that can be null, code an indicator variable (PIC S9(4) COMP). A negative indicator on output means the column was null.
• Always check SQLCODE after the statement runs. The host variables hold valid data only when SQLCODE is 0 (or +100 for not found).

Example

• This SELECT reads two columns into two host variables for one customer row.
• EXEC SQL SELECT CUSTNAME, CITY INTO :WS-CUST-NAME, :WS-CITY FROM CUSTOMER WHERE CUSTNO = :WS-CUST-NO END-EXEC. IF SQLCODE = 0 MOVE WS-CUST-NAME TO CUSTNAMEO END-IF.
• The colon tells the precompiler that WS-CUST-NAME, WS-CITY and WS-CUST-NO are host variables, not column names.