Db2 SQLCODE and SQLSTATE: SQLCA Return Codes for COBOL Programs



Check SQLCODE after each Db2 statement.

SQLCODE +100 is not the same as a failed SELECT. It means no row was found, and a COBOL program often has to handle that path cleanly. A negative SQLCODE is different: it means the SQL statement failed and the program should not continue as if the data is valid.

Db2 returns SQL status information through the SQLCA, including SQLCODE, SQLSTATE, warning flags, row counts, and diagnostic text. Good programs check those fields immediately after each SQL statement that matters.

SQLCODE quick meaning

SQLCODE value	Meaning	Program action
0	Statement completed successfully	Continue normal processing.
> 0	Statement completed with a warning or special condition	Handle the specific code. +100 is common for no row found.
< 0	Statement failed	Log SQLCA details and take the error path.

SQLSTATE quick meaning

SQLSTATE is a five-character return code. The first two characters identify a class, and the last three identify a subclass. It is useful when code needs a standard status check across database products, while SQLCODE often gives Db2-specific detail.

SQLSTATE class	General meaning
00	Successful completion
01	Warning
02	No data
Other classes	Error or condition that must be checked against Db2 message documentation

Important SQLCA fields

Field	Use
SQLCODE	Primary numeric return code checked by many COBOL programs.
SQLSTATE	Five-character status code useful for standard classes.
SQLERRMC	Message tokens that can identify object names or values related to an error.
SQLERRD	Diagnostic integers; one common use is row count after some SQL operations.
SQLWARN	Warning indicators, such as truncation or null-related conditions.

COBOL SQLCODE handling pattern

EXEC SQL
    SELECT CUSTOMER_NAME
      INTO :WS-CUSTOMER-NAME
      FROM CUSTOMER
     WHERE CUSTOMER_ID = :WS-CUSTOMER-ID
END-EXEC.

EVALUATE SQLCODE
    WHEN 0
        CONTINUE
    WHEN +100
        MOVE 'N' TO WS-CUSTOMER-FOUND
    WHEN OTHER
        DISPLAY 'DB2 ERROR SQLCODE=' SQLCODE
        DISPLAY 'SQLSTATE=' SQLSTATE
        DISPLAY 'SQLERRMC=' SQLERRMC
        PERFORM ABEND-ROUTINE
END-EVALUATE.

Do not bury this check far away from the SQL statement. When a production issue happens, the fastest clue is often the exact statement, SQLCODE, SQLSTATE, and message tokens written together.

Common mistakes

• Treating +100 as a fatal error when it is a normal no-row path for the program.
• Ignoring positive warning codes because only negative codes stop the job.
• Checking SQLCODE after several statements instead of immediately after the statement that set it.
• Logging only SQLCODE and losing SQLSTATE or SQLERRMC details.
• Continuing after a negative SQLCODE and writing output records from invalid host variable values.

Related DB2 topics

SQLCODE handling connects to Db2 SQL Execution Validation, Db2 Application Environment, Db2 Data Types, Db2 Triggers, and Db2 DSN Command Reference.

FAQ

Is SQLCODE +100 an error?

No. +100 means no row was found or no more rows are available. It is often a normal branch in SELECT and cursor logic.

Should COBOL check SQLCODE or SQLSTATE?

Many Db2 COBOL programs check SQLCODE first because it gives Db2-specific detail. SQLSTATE is useful for standard status classes and cross-product logic.

Where is SQLCODE stored?

When the SQLCA is included, Db2 places the return code in the SQLCA field SQLCODE. Some precompiler options can also use standalone SQLCODE and SQLSTATE host variables.

The rule is simple enough to save a batch night: check the SQL return status before trusting the host variables.