SQLCODE

What is SQLCODE

• Every time a DB2 program runs an SQL statement, DB2 returns a status code.
• This code is stored in the SQLCODE field of the SQLCA block.
• Your program must check SQLCODE after every SQL statement to know what happened.
• Ignoring SQLCODE means errors and warnings go unnoticed.

SQLCODE values and their meaning

• 0 means the statement ran successfully.
• A negative value means the statement failed with an error. Example: -911 means a deadlock or timeout occurred and DB2 rolled back.
• A positive value means the statement ran but with a warning or special condition. Example: +100 means no rows found or end of table.
• Learn the common codes by heart: 0, +100, -803, -805, -811, -818, -911, -913.

Checking SQLCODE in a COBOL program

• Code EXEC SQL INCLUDE SQLCA END-EXEC in WORKING-STORAGE so the SQLCODE field exists.
• Check SQLCODE immediately after each EXEC SQL block, before any other SQL runs.
• Example:
  
  WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. ... PROCEDURE DIVISION. MAIN-PARA. EXEC SQL SELECT EMPNAME INTO :WS-EMPNAME FROM EMP WHERE EMPNO = :WS-EMPNO END-EXEC EVALUATE SQLCODE WHEN 0 DISPLAY 'Row found: ' WS-EMPNAME WHEN 100 DISPLAY 'No row found for ' WS-EMPNO WHEN OTHER DISPLAY 'SQL error, SQLCODE = ' SQLCODE STOP RUN END-EVALUATE.