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.

Db2 Data Types Guide: COBOL Host Variables, CHAR, DECIMAL, DATE, and LOBs



Column type and COBOL host variable must agree.

A bad data type choice can turn a clean SQL statement into a production defect. A customer amount stored as floating point, a date stored as free text, or a nullable column fetched without an indicator variable can create wrong results before the program ever gets an abend.

Db2 data types define what a column can store, how Db2 compares values, which functions can be used, and how a COBOL host variable should be declared. For mainframe work, the best data type is usually the one that matches the business value and keeps the program's host variables predictable.

Common Db2 data type groups

Group	Db2 data types	Typical use
Exact numeric	SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC	Counts, identifiers, money, rates, quantities, and packed business values.
Approximate numeric	REAL, DOUBLE, FLOAT	Scientific or approximate values where rounding is expected. Avoid these for money.
Character	CHAR, VARCHAR, CLOB	Codes, names, descriptions, comments, and long text.
Graphic	GRAPHIC, VARGRAPHIC, DBCLOB	Double-byte character data where the application uses graphic strings.
Binary	BINARY, VARBINARY, BLOB	Bit data, encoded payloads, documents, and other non-character values.
Date and time	DATE, TIME, TIMESTAMP	Business dates, processing times, audit columns, and event timestamps.
Special identifiers	ROWID	Direct row identification where the table design and access path require it.

Db2 and COBOL host variable mapping

In embedded SQL, the column type and the COBOL host variable must be compatible. DCLGEN is often used to generate a starting copybook, but a programmer still has to understand the mapping before changing column definitions or hand-coding host variables.

Db2 column	Typical COBOL host variable	Watch point
CHAR(10)	01 WS-CODE PIC X(10).	Fixed length values can include trailing blanks.
VARCHAR(40)	Group item with length and text fields	COBOL needs the length field and data field, not only PIC X(40).
DECIMAL(9,2)	01 WS-AMT PIC S9(7)V99 COMP-3.	Use packed decimal for business amounts where exact cents matter.
INTEGER	01 WS-COUNT PIC S9(9) COMP.	Binary host variables are common for integer values.
DATE	01 WS-DATE PIC X(10).	Db2 commonly returns ISO format such as 2026-07-07 unless formatting is changed.
TIMESTAMP	01 WS-TS PIC X(26).	Confirm precision and format before moving values to files or screens.
Nullable column	Host variable plus indicator variable	A null value needs an indicator variable; blanks and zero are not null.

Exact numeric types

Use exact numeric types when the value must be stored and compared without approximate rounding. DECIMAL is the normal choice for money, tax amounts, account balances, and rates that need fixed scale. SMALLINT, INTEGER, and BIGINT are better for whole-number counts and technical identifiers.

CREATE TABLE MF_CUSTOMER_BAL
 (ACCT_NO      CHAR(12)       NOT NULL,
  CURR_BAL     DECIMAL(13,2)  NOT NULL,
  PAYMENT_CNT  INTEGER        NOT NULL);

For a COBOL amount such as DECIMAL(13,2), a packed decimal host variable is usually easier to reason about than a display field. Keep the implied decimal position clear in both the copybook and the program move logic.

Character and varying character types

CHAR works well for fixed business codes, such as a two-character country code or a one-character status. VARCHAR fits names, descriptions, and text values where the stored length varies from row to row.

CREATE TABLE MF_ORDER
 (ORDER_ID      INTEGER      NOT NULL,
  ORDER_STATUS  CHAR(1)      NOT NULL,
  CUSTOMER_NAME VARCHAR(60)  NOT NULL);

Do not choose VARCHAR only because it looks flexible. If every value is a fixed two-character code, CHAR(2) is simpler for predicates, display checks, and host variable handling.

Date, time, and timestamp types

Use DATE, TIME, and TIMESTAMP for real calendar and clock values. Storing a date in CHAR(8) may look easy in COBOL, but it pushes validation and comparison work into the program.

CREATE TABLE MF_AUDIT_EVENT
 (EVENT_ID     INTEGER     NOT NULL,
  EVENT_DATE   DATE        NOT NULL,
  EVENT_TS     TIMESTAMP   NOT NULL);

With a real date column, Db2 can compare dates as dates. A predicate such as WHERE EVENT_DATE >= DATE('2026-07-01') is clearer than comparing text strings that might not be in a consistent format.

Large object and binary types

CLOB, BLOB, and DBCLOB are for values larger than ordinary character or binary strings. They are useful for long text, documents, images, and payloads, but they need different program handling from a normal CHAR or VARCHAR column.

Before adding a large object column, check how the application will read it, update it, back it up, and move it through test data. A daily batch extract that once wrote fixed records may need new handling when a CLOB or BLOB appears in the table.

ROWID and distinct types

ROWID identifies a row in a way Db2 can use for direct row access. It is not a business key, and it should not replace a proper primary key such as account number, policy number, or claim number. Use it only when the table design calls for that access pattern.

Db2 also supports distinct types, which let a site create a type based on a built-in type. That can help make two values with the same physical representation mean different things, such as an account identifier and a branch identifier. Check local standards before using distinct types, because they affect SQL, casting, and application code.

How to choose a Db2 data type

Question	Good starting choice
Will the value be used in arithmetic?	DECIMAL for exact business amounts, or integer types for whole numbers.
Is the value a fixed code?	CHAR with the real code length.
Is the value human text with variable length?	VARCHAR, or CLOB for long text.
Is the value a calendar date or event time?	DATE, TIME, or TIMESTAMP.
Can the column be unknown?	Allow null only when the business rule needs it, and code indicator variables in COBOL.
Will the column be searched often?	Choose a type that matches predicates and indexes without frequent casts.

Common mistakes

• Using CHAR for dates and then comparing text instead of real dates.
• Using approximate numeric types for money or balances.
• Changing a column type without checking DCLGEN copybooks, bind steps, and test data.
• Fetching nullable columns without indicator variables.
• Using a larger data type because it feels safer, then paying for bigger indexes and wider rows.

Related DB2 topics

Data types connect directly to Db2 Objects, Db2 Relational Database Anatomy, Db2 Table Spaces, Db2 Indexing, and Db2 Data Types for GET DIAGNOSTICS.

FAQ

Which Db2 data type should be used for money?

Use DECIMAL with the precision and scale required by the business rule. Avoid approximate numeric types for money because rounding can create wrong balances.

Should dates be stored as CHAR in Db2?

Use DATE for calendar dates unless a strict interface rule forces text. Db2 can compare, validate, and format real date columns more reliably than free text.

Does a nullable Db2 column need a COBOL indicator variable?

Yes. When a nullable column is selected into a COBOL host variable, the program should use an indicator variable to detect null. A blank or zero value is not the same as null.

Pick the column type first, then make the COBOL host variable match it. That small discipline prevents many quiet data defects.