A COBOL program can compile, bind, and start correctly, then still make a bad production decision if it ignores the result of the SQL statement it just ran. A singleton SELECT might return +100, a searched UPDATE might affect zero rows, or a warning flag might show that data was truncated into a host variable.
Db2 SQL execution validation is the application logic that checks those results before the next business step runs. For batch and online programs, that usually means checking SQLCODE, SQLSTATE, selected SQLCA fields, indicator variables, affected-row counts, and commit or rollback decisions.
What SQL execution validation means
Execution validation is not the same as syntax checking. The Db2 precompiler validates embedded SQL structure, and bind processing validates access paths and object references for static SQL. Runtime validation happens after each SQL statement runs inside the program.
At runtime, the program should decide whether the SQL result is expected, unexpected but recoverable, or a hard error. That decision must be close to the statement that caused it. When error handling is pushed to the end of a paragraph, the program often loses the table name, key value, and business context needed for useful diagnosis.
Fields most programs should check
| Field or value | What to check | Typical program action |
|---|---|---|
SQLCODE | 0, +100, warnings, and negative values. | Continue, branch to no-data logic, log warning, or stop with an error path. |
SQLSTATE | Five-character class and subclass, useful for grouped error handling. | Use with SQLCODE when you need portable or class-based handling. |
SQLERRMC | Message tokens returned by Db2. | Write it to an error log with the program name, table, and key fields. |
SQLERRD(3) | Commonly checked for affected-row count after searched UPDATE, DELETE, or INSERT processing. Confirm behavior for your statement and Db2 level. | Reject a zero-row update when one row was required, or flag too many rows when a key should be unique. |
SQLWARN fields | Warning flags such as truncation or null assignment cases. | Log and decide whether the warning is acceptable for that transaction. |
| Indicator variables | Null and truncation status for nullable columns. | Prevent spaces, zeros, or old working-storage values from being treated as real data. |
Validation pattern for singleton SELECT
A singleton SELECT INTO expects either one row or no row. Treating +100 as a normal zero-value result can create bad output files, missing customer records, or incorrect audit rows.
EXEC SQL
SELECT ACCT_STATUS,
CURRENT_BAL
INTO :WS-ACCT-STATUS,
:WS-CURRENT-BAL
FROM ACCOUNT
WHERE ACCT_NO = :WS-ACCT-NO
END-EXEC
EVALUATE SQLCODE
WHEN 0
PERFORM VALIDATE-ACCOUNT-DATA
WHEN +100
MOVE 'ACCOUNT NOT FOUND' TO WS-ERROR-TEXT
PERFORM WRITE-APPLICATION-ERROR
WHEN OTHER
PERFORM WRITE-DB2-ERROR
PERFORM ABEND-PROGRAM
END-EVALUATEIf nullable columns are selected, add indicator variables. Without them, the program may accept stale working-storage values after a null column is returned.
Validation pattern for UPDATE, INSERT, and DELETE
For data-change statements, SQLCODE = 0 only says Db2 accepted the statement. The program still needs to check whether the number of affected rows matches the business rule.
EXEC SQL
UPDATE ACCOUNT
SET ACCT_STATUS = :WS-NEW-STATUS
WHERE ACCT_NO = :WS-ACCT-NO
END-EXEC
EVALUATE SQLCODE
WHEN 0
IF SQLERRD(3) = 1
PERFORM WRITE-AUDIT-ROW
ELSE
MOVE 'UNEXPECTED UPDATE COUNT' TO WS-ERROR-TEXT
PERFORM WRITE-APPLICATION-ERROR
PERFORM ROLLBACK-WORK
END-IF
WHEN +100
MOVE 'NO ACCOUNT UPDATED' TO WS-ERROR-TEXT
PERFORM WRITE-APPLICATION-ERROR
WHEN OTHER
PERFORM WRITE-DB2-ERROR
PERFORM ROLLBACK-WORK
END-EVALUATEFor a key-based update, one affected row may be the only acceptable result. For a batch correction statement, thousands of rows may be expected. Code the expected count instead of assuming any successful SQLCODE is enough.
Cursor FETCH validation
A cursor loop usually has three valid paths: row found, end of cursor, and error. The program should not treat every non-zero SQLCODE as an abend, because +100 is the normal end-of-data signal for a FETCH.
PERFORM UNTIL WS-END-OF-CURSOR = 'Y'
EXEC SQL
FETCH C1
INTO :WS-ACCT-NO,
:WS-ACCT-STATUS
END-EXEC
EVALUATE SQLCODE
WHEN 0
PERFORM PROCESS-ACCOUNT
WHEN +100
MOVE 'Y' TO WS-END-OF-CURSOR
WHEN OTHER
PERFORM WRITE-DB2-ERROR
PERFORM ROLLBACK-WORK
MOVE 'Y' TO WS-END-OF-CURSOR
END-EVALUATE
END-PERFORMCommit and rollback checks
Transaction control belongs in the validation design. A batch job that updates 50,000 rows should know when to commit, what to do after a failed commit, and how much restart information has been written. An online program should avoid sending a success message before the unit of work is safely committed.
- Check every SQL statement that changes data before issuing
COMMIT. - Use
ROLLBACKwhen a related update, insert, or delete fails inside the same unit of work. - Log the business key, program name, paragraph,
SQLCODE,SQLSTATE, andSQLERRMC. - For restartable batch jobs, record the last committed key or checkpoint data.
Common mistakes
Checking only negative SQLCODEs
+100 can be correct for a cursor end, but wrong for a required singleton lookup. Warnings can also matter when host variables receive truncated values.
Ignoring affected-row count
An update that affects zero rows can still return a successful SQL execution path. The business rule decides whether zero rows is acceptable.
Logging only the SQLCODE
A production support team needs more than -803 or -911. Include table name, key values, module name, and message tokens where available.
Related Db2 topics
Use this article with Db2 SQLCODE and SQLSTATE, Db2 Application Environment, Db2 Data Types, Db2 Binding Application, and Db2 Utilities.
FAQ
Should a COBOL Db2 program check SQLCODE after every statement?
Yes. Every embedded SQL statement should have a nearby validation path so the program can handle success, no-data, warning, and error results with the correct business context.
Is SQLCODE +100 always an error?
No. For a cursor fetch, +100 usually means end of cursor. For a required singleton lookup, it may mean the application cannot continue safely.
When should SQLERRD(3) be checked?
Check it when the program must confirm how many rows were affected by a data-change statement. Confirm the exact meaning for your SQL statement and Db2 version.
