DB2 Error Handling in COBOL

The standard error-handling pattern

• Include SQLCA in WORKING-STORAGE so SQLCODE and SQLSTATE are available.
• Check SQLCODE immediately after every SQL statement.
• Use EVALUATE: 0 means continue, +100 means no-data logic, negative means the error paragraph.
• Never let a negative SQLCODE fall through to normal processing.
• The decision flow is shown below:

• 

Writing the error paragraph

• DISPLAY SQLCODE, SQLSTATE and SQLERRMC so the job log explains the failure.
• Issue ROLLBACK before stopping, so no partial unit of work is left behind.
• For -913 the ROLLBACK is mandatory. For -911 DB2 already rolled back.
• Decide per code: retry for deadlocks (-911, -913), abend or STOP RUN for the rest.

Complete error-handling example

• Example - the full pattern in one program:
  
  WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. ... PROCEDURE DIVISION. MAIN-PARA. PERFORM FETCH-PARA UNTIL SQLCODE NOT = 0 ... FETCH-PARA. EXEC SQL FETCH C1 INTO :WS-EMPNO, :WS-EMPNAME END-EXEC EVALUATE SQLCODE WHEN 0 PERFORM PROCESS-ROW WHEN 100 DISPLAY 'All rows processed' WHEN -911 DISPLAY 'Deadlock/timeout - retrying' PERFORM RETRY-LOGIC WHEN -913 DISPLAY 'Deadlock victim - rolling back' EXEC SQL ROLLBACK END-EXEC PERFORM RETRY-LOGIC WHEN OTHER PERFORM ERROR-PARA END-EVALUATE. ERROR-PARA. DISPLAY 'SQL error. SQLCODE = ' SQLCODE DISPLAY 'SQLSTATE = ' SQLSTATE DISPLAY 'Message: ' SQLERRMC EXEC SQL ROLLBACK END-EXEC STOP RUN.