> "Every transaction at GlobalBank eventually touches DB2. Understanding embedded SQL isn't optional — it's how COBOL programs talk to the database that holds every account, every balance, every transaction record. Get it right, and the system hums...
In This Chapter
- 27.1 Why Embedded SQL?
- 27.2 EXEC SQL...END-EXEC — The Basic Syntax
- 27.3 Host Variables — COBOL Meets SQL
- 27.4 DCLGEN — Generating Host Variable Declarations
- 27.5 The SQLCA — SQL Communication Area
- 27.6 SELECT INTO — Single-Row Queries
- 27.8 INSERT, UPDATE, DELETE with Host Variables
- 27.9 The WHENEVER Statement — Automatic Error Handling
- 27.10 The Precompiler Process
- 27.11 Transaction Control — COMMIT and ROLLBACK
- 27.12 GlobalBank Case Study: Account Queries and Transaction History
- 27.14 Common Embedded SQL Patterns
- 27.15 The INCLUDE Statement
- 27.16 🧪 Try It Yourself: Student Lab Exercises
- 27.17 Advanced SQLCA Analysis
- 27.18 Working with Date and Time in Embedded SQL
- 27.19 Embedded SQL in Batch Programs — A Complete Pattern
- 27.20 The Student Mainframe Lab: Practicing Embedded SQL
- 27.21 Chapter Review Questions
- 27.22 Common Embedded SQL Mistakes and How to Avoid Them
- 27.23 Putting It All Together: The Embedded SQL Development Workflow
- 27.24 Summary
Chapter 27: Embedded SQL Fundamentals
"Every transaction at GlobalBank eventually touches DB2. Understanding embedded SQL isn't optional — it's how COBOL programs talk to the database that holds every account, every balance, every transaction record. Get it right, and the system hums. Get it wrong, and you get a 2 AM phone call." — Maria Chen, senior developer, GlobalBank
Tomás Rivera remembers the day MedClaim migrated its last VSAM-based claim lookup to DB2. "We had been reading sequential files and VSAM datasets for twenty years," he said. "Embedded SQL felt foreign at first — you are writing SQL inside COBOL, and the precompiler transforms it before the COBOL compiler even sees it. But once you understand the mechanics, it is the most powerful data access tool in the COBOL developer's toolkit."
This chapter introduces embedded SQL — the technique of writing SQL statements directly inside COBOL source code. On IBM mainframes, this almost always means COBOL interacting with DB2, IBM's relational database. We will cover the syntax for embedding SQL, the mechanism of host variables, the critical SQLCA error-handling structure, cursors for multi-row queries, and the precompiler process that makes it all work. By the end of this chapter, you will be able to write COBOL programs that query, insert, update, and delete database records with the same confidence you bring to file I/O.
27.1 Why Embedded SQL?
Before diving into syntax, let us understand why COBOL programs use embedded SQL rather than some other database access method.
The Historical Context
Early COBOL programs accessed data through sequential files, indexed files, and VSAM datasets. These file-based approaches worked well for batch processing but had limitations:
- No ad hoc query capability
- No multi-user concurrent access (without complex locking)
- No relational joins across different data sets
- No transaction management (commit/rollback)
- Limited data integrity enforcement
Relational databases — particularly IBM's DB2, introduced in 1983 — addressed all of these limitations. The question was: how should COBOL programs talk to DB2?
The Embedded SQL Approach
IBM's answer was embedded SQL: write SQL statements directly in your COBOL source code, marked by special delimiters. A precompiler processes the source before the COBOL compiler, replacing SQL statements with COBOL CALL statements to the DB2 runtime.
Source code Precompiler COBOL compiler Linker
(COBOL + SQL) → (Modified COBOL) → (Object code) → (Executable)
+ DBRM + DB2 bind
The alternatives to embedded SQL — dynamic SQL APIs, ODBC, JDBC — exist but are less common in traditional COBOL environments. Embedded SQL remains the dominant approach because:
- It is tightly integrated with COBOL syntax and data types
- The precompiler validates SQL at compile time
- DB2 can optimize static SQL at bind time (better performance than dynamic SQL)
- It has been the standard approach for forty years (vast institutional knowledge)
💡 Key Insight: Embedded SQL is static SQL — the SQL text is known at compile time and optimized at bind time. This is different from dynamic SQL, where the SQL text is constructed at runtime. Static SQL typically performs better because DB2 pre-optimizes the access paths. We will cover dynamic SQL in Chapter 29.
27.2 EXEC SQL...END-EXEC — The Basic Syntax
Every SQL statement in a COBOL program is enclosed in EXEC SQL and END-EXEC delimiters:
EXEC SQL
SELECT ACCT_BALANCE
INTO :WS-BALANCE
FROM ACCOUNT_MASTER
WHERE ACCT_NUMBER = :WS-ACCT-NUM
END-EXEC
Rules for EXEC SQL Blocks
- EXEC SQL must start in Area B (column 12 or beyond)
- END-EXEC terminates the SQL statement (note: no period after END-EXEC unless it is the last statement in a sentence)
- SQL statements inside the block follow SQL syntax, not COBOL syntax
- COBOL host variables are prefixed with a colon (
:WS-BALANCE) - Comments within EXEC SQL blocks use SQL comment style (
--or/* */) - Each EXEC SQL block contains exactly one SQL statement
*> CORRECT: One SQL statement per EXEC SQL block
EXEC SQL
SELECT ACCT_BALANCE
INTO :WS-BALANCE
FROM ACCOUNT_MASTER
WHERE ACCT_NUMBER = :WS-ACCT-NUM
END-EXEC
*> WRONG: Multiple SQL statements in one block
EXEC SQL
SELECT ... ;
UPDATE ... ;
END-EXEC
Where Can EXEC SQL Appear?
EXEC SQL blocks can appear in several divisions:
DATA DIVISION.
WORKING-STORAGE SECTION.
*> SQL INCLUDE for copybooks
EXEC SQL
INCLUDE SQLCA
END-EXEC
EXEC SQL
INCLUDE ACCT-DCL
END-EXEC
PROCEDURE DIVISION.
*> SQL DML (SELECT, INSERT, UPDATE, DELETE)
EXEC SQL
SELECT ...
END-EXEC
*> SQL cursor operations
EXEC SQL
OPEN ACCT-CURSOR
END-EXEC
*> SQL connection and transaction control
EXEC SQL
COMMIT
END-EXEC
27.3 Host Variables — COBOL Meets SQL
Host variables are the bridge between COBOL's data items and SQL's columns. They are ordinary COBOL data items that appear in SQL statements, prefixed with a colon.
Declaring Host Variables
Host variables are declared in the WORKING-STORAGE SECTION or LINKAGE SECTION, typically within an EXEC SQL DECLARE section (though this is optional in most compilers):
WORKING-STORAGE SECTION.
*> Explicit SQL host variable declaration
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 HV-ACCT-NUMBER PIC X(10).
01 HV-ACCT-HOLDER PIC X(40).
01 HV-BALANCE PIC S9(11)V99 COMP-3.
01 HV-STATUS PIC X(1).
01 HV-OPEN-DATE PIC X(10).
01 HV-TXN-AMOUNT PIC S9(9)V99 COMP-3.
01 HV-TXN-TYPE PIC X(2).
EXEC SQL END DECLARE SECTION END-EXEC.
Data Type Mapping: COBOL to DB2
| DB2 Column Type | COBOL Host Variable | Notes |
|---|---|---|
| CHAR(n) | PIC X(n) | Fixed-length string |
| VARCHAR(n) | 01 HV-NAME. 49 HV-LEN PIC S9(4) COMP. 49 HV-TEXT PIC X(n). | Two-part structure |
| INTEGER | PIC S9(9) COMP | 4-byte binary |
| SMALLINT | PIC S9(4) COMP | 2-byte binary |
| BIGINT | PIC S9(18) COMP | 8-byte binary |
| DECIMAL(p,s) | PIC S9(p-s)V9(s) COMP-3 | Packed decimal |
| DATE | PIC X(10) | 'YYYY-MM-DD' format |
| TIME | PIC X(8) | 'HH.MM.SS' format |
| TIMESTAMP | PIC X(26) | Full timestamp format |
💡 Key Insight: COMP-3 (packed decimal) maps naturally to DB2's DECIMAL type. This is a perfect match — both use packed decimal representation. When DB2 stores DECIMAL(11,2), use PIC S9(9)V99 COMP-3 in COBOL. The decimal points align automatically.
VARCHAR Host Variables
VARCHAR columns require a special two-part COBOL structure:
01 HV-DESCRIPTION.
49 HV-DESC-LEN PIC S9(4) COMP.
49 HV-DESC-TEXT PIC X(200).
The level-49 items are mandatory. DB2 sets HV-DESC-LEN to the actual length of the data returned, and HV-DESC-TEXT contains the data (padded with spaces to its declared length).
*> Inserting a VARCHAR value
MOVE 35 TO HV-DESC-LEN
MOVE "Routine monthly service charge"
TO HV-DESC-TEXT
EXEC SQL
INSERT INTO TRANSACTION_LOG
(TXN_ID, DESCRIPTION)
VALUES
(:HV-TXN-ID, :HV-DESCRIPTION)
END-EXEC
Null Indicator Variables
SQL supports NULL values — the absence of data. COBOL has no concept of NULL. The solution is indicator variables:
01 HV-PHONE-NUMBER PIC X(15).
01 HV-PHONE-IND PIC S9(4) COMP.
Use the indicator variable in SQL statements with the syntax :host-var:indicator-var:
EXEC SQL
SELECT PHONE_NUMBER
INTO :HV-PHONE-NUMBER:HV-PHONE-IND
FROM CUSTOMER
WHERE CUST_ID = :HV-CUST-ID
END-EXEC
*> Check if the value was NULL
IF HV-PHONE-IND < 0
DISPLAY "Phone number is NULL"
ELSE
DISPLAY "Phone: " HV-PHONE-NUMBER
END-IF
Indicator variable values: - 0 = column is not NULL, host variable contains the value - Negative = column is NULL, host variable content is undefined - Positive = column value was truncated to fit the host variable
⚠️ Critical Warning: If you SELECT a column that contains NULL into a host variable without an indicator variable, DB2 returns SQLCODE -305. This is one of the most common embedded SQL errors. Always use indicator variables for nullable columns, or use COALESCE in your SQL to provide a default.
*> Alternative: Use COALESCE to avoid NULL handling
EXEC SQL
SELECT COALESCE(PHONE_NUMBER, 'N/A')
INTO :HV-PHONE-NUMBER
FROM CUSTOMER
WHERE CUST_ID = :HV-CUST-ID
END-EXEC
27.4 DCLGEN — Generating Host Variable Declarations
Manually writing host variable declarations for every DB2 table is tedious and error-prone. DCLGEN (Declarations Generator) automates this process.
What DCLGEN Does
DCLGEN reads a DB2 table's catalog entry and generates: 1. A COBOL group-level data structure matching the table's columns 2. An SQL DECLARE TABLE statement documenting the table structure
Running DCLGEN
On z/OS, DCLGEN is typically run through ISPF (option 2.2) or JCL:
//DCLGEN EXEC PGM=DSNTIAD,PARM='...'
//STEPLIB DD DSN=DB2.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
DCLGEN TABLE(ACCOUNT_MASTER)
LIBRARY('STUDENT.COBOL.COPYLIB(ACCTDCL)')
ACTION(REPLACE)
LANGUAGE(COBOL)
STRUCTURE(ACCT-MASTER-ROW)
/*
Generated Output
DCLGEN produces a copybook like this:
*> DCLGEN TABLE(ACCOUNT_MASTER)
*> LIBRARY(STUDENT.COBOL.COPYLIB(ACCTDCL))
*> ACTION(REPLACE)
*> STRUCTURE(ACCT-MASTER-ROW)
*> ... IS THE DCLGEN COMMAND THAT MADE THE
*> FOLLOWING STATEMENTS
*>
EXEC SQL DECLARE ACCOUNT_MASTER TABLE
( ACCT_NUMBER CHAR(10) NOT NULL,
ACCT_HOLDER CHAR(40) NOT NULL,
ACCT_TYPE CHAR(2) NOT NULL,
ACCT_BALANCE DECIMAL(13,2) NOT NULL,
ACCT_STATUS CHAR(1) NOT NULL
WITH DEFAULT 'A',
OPEN_DATE DATE NOT NULL,
LAST_TXN_DATE DATE,
OVERDRAFT_LIMIT DECIMAL(9,2)
WITH DEFAULT 0,
INTEREST_RATE DECIMAL(5,4)
) END-EXEC.
*> COBOL DECLARATION FOR TABLE ACCOUNT_MASTER
01 ACCT-MASTER-ROW.
10 ACCT-NUMBER PIC X(10).
10 ACCT-HOLDER PIC X(40).
10 ACCT-TYPE PIC X(2).
10 ACCT-BALANCE PIC S9(11)V99 COMP-3.
10 ACCT-STATUS PIC X(1).
10 OPEN-DATE PIC X(10).
10 LAST-TXN-DATE PIC X(10).
10 OVERDRAFT-LIMIT PIC S9(7)V99 COMP-3.
10 INTEREST-RATE PIC S9(1)V9(4) COMP-3.
*> THE NUMBER OF COLUMNS DESCRIBED BY THIS
*> DECLARATION IS 9
Using DCLGEN Output
Include the DCLGEN copybook in your program:
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC
EXEC SQL
INCLUDE ACCTDCL
END-EXEC
01 WS-NULL-INDICATORS.
05 NI-LAST-TXN-DATE PIC S9(4) COMP.
05 NI-OVERDRAFT PIC S9(4) COMP.
05 NI-INTEREST-RATE PIC S9(4) COMP.
📊 Best Practice: Always use DCLGEN-generated copybooks rather than manually coding host variables. If the DBA changes a column type or size, regenerating the DCLGEN keeps your program synchronized with the database. Manual declarations silently go out of sync, leading to data corruption or runtime errors.
27.5 The SQLCA — SQL Communication Area
The SQLCA (SQL Communication Area) is the feedback mechanism between DB2 and your COBOL program. After every SQL statement, DB2 populates the SQLCA with information about what happened.
Including the SQLCA
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC
This expands to:
01 SQLCA.
05 SQLCAID PIC X(8) VALUE 'SQLCA '.
05 SQLCABC PIC S9(9) COMP VALUE 136.
05 SQLCODE PIC S9(9) COMP.
05 SQLERRM.
49 SQLERRML PIC S9(4) COMP.
49 SQLERRMC PIC X(70).
05 SQLERRP PIC X(8).
05 SQLERRD OCCURS 6 PIC S9(9) COMP.
05 SQLWARN.
10 SQLWARN0 PIC X.
10 SQLWARN1 PIC X.
10 SQLWARN2 PIC X.
10 SQLWARN3 PIC X.
10 SQLWARN4 PIC X.
10 SQLWARN5 PIC X.
10 SQLWARN6 PIC X.
10 SQLWARN7 PIC X.
10 SQLWARN8 PIC X.
10 SQLWARN9 PIC X.
10 SQLWARNA PIC X.
05 SQLSTATE PIC X(5).
SQLCODE — The Key Field
SQLCODE is the primary indicator of SQL statement success or failure:
| SQLCODE | Meaning | Action |
|---|---|---|
| 0 | Successful execution | Continue normally |
| +100 | No rows found (SELECT) or no more rows (FETCH) | Handle "not found" |
| < 0 | Error occurred | Handle error, possibly ROLLBACK |
| +1 to +99 | Warning (success with a caveat) | Check and log |
Essential SQLCODE Values
Every COBOL-DB2 programmer should know these SQLCODEs:
| SQLCODE | Meaning |
|---|---|
| 0 | Success |
| +100 | Row not found / end of cursor |
| -180 | Invalid date/time/timestamp value |
| -204 | Object not defined to DB2 |
| -305 | NULL value without indicator variable |
| -501 | Cursor not open |
| -803 | Duplicate key on INSERT |
| -805 | DBRM/package not found (bind issue) |
| -811 | SELECT returned more than one row |
| -818 | Timestamp mismatch (rebind needed) |
| -904 | Resource unavailable (tablespace locked) |
| -911 | Deadlock or timeout, rollback occurred |
| -922 | Authorization failure |
Checking SQLCODE After Every Statement
This is the most important defensive programming practice in embedded SQL:
EXEC SQL
SELECT ACCT_BALANCE
INTO :ACCT-BALANCE
FROM ACCOUNT_MASTER
WHERE ACCT_NUMBER = :HV-ACCT-NUM
END-EXEC
EVALUATE SQLCODE
WHEN 0
CONTINUE
WHEN +100
MOVE 'N' TO WS-ACCT-FOUND
DISPLAY "Account not found: "
HV-ACCT-NUM
WHEN -305
DISPLAY "NULL value error - "
"check indicator variables"
PERFORM ERROR-HANDLER
WHEN -811
DISPLAY "Multiple rows returned - "
"check primary key"
PERFORM ERROR-HANDLER
WHEN OTHER
DISPLAY "SQL Error: " SQLCODE
DISPLAY "SQLSTATE: " SQLSTATE
DISPLAY "Message: "
SQLERRMC(1:SQLERRML)
PERFORM ERROR-HANDLER
END-EVALUATE.
⚠️ Defensive Programming: Failing to check SQLCODE after every SQL statement is the embedded SQL equivalent of not checking file status after every I/O operation. The program will continue executing with incorrect or missing data, potentially corrupting downstream processing. Always check. No exceptions.
SQLERRD Array
The SQLERRD array provides additional information:
*> SQLERRD(3) — number of rows affected by
*> INSERT, UPDATE, or DELETE
EXEC SQL
UPDATE ACCOUNT_MASTER
SET ACCT_STATUS = 'C'
WHERE LAST_TXN_DATE < '2020-01-01'
END-EXEC
IF SQLCODE = 0
DISPLAY "Rows updated: " SQLERRD(3)
END-IF
| SQLERRD Element | Content |
|---|---|
| SQLERRD(1) | DBMS-specific diagnostic |
| SQLERRD(2) | DBMS-specific diagnostic |
| SQLERRD(3) | Number of rows affected |
| SQLERRD(4) | Estimated cost (PREPARE) |
| SQLERRD(5) | Position of error in SQL statement |
| SQLERRD(6) | DBMS-specific diagnostic |
27.6 SELECT INTO — Single-Row Queries
The simplest embedded SQL query retrieves a single row into host variables:
EXEC SQL
SELECT ACCT_HOLDER,
ACCT_BALANCE,
ACCT_STATUS,
OPEN_DATE
INTO :HV-ACCT-HOLDER,
:HV-BALANCE,
:HV-STATUS,
:HV-OPEN-DATE
FROM ACCOUNT_MASTER
WHERE ACCT_NUMBER = :HV-ACCT-NUM
END-EXEC
Rules for SELECT INTO
- The query must return exactly one row. If it returns zero rows, SQLCODE = +100. If it returns more than one row, SQLCODE = -811.
- The number of columns in the SELECT list must match the number of host variables in the INTO clause.
- Data types must be compatible (DB2 will convert where possible, but mismatches may cause errors or truncation).
Complete Single-Row Query Example
IDENTIFICATION DIVISION.
PROGRAM-ID. ACCT-INQUIRY.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL INCLUDE ACCTDCL END-EXEC.
01 WS-INPUT-ACCT PIC X(10).
01 WS-DISPLAY-BAL PIC $$$,$$$, MATH1 $,$$$,$$9.99-.
01 WS-ROW-COUNT PIC 9(7) VALUE ZEROS.
01 WS-TOTAL-BALANCE PIC S9(13)V99 VALUE ZEROS.
01 WS-DISPLAY-TOTAL PIC $$$,$$$,$$$,$$9.99-.
01 WS-REPORT-LINE PIC X(80).
*> Cursor declaration
EXEC SQL
DECLARE ACTIVE-ACCT-CURSOR CURSOR FOR
SELECT ACCT_NUMBER,
ACCT_HOLDER,
ACCT_BALANCE,
ACCT_STATUS,
OPEN_DATE
FROM ACCOUNT_MASTER
WHERE ACCT_STATUS = :HV-STATUS-FILTER
ORDER BY ACCT_HOLDER
END-EXEC.
PROCEDURE DIVISION.
MAIN-LOGIC.
PERFORM INITIALIZE-REPORT
PERFORM OPEN-CURSOR
PERFORM FETCH-AND-PROCESS
PERFORM CLOSE-CURSOR
PERFORM PRINT-TOTALS
STOP RUN.
INITIALIZE-REPORT.
MOVE 'A' TO HV-STATUS-FILTER
MOVE ZEROS TO WS-ROW-COUNT
MOVE ZEROS TO WS-TOTAL-BALANCE
DISPLAY "========================================"
"========================================"
DISPLAY "ACTIVE ACCOUNTS REPORT"
DISPLAY "========================================"
"========================================"
DISPLAY " "
STRING "Account "
"Holder Name "
"Balance "
"Opened "
DELIMITED SIZE INTO WS-REPORT-LINE
END-STRING
DISPLAY WS-REPORT-LINE
DISPLAY "----------"
"----------------------------------------"
"---------------"
"----------".
OPEN-CURSOR.
EXEC SQL
OPEN ACTIVE-ACCT-CURSOR
END-EXEC
IF SQLCODE NOT = 0
DISPLAY "Error opening cursor: " SQLCODE
PERFORM SQL-ERROR-HANDLER
END-IF.
FETCH-AND-PROCESS.
PERFORM UNTIL SQLCODE = +100
EXEC SQL
FETCH ACTIVE-ACCT-CURSOR
INTO :HV-ACCT-NUMBER,
:HV-ACCT-HOLDER,
:HV-BALANCE,
:HV-STATUS,
:HV-OPEN-DATE
END-EXEC
EVALUATE SQLCODE
WHEN 0
PERFORM PROCESS-ROW
WHEN +100
CONTINUE
WHEN OTHER
DISPLAY "Fetch error: " SQLCODE
PERFORM SQL-ERROR-HANDLER
END-EVALUATE
END-PERFORM.
PROCESS-ROW.
ADD 1 TO WS-ROW-COUNT
ADD HV-BALANCE TO WS-TOTAL-BALANCE
MOVE HV-BALANCE TO WS-DISPLAY-BAL
DISPLAY HV-ACCT-NUMBER " "
HV-ACCT-HOLDER " "
WS-DISPLAY-BAL " "
HV-OPEN-DATE.
CLOSE-CURSOR.
EXEC SQL
CLOSE ACTIVE-ACCT-CURSOR
END-EXEC.
PRINT-TOTALS.
MOVE WS-TOTAL-BALANCE TO WS-DISPLAY-TOTAL
DISPLAY " "
DISPLAY "========================================"
"========================================"
DISPLAY "Total accounts: " WS-ROW-COUNT
DISPLAY "Total balance: " WS-DISPLAY-TOTAL
DISPLAY "========================================"
"========================================".
SQL-ERROR-HANDLER.
DISPLAY "SQL ERROR: " SQLCODE
DISPLAY "SQLSTATE: " SQLSTATE
DISPLAY "Message: "
SQLERRMC(1:SQLERRML)
EXEC SQL
ROLLBACK
END-EXEC
EXEC SQL
CLOSE ACTIVE-ACCT-CURSOR
END-EXEC
STOP RUN.
Scrollable Cursors
Standard cursors move forward only. DB2 also supports scrollable cursors that can move backward:
EXEC SQL
DECLARE SCROLL-CURSOR SCROLL CURSOR FOR
SELECT ACCT_NUMBER, ACCT_BALANCE
FROM ACCOUNT_MASTER
ORDER BY ACCT_NUMBER
END-EXEC
*> Fetch options with scrollable cursors
EXEC SQL
FETCH NEXT SCROLL-CURSOR
INTO :HV-ACCT-NUM, :HV-BALANCE
END-EXEC
EXEC SQL
FETCH PRIOR SCROLL-CURSOR
INTO :HV-ACCT-NUM, :HV-BALANCE
END-EXEC
EXEC SQL
FETCH FIRST SCROLL-CURSOR
INTO :HV-ACCT-NUM, :HV-BALANCE
END-EXEC
EXEC SQL
FETCH LAST SCROLL-CURSOR
INTO :HV-ACCT-NUM, :HV-BALANCE
END-EXEC
EXEC SQL
FETCH ABSOLUTE 50 SCROLL-CURSOR
INTO :HV-ACCT-NUM, :HV-BALANCE
END-EXEC
⚠️ Performance Note: Scrollable cursors require DB2 to materialize the entire result set, consuming more memory than forward-only cursors. Use them only when you genuinely need bidirectional navigation.
27.8 INSERT, UPDATE, DELETE with Host Variables
Embedded SQL supports all DML (Data Manipulation Language) operations.
INSERT
INSERT-ACCOUNT.
MOVE "1000000099" TO ACCT-NUMBER
MOVE "Derek Washington" TO ACCT-HOLDER
MOVE "CK" TO ACCT-TYPE
MOVE 2500.00 TO ACCT-BALANCE
MOVE "A" TO ACCT-STATUS
MOVE "2025-03-15" TO OPEN-DATE
EXEC SQL
INSERT INTO ACCOUNT_MASTER
(ACCT_NUMBER, ACCT_HOLDER, ACCT_TYPE,
ACCT_BALANCE, ACCT_STATUS, OPEN_DATE)
VALUES
(:ACCT-NUMBER, :ACCT-HOLDER, :ACCT-TYPE,
:ACCT-BALANCE, :ACCT-STATUS, :OPEN-DATE)
END-EXEC
EVALUATE SQLCODE
WHEN 0
DISPLAY "Account created successfully"
WHEN -803
DISPLAY "Duplicate account number"
WHEN OTHER
DISPLAY "Insert error: " SQLCODE
PERFORM SQL-ERROR-HANDLER
END-EVALUATE.
UPDATE
UPDATE-BALANCE.
EXEC SQL
UPDATE ACCOUNT_MASTER
SET ACCT_BALANCE = ACCT_BALANCE
+ :HV-TXN-AMOUNT,
LAST_TXN_DATE = CURRENT DATE
WHERE ACCT_NUMBER = :HV-ACCT-NUMBER
AND ACCT_STATUS = 'A'
END-EXEC
EVALUATE SQLCODE
WHEN 0
IF SQLERRD(3) = 1
DISPLAY "Balance updated"
ELSE
DISPLAY "WARNING: " SQLERRD(3)
" rows updated"
END-IF
WHEN +100
DISPLAY "Account not found or inactive"
WHEN OTHER
DISPLAY "Update error: " SQLCODE
PERFORM SQL-ERROR-HANDLER
END-EVALUATE.
DELETE
PURGE-CLOSED-ACCOUNTS.
EXEC SQL
DELETE FROM ACCOUNT_MASTER
WHERE ACCT_STATUS = 'C'
AND LAST_TXN_DATE < :HV-CUTOFF-DATE
END-EXEC
EVALUATE SQLCODE
WHEN 0
DISPLAY "Purged " SQLERRD(3) " accounts"
WHEN +100
DISPLAY "No accounts to purge"
WHEN OTHER
DISPLAY "Delete error: " SQLCODE
PERFORM SQL-ERROR-HANDLER
END-EVALUATE.
Positioned UPDATE and DELETE
With cursors, you can update or delete the current row using WHERE CURRENT OF:
EXEC SQL
DECLARE UPDATE-CURSOR CURSOR FOR
SELECT ACCT_NUMBER,
ACCT_BALANCE
FROM ACCOUNT_MASTER
WHERE ACCT_TYPE = 'SV'
FOR UPDATE OF ACCT_BALANCE
END-EXEC
*> Inside the fetch loop:
COMPUTE HV-NEW-BALANCE =
HV-BALANCE * (1 + HV-INTEREST-RATE / 12)
EXEC SQL
UPDATE ACCOUNT_MASTER
SET ACCT_BALANCE = :HV-NEW-BALANCE,
LAST_TXN_DATE = CURRENT DATE
WHERE CURRENT OF UPDATE-CURSOR
END-EXEC
💡 Key Insight: FOR UPDATE OF in the cursor declaration tells DB2 to acquire update locks as rows are fetched, preventing other transactions from modifying the same rows. Without this clause, another transaction could change a row between your FETCH and your UPDATE, leading to a lost update anomaly.
27.9 The WHENEVER Statement — Automatic Error Handling
The WHENEVER statement provides automatic branching based on SQL outcomes, eliminating the need to check SQLCODE after every statement:
*> Branch to error handler on any SQL error
EXEC SQL
WHENEVER SQLERROR GO TO SQL-ERROR-EXIT
END-EXEC
*> Branch to end-of-data handler when no rows found
EXEC SQL
WHENEVER NOT FOUND GO TO NO-MORE-ROWS
END-EXEC
*> Display warning messages but continue
EXEC SQL
WHENEVER SQLWARNING CONTINUE
END-EXEC
WHENEVER Conditions
| Condition | Triggers When |
|---|---|
| SQLERROR | SQLCODE < 0 (any error) |
| NOT FOUND | SQLCODE = +100 |
| SQLWARNING | SQLWARN0 = 'W' (any warning) |
WHENEVER Actions
| Action | Effect |
|---|---|
| GO TO paragraph | Branch to the named paragraph |
| CONTINUE | Take no automatic action (check manually) |
Important WHENEVER Behavior
⚠️ Critical Warning: WHENEVER is a precompiler directive, not a runtime statement. It applies to all subsequent SQL statements in the source code until another WHENEVER for the same condition is encountered. This is lexical scoping, not dynamic scoping.
*> WHENEVER applies from here forward
EXEC SQL
WHENEVER SQLERROR GO TO ERROR-EXIT
END-EXEC
*> This SELECT will GO TO ERROR-EXIT on error
EXEC SQL
SELECT ... INTO ...
END-EXEC
*> Turn off automatic error handling
EXEC SQL
WHENEVER SQLERROR CONTINUE
END-EXEC
*> This SELECT requires manual SQLCODE checking
EXEC SQL
SELECT ... INTO ...
END-EXEC
Recommended Pattern
Many experienced COBOL-DB2 programmers prefer manual SQLCODE checking over WHENEVER, because: 1. WHENEVER's lexical scoping can be confusing in large programs 2. Different SQL statements may need different error handling 3. Manual checking is more explicit and self-documenting
The recommended pattern is to use WHENEVER sparingly and check SQLCODE explicitly:
*> Use WHENEVER only as a safety net
EXEC SQL
WHENEVER SQLERROR GO TO EMERGENCY-EXIT
END-EXEC
*> But still check SQLCODE explicitly
EXEC SQL
SELECT ... INTO ...
END-EXEC
PERFORM CHECK-SQL-RESULT
*> The WHENEVER catches any SQL error you
*> forget to check — a defensive programming
*> belt-and-suspenders approach
27.10 The Precompiler Process
Understanding the precompiler process is essential for debugging embedded SQL programs and understanding error messages.
How It Works
┌─────────────┐
COBOL + SQL source ─────────► │ DB2 │ ──► Modified COBOL source
(ACCT-INQUIRY.cbl) │ Precompiler │ (all EXEC SQL replaced
│ (DSNHPC) │ with CALL statements)
└──────┬──────┘
│
▼
┌─────────────┐
│ DBRM │ Database Request Module
│ (ACCT-INQ) │ (SQL statements extracted)
└──────┬──────┘
│
▼
┌─────────────┐
│ DB2 BIND │ Creates a PACKAGE
│ (BIND PLAN │ with access paths
│ or PACKAGE)│
└─────────────┘
Step 1: Precompilation
The precompiler (DSNHPC on z/OS) reads your COBOL+SQL source and: 1. Extracts all EXEC SQL statements 2. Replaces them with COBOL CALL statements to DB2 runtime modules 3. Generates a DBRM (Database Request Module) containing the extracted SQL
Your EXEC SQL becomes something like:
*> What you wrote:
EXEC SQL
SELECT ACCT_BALANCE
INTO :HV-BALANCE
FROM ACCOUNT_MASTER
WHERE ACCT_NUMBER = :HV-ACCT-NUM
END-EXEC
*> What the precompiler generates:
MOVE +320 TO SQLTSIZE OF SQLCA
CALL 'DSNHLI'
USING SQLCA
SQL-STMT-0042
HV-BALANCE
HV-ACCT-NUM
Step 2: COBOL Compilation
The modified COBOL source (with CALL statements instead of EXEC SQL) is compiled by the normal COBOL compiler, producing an object module.
Step 3: Bind
The DBRM is processed by DB2 BIND, which: 1. Validates all SQL statements against the DB2 catalog 2. Determines optimal access paths (which indexes to use, join strategies, etc.) 3. Creates a PACKAGE or PLAN stored in DB2
//BIND EXEC PGM=IKJEFT01
//SYSTSIN DD *
DSN SYSTEM(DB2P)
BIND PACKAGE(COLL1) -
MEMBER(ACCTINQ) -
ACTION(REPLACE) -
ISOLATION(CS) -
VALIDATE(BIND)
END
/*
Step 4: Link and Run
The compiled object module is linked with the DB2 interface module (DSNELI) to produce the executable load module.
📊 Why This Matters: Understanding the precompiler process explains several common issues: - SQLCODE -805 (DBRM not found): The program was compiled but not bound to DB2 - SQLCODE -818 (timestamp mismatch): The program was recompiled but not rebound — the timestamps in the load module and the DB2 package don't match - Precompiler errors: SQL syntax errors are reported by the precompiler, not the COBOL compiler, and have different error numbers
27.11 Transaction Control — COMMIT and ROLLBACK
Embedded SQL programs control transactions with COMMIT and ROLLBACK:
*> Make all changes permanent
EXEC SQL
COMMIT
END-EXEC
*> Undo all changes since last COMMIT
EXEC SQL
ROLLBACK
END-EXEC
Transaction Design
PROCESS-TRANSFER.
*> Transfer funds between two accounts
*> This must be atomic — both updates or neither
*> Step 1: Debit source account
EXEC SQL
UPDATE ACCOUNT_MASTER
SET ACCT_BALANCE = ACCT_BALANCE
- :HV-TRANSFER-AMT
WHERE ACCT_NUMBER = :HV-SOURCE-ACCT
AND ACCT_BALANCE >= :HV-TRANSFER-AMT
END-EXEC
IF SQLCODE NOT = 0 OR SQLERRD(3) NOT = 1
EXEC SQL ROLLBACK END-EXEC
MOVE 'DEBIT-FAIL' TO WS-ERROR-CODE
GOBACK
END-IF
*> Step 2: Credit target account
EXEC SQL
UPDATE ACCOUNT_MASTER
SET ACCT_BALANCE = ACCT_BALANCE
+ :HV-TRANSFER-AMT
WHERE ACCT_NUMBER = :HV-TARGET-ACCT
AND ACCT_STATUS = 'A'
END-EXEC
IF SQLCODE NOT = 0 OR SQLERRD(3) NOT = 1
EXEC SQL ROLLBACK END-EXEC
MOVE 'CREDIT-FAIL' TO WS-ERROR-CODE
GOBACK
END-IF
*> Step 3: Log the transfer
EXEC SQL
INSERT INTO TRANSACTION_LOG
(TXN_ID, TXN_DATE, TXN_TYPE,
SOURCE_ACCT, TARGET_ACCT,
TXN_AMOUNT, TXN_STATUS)
VALUES
(NEXT VALUE FOR TXN_SEQ,
CURRENT TIMESTAMP,
'TR',
:HV-SOURCE-ACCT,
:HV-TARGET-ACCT,
:HV-TRANSFER-AMT,
'C')
END-EXEC
IF SQLCODE NOT = 0
EXEC SQL ROLLBACK END-EXEC
MOVE 'LOG-FAIL' TO WS-ERROR-CODE
GOBACK
END-IF
*> All three operations succeeded — commit
EXEC SQL COMMIT END-EXEC
MOVE 'SUCCESS' TO WS-ERROR-CODE.
💡 Key Insight: The COMMIT/ROLLBACK transaction boundary is the guarantee of data integrity. In the transfer example, if the credit UPDATE fails, the ROLLBACK undoes the debit UPDATE. Without this, money would disappear from the source account without appearing in the target account. This is the "A" (Atomicity) in ACID.
27.12 GlobalBank Case Study: Account Queries and Transaction History
At GlobalBank, Maria Chen's team uses embedded SQL extensively. Here is a realistic program that Derek Washington wrote for the account inquiry system:
IDENTIFICATION DIVISION.
PROGRAM-ID. GB-ACCT-INQ.
*> GlobalBank Account Inquiry
*> Retrieves account details and recent transactions
*> Called from CICS transaction AINQ
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL INCLUDE ACCTDCL END-EXEC.
01 HV-ACCT-NUMBER PIC X(10).
01 HV-TXN-DATE PIC X(10).
01 HV-TXN-TYPE PIC X(2).
01 HV-TXN-AMOUNT PIC S9(9)V99 COMP-3.
01 HV-TXN-DESC.
49 HV-TXN-DESC-LEN PIC S9(4) COMP.
49 HV-TXN-DESC-TEXT PIC X(100).
01 HV-TXN-BALANCE PIC S9(11)V99 COMP-3.
01 WS-ROW-COUNT PIC 9(5) VALUE ZEROS.
01 WS-DISPLAY-AMT PIC -(9)9.99.
01 WS-DISPLAY-BAL PIC $$$,$$$, MATH6 $,$$$,$$9.99.
01 WS-DISPLAY-ALLOWED PIC $$$,$$$, MATH9 $,$$$,$$9.99.
01 WS-STATUS-DESC PIC X(20).
01 WS-FOUND-FLAG PIC X VALUE 'N'.
PROCEDURE DIVISION.
MAIN-LOGIC.
DISPLAY "Enter claim ID: "
ACCEPT HV-CLAIM-ID
PERFORM QUERY-CLAIM
IF WS-FOUND-FLAG = 'Y'
PERFORM QUERY-PROVIDER
PERFORM DISPLAY-CLAIM-DETAILS
ELSE
DISPLAY "Claim not found: " HV-CLAIM-ID
END-IF
STOP RUN.
QUERY-CLAIM.
EXEC SQL
SELECT CLM.MEMBER_ID,
CLM.PROVIDER_ID,
CLM.SERVICE_DATE,
CLM.DIAGNOSIS_CODE,
CLM.PROCEDURE_CODE,
CLM.BILLED_AMOUNT,
CLM.ALLOWED_AMOUNT,
CLM.PAID_AMOUNT,
CLM.CLAIM_STATUS,
CLM.DENIAL_REASON
INTO :HV-MEMBER-ID,
:HV-PROVIDER-ID,
:HV-SERVICE-DATE,
:HV-DIAG-CODE,
:HV-PROC-CODE,
:HV-BILLED-AMT,
:HV-ALLOWED-AMT,
:HV-PAID-AMT,
:HV-CLAIM-STATUS,
:HV-DENIAL-REASON:NI-DENIAL-REASON
FROM CLAIM_MASTER CLM
WHERE CLM.CLAIM_ID = :HV-CLAIM-ID
END-EXEC
EVALUATE SQLCODE
WHEN 0
MOVE 'Y' TO WS-FOUND-FLAG
WHEN +100
MOVE 'N' TO WS-FOUND-FLAG
WHEN OTHER
DISPLAY "Claim query error: " SQLCODE
DISPLAY "SQLSTATE: " SQLSTATE
PERFORM SQL-ERROR-EXIT
END-EVALUATE.
QUERY-PROVIDER.
EXEC SQL
SELECT PROVIDER_NAME,
SPECIALTY,
NPI_NUMBER
INTO :HV-PROVIDER-NAME,
:HV-PROVIDER-SPEC,
:HV-PROVIDER-NPI
FROM PROVIDER_MASTER
WHERE PROVIDER_ID = :HV-PROVIDER-ID
END-EXEC
IF SQLCODE NOT = 0 AND SQLCODE NOT = +100
DISPLAY "Provider query warning: "
SQLCODE
MOVE "(Provider not found)" TO
HV-PROVIDER-NAME
MOVE SPACES TO HV-PROVIDER-SPEC
MOVE SPACES TO HV-PROVIDER-NPI
END-IF.
DISPLAY-CLAIM-DETAILS.
MOVE HV-BILLED-AMT TO WS-DISPLAY-BILLED
MOVE HV-ALLOWED-AMT TO WS-DISPLAY-ALLOWED
MOVE HV-PAID-AMT TO WS-DISPLAY-PAID
PERFORM GET-STATUS-DESCRIPTION
DISPLAY "=================================="
DISPLAY "MEDCLAIM - CLAIM STATUS INQUIRY"
DISPLAY "=================================="
DISPLAY "Claim ID: " HV-CLAIM-ID
DISPLAY "Member ID: " HV-MEMBER-ID
DISPLAY "Service Date: " HV-SERVICE-DATE
DISPLAY "Diagnosis: " HV-DIAG-CODE
DISPLAY "Procedure: " HV-PROC-CODE
DISPLAY " "
DISPLAY "PROVIDER INFORMATION"
DISPLAY " Name: " HV-PROVIDER-NAME
DISPLAY " Specialty: " HV-PROVIDER-SPEC
DISPLAY " NPI: " HV-PROVIDER-NPI
DISPLAY " "
DISPLAY "FINANCIAL DETAILS"
DISPLAY " Billed: " WS-DISPLAY-BILLED
DISPLAY " Allowed: " WS-DISPLAY-ALLOWED
DISPLAY " Paid: " WS-DISPLAY-PAID
DISPLAY " "
DISPLAY "STATUS"
DISPLAY " Status: " HV-CLAIM-STATUS
" - " WS-STATUS-DESC
IF NI-DENIAL-REASON >= 0
DISPLAY " Denial: " HV-DENIAL-REASON
END-IF
DISPLAY "==================================".
GET-STATUS-DESCRIPTION.
EVALUATE HV-CLAIM-STATUS
WHEN 'NW' MOVE "New" TO WS-STATUS-DESC
WHEN 'IR' MOVE "In Review" TO WS-STATUS-DESC
WHEN 'AP' MOVE "Approved" TO WS-STATUS-DESC
WHEN 'DN' MOVE "Denied" TO WS-STATUS-DESC
WHEN 'PN' MOVE "Pending" TO WS-STATUS-DESC
WHEN 'PD' MOVE "Paid" TO WS-STATUS-DESC
WHEN OTHER MOVE "Unknown" TO WS-STATUS-DESC
END-EVALUATE.
SQL-ERROR-EXIT.
DISPLAY "FATAL SQL ERROR"
DISPLAY "SQLCODE: " SQLCODE
DISPLAY "SQLSTATE: " SQLSTATE
EXEC SQL ROLLBACK END-EXEC
STOP RUN.
Tomás Rivera noted an important design decision: "We use a separate QUERY-PROVIDER paragraph rather than a JOIN in the claim query. Why? Because the provider lookup is optional — if the provider record is missing (it happens with out-of-network claims), we still want to display the claim. A JOIN would return no rows if the provider is missing. Separate queries give us more control."
27.14 Common Embedded SQL Patterns
Pattern 1: Existence Check
CHECK-ACCOUNT-EXISTS.
EXEC SQL
SELECT 1
INTO :HV-DUMMY
FROM SYSIBM.SYSDUMMY1
WHERE EXISTS
(SELECT 1 FROM ACCOUNT_MASTER
WHERE ACCT_NUMBER = :HV-ACCT-NUM)
END-EXEC
IF SQLCODE = 0
MOVE 'Y' TO WS-EXISTS
ELSE
MOVE 'N' TO WS-EXISTS
END-IF.
Pattern 2: Count with Threshold
CHECK-DAILY-LIMIT.
EXEC SQL
SELECT COUNT(*)
INTO :HV-TXN-COUNT
FROM TRANSACTION_HISTORY
WHERE ACCT_NUMBER = :HV-ACCT-NUM
AND TXN_DATE = CURRENT DATE
AND TXN_TYPE IN ('WD', 'TR')
END-EXEC
IF SQLCODE = 0
AND HV-TXN-COUNT >= 10
MOVE 'Y' TO WS-LIMIT-REACHED
END-IF.
Pattern 3: Upsert (INSERT or UPDATE)
UPSERT-BALANCE.
*> Try UPDATE first
EXEC SQL
UPDATE DAILY_BALANCE
SET CLOSING_BALANCE = :HV-BALANCE,
LAST_UPDATED = CURRENT TIMESTAMP
WHERE ACCT_NUMBER = :HV-ACCT-NUM
AND BALANCE_DATE = CURRENT DATE
END-EXEC
IF SQLCODE = +100 OR SQLERRD(3) = 0
*> Row doesn't exist — INSERT
EXEC SQL
INSERT INTO DAILY_BALANCE
(ACCT_NUMBER, BALANCE_DATE,
CLOSING_BALANCE, LAST_UPDATED)
VALUES
(:HV-ACCT-NUM, CURRENT DATE,
:HV-BALANCE, CURRENT TIMESTAMP)
END-EXEC
END-IF.
Pattern 4: Batch COMMIT
PROCESS-BATCH.
MOVE ZEROS TO WS-COMMIT-CTR
PERFORM UNTIL WS-EOF = 'Y'
READ INPUT-FILE INTO WS-INPUT-REC
AT END SET WS-EOF-TRUE TO TRUE
END-READ
IF WS-EOF = 'N'
PERFORM PROCESS-RECORD
ADD 1 TO WS-COMMIT-CTR
IF WS-COMMIT-CTR >= 500
EXEC SQL COMMIT END-EXEC
MOVE ZEROS TO WS-COMMIT-CTR
DISPLAY "Committed at record: "
WS-TOTAL-COUNT
END-IF
END-IF
END-PERFORM
*> Final commit for remaining records
IF WS-COMMIT-CTR > 0
EXEC SQL COMMIT END-EXEC
END-IF.
💡 Key Insight: Committing every 500 records (or every N records) is a standard batch pattern. Committing after every single record is safe but slow (each COMMIT forces a log write). Committing only at the end is fast but risky — if the program ABENDs after processing 100,000 records without a COMMIT, DB2 rolls back all 100,000 changes.
27.15 The INCLUDE Statement
The EXEC SQL INCLUDE statement is the embedded SQL equivalent of COBOL's COPY statement. It includes SQL-related copybooks:
WORKING-STORAGE SECTION.
*> Include the SQLCA
EXEC SQL INCLUDE SQLCA END-EXEC.
*> Include DCLGEN-generated table declarations
EXEC SQL INCLUDE ACCTDCL END-EXEC.
EXEC SQL INCLUDE TXNDCL END-EXEC.
EXEC SQL INCLUDE PROVDCL END-EXEC.
INCLUDE vs. COPY
| Feature | EXEC SQL INCLUDE | COPY |
|---|---|---|
| Processed by | DB2 precompiler | COBOL compiler |
| Contains | SQL declarations, host variables | Any COBOL code |
| Typical use | SQLCA, DCLGEN output | Record layouts, common paragraphs |
Use EXEC SQL INCLUDE for SQL-related copybooks (SQLCA, DCLGEN). Use COPY for everything else. Some shops standardize on EXEC SQL INCLUDE for all copybooks used in DB2 programs, because the precompiler processes them before the COBOL compiler, ensuring that host variables are visible to the precompiler.
27.16 🧪 Try It Yourself: Student Lab Exercises
Exercise 1: Simple Query Program
Using your Student Mainframe Lab (z/OS, Hercules, or GnuCOBOL with SQLite/PostgreSQL), write a program that:
- Accepts a student ID from the terminal
- Queries a STUDENT_MASTER table
- Displays the student's name, major, and GPA
- Handles the "not found" case gracefully
Exercise 2: Cursor-Based Report
Write a program that:
- Declares a cursor to SELECT all students with GPA > 3.5
- Fetches each row and displays it in a formatted report
- Counts total rows and displays the count
- Properly opens and closes the cursor
Exercise 3: Transaction Program
Write a program that:
- Reads a file of grade updates (student ID, course, new grade)
- For each record, UPDATEs the ENROLLMENT table
- COMMITs every 100 records
- Handles errors by ROLLBACKing the current batch and writing the failed record to an error file
📊 GnuCOBOL Note: If you are using GnuCOBOL, you can use OCESQL (Open COBOL ESQL) preprocessor to work with PostgreSQL or MySQL. The syntax is similar to DB2 embedded SQL, with minor differences. See Appendix E for GnuCOBOL-specific setup instructions.
27.17 Advanced SQLCA Analysis
Beyond SQLCODE, the SQLCA contains information that experienced COBOL-DB2 programmers use for diagnostics and performance monitoring.
SQLWARN Flags
The SQLWARN array contains single-character warning flags:
*> Check if any warning was raised
IF SQLWARN0 = 'W'
DISPLAY "SQL Warning detected"
*> SQLWARN1 = 'W': String truncation occurred
IF SQLWARN1 = 'W'
DISPLAY "Warning: Data truncated"
END-IF
*> SQLWARN2 = 'W': NULL values eliminated from function
IF SQLWARN2 = 'W'
DISPLAY "Warning: NULLs in aggregate"
END-IF
*> SQLWARN3 = 'W': Number of columns != number of host vars
IF SQLWARN3 = 'W'
DISPLAY "Warning: Column count mismatch"
END-IF
*> SQLWARN4 = 'W': UPDATE or DELETE without WHERE
IF SQLWARN4 = 'W'
DISPLAY "WARNING: No WHERE clause!"
END-IF
*> SQLWARN5 = 'W': SQL statement not valid for this env
IF SQLWARN5 = 'W'
DISPLAY "Warning: Environment mismatch"
END-IF
*> SQLWARN6 = 'W': Date arithmetic overflow
IF SQLWARN6 = 'W'
DISPLAY "Warning: Date overflow"
END-IF
END-IF
SQLWARN4 deserves special attention. An UPDATE or DELETE without a WHERE clause affects every row in the table. DB2 raises SQLWARN4 to alert you. In a production system, this should trigger an immediate abort:
EXEC SQL
UPDATE ACCOUNT_MASTER
SET ACCT_STATUS = :HV-NEW-STATUS
END-EXEC
*> This UPDATE has no WHERE clause!
IF SQLWARN4 = 'W'
DISPLAY "CRITICAL: UPDATE without WHERE!"
DISPLAY "All rows in table were updated!"
EXEC SQL ROLLBACK END-EXEC
PERFORM EMERGENCY-ABORT
END-IF
SQLSTATE vs. SQLCODE
SQLSTATE provides a standardized five-character error code that is portable across database vendors. SQLCODE is DB2-specific.
*> SQLSTATE format: CCNNN
*> CC = class code (00=success, 01=warning, 02=not found)
*> NNN = subclass code
EVALUATE SQLSTATE(1:2)
WHEN '00'
CONTINUE
WHEN '01'
DISPLAY "Warning: " SQLSTATE
WHEN '02'
DISPLAY "Not found"
WHEN '23'
DISPLAY "Constraint violation"
WHEN '40'
DISPLAY "Transaction rollback"
WHEN '42'
DISPLAY "Authorization/syntax error"
WHEN OTHER
DISPLAY "Error class: "
SQLSTATE(1:2)
END-EVALUATE
For portable code (code that might run against DB2, Oracle, or PostgreSQL), use SQLSTATE. For DB2-specific code, SQLCODE provides more detailed information.
27.18 Working with Date and Time in Embedded SQL
Date and time handling in embedded SQL is a frequent source of confusion because DB2's date/time formats differ from COBOL's traditional date handling.
DB2 Date Formats
DB2 stores DATE, TIME, and TIMESTAMP values internally in an optimized binary format. When transferred to COBOL host variables, they are converted to character strings:
01 HV-DATE PIC X(10).
*> Format: 'YYYY-MM-DD' (ISO format by default)
01 HV-TIME PIC X(8).
*> Format: 'HH.MM.SS'
01 HV-TIMESTAMP PIC X(26).
*> Format: 'YYYY-MM-DD-HH.MM.SS.NNNNNN'
Using Date Functions in SQL
*> Current date
EXEC SQL
SELECT CURRENT DATE
INTO :HV-TODAY
FROM SYSIBM.SYSDUMMY1
END-EXEC
*> Date arithmetic
EXEC SQL
SELECT ACCT_NUMBER, ACCT_HOLDER
INTO :HV-ACCT-NUMBER, :HV-ACCT-HOLDER
FROM ACCOUNT_MASTER
WHERE OPEN_DATE < CURRENT DATE - 365 DAYS
AND ACCT_STATUS = 'A'
END-EXEC
*> Extracting date components
EXEC SQL
SELECT YEAR(OPEN_DATE),
MONTH(OPEN_DATE),
DAY(OPEN_DATE)
INTO :HV-YEAR, :HV-MONTH, :HV-DAY
FROM ACCOUNT_MASTER
WHERE ACCT_NUMBER = :HV-ACCT-NUM
END-EXEC
Converting Between COBOL and DB2 Date Formats
COBOL traditionally uses PIC 9(8) for dates (YYYYMMDD). DB2 uses 'YYYY-MM-DD'. Converting between them:
COBOL-DATE-TO-DB2.
*> Convert 20250315 to '2025-03-15'
STRING WS-COBOL-DATE(1:4) DELIMITED SIZE
'-' DELIMITED SIZE
WS-COBOL-DATE(5:2) DELIMITED SIZE
'-' DELIMITED SIZE
WS-COBOL-DATE(7:2) DELIMITED SIZE
INTO HV-DB2-DATE
END-STRING.
DB2-DATE-TO-COBOL.
*> Convert '2025-03-15' to 20250315
STRING HV-DB2-DATE(1:4) DELIMITED SIZE
HV-DB2-DATE(6:2) DELIMITED SIZE
HV-DB2-DATE(9:2) DELIMITED SIZE
INTO WS-COBOL-DATE
END-STRING.
A cleaner approach uses UNSTRING:
PARSE-DB2-DATE.
UNSTRING HV-DB2-DATE DELIMITED '-'
INTO WS-YEAR-PART
WS-MONTH-PART
WS-DAY-PART
END-UNSTRING
STRING WS-YEAR-PART DELIMITED SPACES
WS-MONTH-PART DELIMITED SPACES
WS-DAY-PART DELIMITED SPACES
INTO WS-COBOL-DATE
END-STRING.
27.19 Embedded SQL in Batch Programs — A Complete Pattern
Most production embedded SQL programs are batch programs that process files against DB2 tables. Here is a complete pattern that incorporates all the best practices from this chapter:
IDENTIFICATION DIVISION.
PROGRAM-ID. BATCH-ACCT-UPDATE.
*> Batch account update program
*> Reads updates from sequential file
*> Applies to DB2 ACCOUNT_MASTER table
*> Commits every 500 records
*> Writes errors to error file
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT UPDATE-FILE ASSIGN TO UPDTFILE
FILE STATUS IS WS-UPDATE-STATUS.
SELECT ERROR-FILE ASSIGN TO ERRFILE
FILE STATUS IS WS-ERROR-STATUS.
DATA DIVISION.
FILE SECTION.
FD UPDATE-FILE.
01 UPDATE-RECORD.
05 UR-ACCT-NUMBER PIC X(10).
05 UR-ACTION PIC X(1).
88 UR-UPDATE VALUE 'U'.
88 UR-INSERT VALUE 'I'.
88 UR-DELETE VALUE 'D'.
05 UR-ACCT-HOLDER PIC X(40).
05 UR-BALANCE PIC S9(11)V99.
05 UR-STATUS PIC X(1).
FD ERROR-FILE.
01 ERROR-RECORD.
05 ER-ACCT-NUMBER PIC X(10).
05 ER-ACTION PIC X(1).
05 ER-SQLCODE PIC S9(9) SIGN LEADING.
05 ER-MESSAGE PIC X(60).
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
01 HV-ACCT-NUMBER PIC X(10).
01 HV-ACCT-HOLDER PIC X(40).
01 HV-BALANCE PIC S9(11)V99 COMP-3.
01 HV-STATUS PIC X(1).
01 WS-UPDATE-STATUS PIC XX.
01 WS-ERROR-STATUS PIC XX.
01 WS-EOF PIC X VALUE 'N'.
88 END-OF-FILE VALUE 'Y'.
01 WS-RECORDS-READ PIC 9(7) VALUE ZEROS.
01 WS-RECORDS-OK PIC 9(7) VALUE ZEROS.
01 WS-RECORDS-ERROR PIC 9(7) VALUE ZEROS.
01 WS-COMMIT-COUNT PIC 9(4) VALUE ZEROS.
01 WS-COMMIT-INTERVAL PIC 9(4) VALUE 500.
PROCEDURE DIVISION.
MAIN-LOGIC.
PERFORM OPEN-FILES
PERFORM PROCESS-UPDATES
UNTIL END-OF-FILE
PERFORM FINAL-COMMIT
PERFORM CLOSE-FILES
PERFORM DISPLAY-STATS
STOP RUN.
OPEN-FILES.
OPEN INPUT UPDATE-FILE
IF WS-UPDATE-STATUS NOT = '00'
DISPLAY "Error opening update file: "
WS-UPDATE-STATUS
STOP RUN
END-IF
OPEN OUTPUT ERROR-FILE
IF WS-ERROR-STATUS NOT = '00'
DISPLAY "Error opening error file: "
WS-ERROR-STATUS
STOP RUN
END-IF.
PROCESS-UPDATES.
READ UPDATE-FILE
AT END SET END-OF-FILE TO TRUE
END-READ
IF NOT END-OF-FILE
ADD 1 TO WS-RECORDS-READ
PERFORM APPLY-UPDATE
PERFORM CHECK-COMMIT
END-IF.
APPLY-UPDATE.
MOVE UR-ACCT-NUMBER TO HV-ACCT-NUMBER
MOVE UR-ACCT-HOLDER TO HV-ACCT-HOLDER
MOVE UR-BALANCE TO HV-BALANCE
MOVE UR-STATUS TO HV-STATUS
EVALUATE TRUE
WHEN UR-UPDATE
PERFORM DO-UPDATE
WHEN UR-INSERT
PERFORM DO-INSERT
WHEN UR-DELETE
PERFORM DO-DELETE
WHEN OTHER
PERFORM WRITE-ERROR-RECORD
END-EVALUATE.
DO-UPDATE.
EXEC SQL
UPDATE ACCOUNT_MASTER
SET ACCT_HOLDER = :HV-ACCT-HOLDER,
ACCT_BALANCE = :HV-BALANCE,
ACCT_STATUS = :HV-STATUS,
LAST_TXN_DATE = CURRENT DATE
WHERE ACCT_NUMBER = :HV-ACCT-NUMBER
END-EXEC
IF SQLCODE = 0 AND SQLERRD(3) = 1
ADD 1 TO WS-RECORDS-OK
ADD 1 TO WS-COMMIT-COUNT
ELSE
PERFORM WRITE-ERROR-RECORD
END-IF.
DO-INSERT.
EXEC SQL
INSERT INTO ACCOUNT_MASTER
(ACCT_NUMBER, ACCT_HOLDER,
ACCT_BALANCE, ACCT_STATUS,
OPEN_DATE)
VALUES
(:HV-ACCT-NUMBER, :HV-ACCT-HOLDER,
:HV-BALANCE, :HV-STATUS,
CURRENT DATE)
END-EXEC
IF SQLCODE = 0
ADD 1 TO WS-RECORDS-OK
ADD 1 TO WS-COMMIT-COUNT
ELSE
PERFORM WRITE-ERROR-RECORD
END-IF.
DO-DELETE.
EXEC SQL
DELETE FROM ACCOUNT_MASTER
WHERE ACCT_NUMBER = :HV-ACCT-NUMBER
END-EXEC
IF SQLCODE = 0 AND SQLERRD(3) = 1
ADD 1 TO WS-RECORDS-OK
ADD 1 TO WS-COMMIT-COUNT
ELSE
PERFORM WRITE-ERROR-RECORD
END-IF.
CHECK-COMMIT.
IF WS-COMMIT-COUNT >= WS-COMMIT-INTERVAL
EXEC SQL COMMIT END-EXEC
DISPLAY "Committed at record "
WS-RECORDS-READ
MOVE ZEROS TO WS-COMMIT-COUNT
END-IF.
FINAL-COMMIT.
IF WS-COMMIT-COUNT > 0
EXEC SQL COMMIT END-EXEC
DISPLAY "Final commit"
END-IF.
WRITE-ERROR-RECORD.
ADD 1 TO WS-RECORDS-ERROR
MOVE UR-ACCT-NUMBER TO ER-ACCT-NUMBER
MOVE UR-ACTION TO ER-ACTION
MOVE SQLCODE TO ER-SQLCODE
MOVE SQLERRMC(1:SQLERRML) TO ER-MESSAGE
WRITE ERROR-RECORD.
CLOSE-FILES.
CLOSE UPDATE-FILE
CLOSE ERROR-FILE.
DISPLAY-STATS.
DISPLAY "==============================="
DISPLAY "BATCH UPDATE COMPLETE"
DISPLAY "Records read: " WS-RECORDS-READ
DISPLAY "Records updated: " WS-RECORDS-OK
DISPLAY "Records failed: " WS-RECORDS-ERROR
DISPLAY "===============================".
This program demonstrates every major embedded SQL concept from this chapter:
- EXEC SQL INCLUDE for the SQLCA
- Host variables with proper COMP-3 for decimal, PIC X for character
- INSERT, UPDATE, and DELETE with host variables
- SQLCODE checking after every SQL statement
- SQLERRD(3) verification for row counts
- Periodic COMMIT with a configurable interval
- Error records written to a file with SQLCODE for diagnosis
- Clean program structure separating file I/O, SQL operations, and error handling
27.20 The Student Mainframe Lab: Practicing Embedded SQL
Using IBM Z Xplore
IBM Z Xplore (formerly Master the Mainframe) provides free cloud access to a z/OS environment with DB2. This is the ideal platform for practicing embedded SQL because it includes the full DB2 precompiler, bind process, and runtime.
Setup steps: 1. Register at https://www.ibm.com/z/resources/zxplore 2. Complete the initial challenges to gain access to TSO/ISPF 3. Allocate datasets for your COBOL source and copybooks 4. Create sample DB2 tables using SPUFI (the DB2 interactive tool)
Using GnuCOBOL with OCESQL
If you do not have z/OS access, you can practice embedded SQL using GnuCOBOL with the OCESQL preprocessor, which supports PostgreSQL and MySQL.
Installation:
# Install PostgreSQL
sudo apt install postgresql postgresql-client
# Install OCESQL (from GnuCOBOL contrib)
# Follow instructions at:
# https://github.com/OCamlPro/gnucobol-contrib
# Compile an embedded SQL program
ocesql sample.cbl sample.cob
cobc -x sample.cob -L/usr/lib -lpq
The OCESQL syntax is similar to DB2's EXEC SQL, with minor differences:
- Use CONNECT to establish database connections
- SQLCODE values may differ slightly from DB2
- Some DB2-specific SQL syntax (like FETCH FIRST N ROWS ONLY) may not be supported
🧪 Try It Yourself: Building a Student Database
Step 1: Create the database tables:
CREATE TABLE STUDENT (
STUDENT_ID CHAR(8) NOT NULL PRIMARY KEY,
STUDENT_NAME VARCHAR(50) NOT NULL,
MAJOR_CODE CHAR(4) NOT NULL,
GPA DECIMAL(3,2),
ENROLL_DATE DATE NOT NULL,
ADVISOR_ID CHAR(8)
);
CREATE TABLE COURSE_ENROLLMENT (
STUDENT_ID CHAR(8) NOT NULL,
COURSE_CODE CHAR(8) NOT NULL,
SEMESTER CHAR(6) NOT NULL,
GRADE CHAR(2),
PRIMARY KEY (STUDENT_ID, COURSE_CODE, SEMESTER),
FOREIGN KEY (STUDENT_ID)
REFERENCES STUDENT(STUDENT_ID)
);
INSERT INTO STUDENT VALUES
('STU00001', 'Alice Johnson', 'CSCI', 3.85,
'2023-08-15', 'ADV001');
INSERT INTO STUDENT VALUES
('STU00002', 'Bob Williams', 'MATH', 3.42,
'2023-08-15', 'ADV002');
INSERT INTO STUDENT VALUES
('STU00003', 'Carol Davis', 'CSCI', 3.97,
'2022-08-15', 'ADV001');
Step 2: Write the COBOL inquiry program using the patterns from Sections 27.6 and 27.7. Your program should:
- Accept a student ID
- Display the student's name, major, and GPA (SELECT INTO)
- List all courses enrolled using a cursor
- Handle "student not found" gracefully
- Display total courses enrolled at the end
Step 3: Write a batch update program that reads a file of GPA updates and applies them to the STUDENT table, committing every 10 records (since the test dataset is small).
Debugging Embedded SQL in the Lab
When your embedded SQL program does not work as expected, follow this diagnostic sequence:
Step 1: Check the precompiler output. The precompiler generates a listing that shows each SQL statement and any syntax errors. On z/OS, this is the SYSPRINT output from the DSNHPC step.
Step 2: Check the BIND output. If the program precompiles successfully but the BIND fails, the SQL references tables or columns that do not exist in DB2's catalog. Verify table names, column names, and your authorization.
Step 3: Add DISPLAY statements around SQL. Before and after each EXEC SQL block, display the host variable values and the SQLCODE:
DISPLAY "Before query: ACCT=" HV-ACCT-NUMBER
EXEC SQL
SELECT ... INTO ...
END-EXEC
DISPLAY "After query: SQLCODE=" SQLCODE
" ROWS=" SQLERRD(3)
Step 4: Check for NULL issues. If SQLCODE = -305, a NULL value is being returned without an indicator variable. Add indicator variables to all nullable columns.
Step 5: Check for data type mismatches. If your query returns unexpected values (wrong amounts, garbled text), verify that host variable PIC clauses match the DB2 column types using the DCLGEN mapping table from Section 27.4.
27.21 Chapter Review Questions
Before moving to the summary, test your understanding:
-
Trace the execution of a cursor-based program: What happens if you FETCH from a cursor that has not been OPENed? What SQLCODE do you get?
-
Explain why Tomás Rivera chose separate queries instead of a JOIN for the MedClaim claim inquiry. Write the JOIN version and identify the specific scenario where it would return different results.
-
Transaction design: A program needs to transfer $500 from Account A to Account B, log the transfer, and send a notification. Draw the transaction boundary. Which operations should be inside the COMMIT scope? Which should be outside? What happens if the notification step fails — should the transfer be rolled back?
-
Batch commit analysis: A batch program processes 1 million records. Compare commit intervals of 1, 100, 1000, and 10000. For each, estimate the total number of COMMIT operations, the approximate processing time overhead, and the worst-case recovery time if the program ABENDs at record 750,000.
27.22 Common Embedded SQL Mistakes and How to Avoid Them
This section catalogs the most frequent embedded SQL errors encountered by COBOL-DB2 programmers. Learning from these mistakes will save you debugging time and production incidents.
Mistake 1: Not Checking SQLCODE After Every Statement
This is the number one embedded SQL mistake, and it bears repeating. Every SQL statement — every SELECT, INSERT, UPDATE, DELETE, OPEN, FETCH, CLOSE — must be followed by an SQLCODE check.
*> WRONG: No SQLCODE check after UPDATE
EXEC SQL
UPDATE ACCOUNT_MASTER
SET ACCT_BALANCE = :HV-BALANCE
WHERE ACCT_NUMBER = :HV-ACCT-NUM
END-EXEC
DISPLAY "Update complete"
*> The UPDATE may have failed silently!
*> SQLCODE could be -911 (deadlock),
*> -904 (resource unavailable), or +100 (no matching row)
Fix: Check SQLCODE after every statement, even ones you "know" will work.
Mistake 2: SELECT INTO with Non-Unique WHERE Clause
*> WRONG: May return multiple rows
EXEC SQL
SELECT ACCT_HOLDER INTO :HV-HOLDER
FROM ACCOUNT_MASTER
WHERE ACCT_STATUS = 'A'
END-EXEC
*> SQLCODE = -811 if more than one active account exists
Fix: Ensure the WHERE clause matches exactly one row (use primary key), or use a cursor.
Mistake 3: Missing Null Indicator Variables
*> WRONG: PHONE_NUMBER is nullable
EXEC SQL
SELECT PHONE_NUMBER
INTO :HV-PHONE
FROM CUSTOMER
WHERE CUST_ID = :HV-CUST-ID
END-EXEC
*> SQLCODE = -305 if PHONE_NUMBER is NULL
Fix: Always use indicator variables for nullable columns:
EXEC SQL
SELECT PHONE_NUMBER
INTO :HV-PHONE:NI-PHONE
FROM CUSTOMER
WHERE CUST_ID = :HV-CUST-ID
END-EXEC
IF NI-PHONE < 0
MOVE "(no phone)" TO HV-PHONE
END-IF
Mistake 4: Cursor Left Open After Error
*> WRONG: Error path doesn't close cursor
FETCH-LOOP.
EXEC SQL FETCH MY-CURSOR INTO ... END-EXEC
IF SQLCODE = -911
DISPLAY "Deadlock!"
GOBACK *> Cursor left open!
END-IF
Fix: Always close cursors in error handlers:
IF SQLCODE = -911
DISPLAY "Deadlock!"
EXEC SQL CLOSE MY-CURSOR END-EXEC
EXEC SQL ROLLBACK END-EXEC
GOBACK
END-IF
Mistake 5: Committing After Every Row in Batch
*> WRONG: Extremely slow for large batches
PROCESS-LOOP.
EXEC SQL UPDATE ... END-EXEC
EXEC SQL COMMIT END-EXEC *> Every row!
Each COMMIT forces DB2 to write to the log, sync I/O to disk, and release locks. At 1,000 COMMITs per second, processing one million records takes 1,000 seconds — over 16 minutes of pure COMMIT overhead.
Fix: Commit every N records (500-1000 is typical).
Mistake 6: Forgetting to Rebind After Recompile
After recompiling a COBOL-DB2 program, you must BIND the new DBRM to DB2. If you forget, the program runs with the old package, and SQLCODE = -818 (timestamp mismatch) results.
Fix: Include BIND in your build process — make it automatic, not manual. On z/OS, add a BIND step to your compile JCL.
27.23 Putting It All Together: The Embedded SQL Development Workflow
Understanding the end-to-end workflow for developing embedded SQL programs helps you work efficiently and avoid common process errors.
The Development Cycle
1. Design the SQL → Write SELECT/INSERT/UPDATE/DELETE
statements on paper or in SPUFI first
2. Generate DCLGEN → Run DCLGEN for all tables your
program accesses
3. Write the COBOL → Include SQLCA and DCLGEN copybooks,
write host variable logic, add
SQLCODE checking
4. Precompile → Run DSNHPC to extract SQL and
generate modified COBOL + DBRM
5. Compile → Run the COBOL compiler on the
modified source
6. Link → Link with the DB2 interface module
7. Bind → Bind the DBRM to create a DB2
package or plan
8. Test → Run against a test DB2 subsystem
with test data
9. Promote → Move to production, rebind against
production DB2
Testing Strategy for Embedded SQL Programs
Unit testing: Test each SQL operation in isolation. Create a test program that calls each paragraph with known input values and verifies the SQLCODE and output values.
Integration testing: Test the complete program flow, including the cursor lifecycle (OPEN, FETCH loop, CLOSE) and the transaction lifecycle (multiple DML operations, COMMIT).
Error path testing: Deliberately cause SQL errors to verify error handling: - Use an invalid account number to test SQLCODE +100 handling - Insert a duplicate key to test SQLCODE -803 handling - Lock a table from another session to test SQLCODE -911 handling
Performance testing: For batch programs, run against a realistic data volume. A program that works fine with 1,000 test records may behave differently with 1,000,000 production records due to lock contention, buffer pool pressure, and commit frequency.
Production Checklist
Before deploying an embedded SQL program to production, verify:
- [ ] All SQL statements have SQLCODE checking
- [ ] All nullable columns have indicator variables
- [ ] Cursors are closed in all error paths
- [ ] Batch programs have periodic COMMITs
- [ ] DCLGEN copybooks match production table definitions
- [ ] BIND has been executed against the production DB2 subsystem
- [ ] Authorization has been granted for all tables and operations
- [ ] Error messages include enough detail for production support diagnosis
27.24 Summary
Embedded SQL is the primary mechanism for COBOL programs to interact with relational databases, particularly IBM DB2 on z/OS. The key concepts covered in this chapter are:
EXEC SQL...END-EXEC: All SQL statements in COBOL are enclosed in these delimiters. Each block contains exactly one SQL statement.
Host variables: COBOL data items used in SQL statements, prefixed with a colon. COMP-3 maps to DECIMAL, COMP maps to INTEGER, PIC X maps to CHAR. Use indicator variables (PIC S9(4) COMP) for nullable columns.
DCLGEN: Automatically generates COBOL data structures from DB2 table definitions. Always use DCLGEN rather than manual declarations.
SQLCA: The SQL Communication Area, populated after every SQL statement. SQLCODE is the primary success/failure indicator: 0 = success, +100 = not found, negative = error.
SELECT INTO: Retrieves a single row into host variables. Returns SQLCODE -811 if more than one row matches.
Cursors: DECLARE, OPEN, FETCH (in a loop), CLOSE. The standard mechanism for processing multi-row result sets. Use FOR UPDATE OF for positioned updates.
DML operations: INSERT, UPDATE, and DELETE all use host variables with the colon prefix. Check SQLERRD(3) for the number of rows affected.
WHENEVER: Automatic error branching — useful as a safety net but not a substitute for explicit SQLCODE checking.
The precompiler: Transforms EXEC SQL into COBOL CALL statements and produces a DBRM for DB2 binding. Understanding this process explains compilation errors and runtime bind issues.
Transaction control: COMMIT makes changes permanent; ROLLBACK undoes changes. Batch programs should COMMIT periodically to balance performance and recovery.
⚠️ Defensive Programming: The single most important habit in embedded SQL programming is checking SQLCODE after every SQL statement. Not most of them. Every one. A missed SQLCODE check is a ticking time bomb — the program continues with bad data until something visibly fails, often far from the original error.
🔗 Looking Ahead: Chapter 28 will cover advanced DB2 techniques, including dynamic SQL, stored procedures, and performance tuning. Chapter 29 introduces CICS — the online transaction processing environment where embedded SQL meets interactive, real-time applications.