C, PL/I, Assembler, REXX and DB2

The same Db2, different wrappers

Sample programs shipped with Db2 illustrate the split: DSN8BC3 (COBOL static), DSN8BD3 (C static), DSN8BP3 (PL/I static), DSNTEP2 (PL/I dynamic). REXX does not appear in that precompile list because IBM documents no program preparation for REXX SQL.

C and C++

Embedded SQL

Place SQL wherever an executable C statement is legal. Each statement begins with EXEC SQL — those two keywords on one line — and ends with a semicolon. You can continue the SQL across following lines. C block comments /* ... */ may appear inside SQL where a blank is allowed, but not between EXEC and SQL. C++ // comments are for C statements, not for embedded SQL; use SQL comments inside the SQL text.

1
2
3
4
5
6
7
8
9
10
11
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
char hv_empno[7];
char hv_lastname[16];
EXEC SQL END DECLARE SECTION;
 
EXEC SQL
  SELECT LASTNAME
    INTO :hv_lastname
    FROM DSN8C10.EMP
   WHERE EMPNO = :hv_empno;

SQL precompiler and host variables

C/C++ go through the Db2 precompiler or a coprocessor, producing a DBRM and modified source. Host variables are ordinary C objects, referenced with a colon in SQL. The coprocessor is more flexible about where they are declared; the classic precompiler wants a DECLARE SECTION for standard host variables. Names must be valid C identifiers. Match Db2 types: CHAR to null-terminated or VARCHAR structs as your shop standardizes; DECIMAL often to a packed struct; DATE/TIME/TIMESTAMP to character buffers of the documented length.

SQLCA and SQLDA

INCLUDE SQLCA generates a C struct sqlca with lowercase members: sqlcode, sqlstate, sqlerrmc, sqlerrd[6] (zero-based!). INCLUDE SQLDA generates the descriptor used with DESCRIBE, FETCH USING DESCRIPTOR, and EXECUTE USING DESCRIPTOR. Check sqlcode after every executable statement, same discipline as COBOL.

Static SQL, dynamic SQL, and cursors

Static SQL is written in the source and bound in the package. Dynamic SQL uses PREPARE from a char buffer, then EXECUTE or a cursor. DECLARE CURSOR / OPEN / FETCH / CLOSE work as in COBOL; rowset FETCH needs WITH ROWSET POSITIONING and C arrays. CICS and IMS still forbid SQL COMMIT — call the transaction manager from C the same as from COBOL.

LOBs, XML, and Db2 APIs

LOB and XML columns use locator host variables or file-reference variables; you FETCH a locator then use HOLD LOCATOR / LOB routines or XMLSERIALIZE depending on the design. Besides EXEC SQL, C on z/OS can use ODBC/CLI (no DBRM for those calls) and can call stored procedures. Do not mix “I linked ODBC” with “I forgot to BIND the embedded package” — they are different stacks.

PL/I

PL/I embedded SQL also uses EXEC SQL ... ;. Host variables are PL/I variables with a colon in SQL. INCLUDE SQLCA produces a DECLARE with SQLCODE FIXED(31) BINARY, SQLERRM CHAR(70) VAR, SQLERRD(6), SQLWARN0–A, and SQLSTATE CHAR(5). INCLUDE SQLDA is available for dynamic SQL.

1
2
3
4
5
6
EXEC SQL INCLUDE SQLCA;
EXEC SQL
  SELECT LASTNAME
    INTO :LASTNAME
    FROM DSN8C10.EMP
   WHERE EMPNO = :EMPNO;

Static and dynamic SQL, cursors, LOBs, and XML follow the same SQL Reference rules as COBOL. DSNTEP2 is the famous dynamic PL/I sample: it PREPARE/DESCRIBE/FETCHes whatever you put in SYSIN. Preparation is precompile (or coprocessor), PL/I compile, link with the right attachment, BIND PACKAGE. PL/I shops still hit -818 when the DBRM and load module drift.

Assembler and Db2 assembler programming

High-volume exits, glue CSECTs, and old tools still embed SQL in assembler. The precompiler recognizes EXEC SQL in assembler source, writes a DBRM, and replaces SQL with a parameter list plus a call to DSNHLI.

DSNHLI and SQL communication

DSNHLI is the Host Language Interface. Your program does not pick a Db2 address space by coding DSNHLI itself; the attachment facility stub you include at link-edit is DSNHLI (or an alias of it).

SQL communication is still the SQLCA: SQLCAID, SQLCODE, SQLERRM as H,CL70, SQLERRD 6F, SQLWARN flags, SQLSTATE CL5. After each executable SQL, branch on SQLCODE. Dynamic SQL uses an SQLDA you map with DSECTs from INCLUDE SQLDA. Assembler does not get you out of BIND or out of CICS SYNCPOINT rules.

REXX, REXX SQL, and DSNREXX

DSNREXX is how TSO/E REXX issues SQL. You do not precompile the exec. You add the host command environment, connect to a subsystem, and run SQL dynamically.

Connection handling

1
2
3
4
5
6
7
8
9
10
11
12
/* REXX */
call RXSUBCOM 'ADD', 'DSNREXX', 'DSNREXX'
ssid = 'DB2P'
address DSNREXX 'CONNECT' ssid
if SQLCODE <> 0 then do
  say 'CONNECT failed' SQLCODE SQLSTATE
  exit 8
end
/* SQL here */
address DSNREXX 'EXECSQL COMMIT'
address DSNREXX 'DISCONNECT'
call RXSUBCOM 'DELETE', 'DSNREXX', 'DSNREXX'

• CONNECT — attach to the SSID (not allowed in a REXX stored procedure; Db2 already connected)
• DISCONNECT — leave Db2 (also not for REXX stored procedures)
• EXECSQL — run one SQL statement (including PREPARE, DECLARE, OPEN, FETCH, COMMIT)

Batch invocation is typically IKJEFT01 with SYSTSIN calling the exec, STEPLIB to SDSNLOAD, and the DSNREXX plan available. If you exit without COMMIT/ROLLBACK/DISCONNECT, behaviour depends on how the thread ends — IBM’s guidance is to COMMIT or ROLLBACK explicitly so you are not surprised by termination processing.

SQL statements and result processing

All REXX SQL is dynamic. A usual SELECT pattern:

1. Build a statement string
2. EXECSQL PREPARE s1 FROM :sqlstmt
3. EXECSQL DECLARE c1 CURSOR FOR s1
4. EXECSQL DESCRIBE s1 INTO :outsqlda (REXX SQLDA stem)
5. EXECSQL OPEN c1
6. Loop EXECSQL FETCH c1 USING DESCRIPTOR :outsqlda until SQLCODE = 100
7. EXECSQL CLOSE c1

After each EXECSQL, REXX variables SQLCODE and SQLSTATE are set. FETCH deposits column values into the SQLDA / stem variables DSNREXX defines for that describe. You write the loop; there is no automatic result-set object. Parameter markers work on PREPARE/EXECUTE the same as in COBOL dynamic SQL — prefer them over concatenating unchecked text.

Choosing a language

• COBOL — default for CICS/IMS/batch business logic
• C/C++ — tools, DDF-adjacent code, shops that already compile C on z/OS
• PL/I — existing PL/I estates; DSNTEP2-style dynamic processors
• Assembler — thin interfaces, exits, when every instruction is budgeted
• REXX — DBA utilities, one-off fixes, automation; not a replacement for a bound high-volume CICS transaction

LOB locators, XML, isolation, and lock duration are SQL and bind options, not language features. If a cursor trick works in COBOL, the SQL works in C; only the host-variable declaration changes.

Explain It Like I'm Five

Db2 is one kitchen. COBOL, C, PL/I, and assembler are different waiters who all fill out the same order pad (SQL) and wear the same name tag on their jacket (DSNHLI) so the kitchen knows which door they came through (TSO, CICS, IMS). REXX is a walk-up window: you shout the order in plain words every time (dynamic SQL) and you do not get a filed recipe card (no DBRM of your exec). Everyone still checks the sticky note that comes back with the plate (SQLCODE) before they tell the customer the food is ready.

Exercises

1. Rewrite a COBOL SELECT INTO as C EXEC SQL with a DECLARE SECTION and sqlcode check.
2. List which attachment stub you would INCLUDE for a C CAF batch job versus a CICS C program.
3. Explain why a REXX exec that FETCHes 100,000 rows is usually the wrong tool compared with a bound COBOL cursor.
4. In assembler, where does SQLSTATE live in the INCLUDE SQLCA layout?
5. Write a DSNREXX CONNECT / EXECSQL SET CURRENT SQLID / DISCONNECT skeleton and note why a REXX stored procedure must omit CONNECT.