Db2 SQL Execution Validation: SQLCA, SQLCODE, Row Counts, and COMMIT Checks

Validate the SQL result before the next step.

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-EVALUATE

If 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-EVALUATE

For 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-PERFORM

Commit 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 ROLLBACK when a related update, insert, or delete fails inside the same unit of work.
• Log the business key, program name, paragraph, SQLCODE, SQLSTATE, and SQLERRMC.
• 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.