Cursor Error Handling

Every cursor statement can fail, so every program must check SQLCODE after DECLARE, OPEN, FETCH, UPDATE, DELETE and CLOSE, and handle the errors in a clean way.

Checking SQLCODE after each cursor statement

• SQLCODE = 0 means the statement worked.
• SQLCODE = 100 means no more rows (on FETCH) or no rows found - it is a normal condition, not an error.
• SQLCODE < 0 means an error - the statement failed and the program must handle it.
• Include the SQLCA (EXEC SQL INCLUDE SQLCA END-EXEC) so SQLCODE is available after every SQL statement.
• A clean pattern is the EVALUATE block:
  
  EVALUATE SQLCODE WHEN 0 CONTINUE WHEN 100 EXEC SQL CLOSE EMPCUR END-EXEC WHEN OTHER DISPLAY "SQL ERROR " SQLCODE PERFORM ERROR-PARA END-EVALUATE.
• In the error paragraph, CLOSE any open cursors, then either abend with a message or ROLLBACK and stop - never continue fetching after a negative SQLCODE.

Key cursor SQLCODEs to remember

• 100 - No row satisfied the statement, or end of cursor on FETCH. Normal - close the cursor.
• -501 - Cursor not open on FETCH, CLOSE, UPDATE or DELETE. OPEN the cursor first.
• -502 - Opening a cursor that is already open. CLOSE it before OPENing again.
• -503 - Column cannot be updated because it was not named in the FOR UPDATE OF clause of the cursor.
• -545 - Cursor name not declared. Check the spelling against the DECLARE.
• -811 - Singleton SELECT returned more than one row. Use a cursor instead.
• -305 - Null indicator needed. A nullable column was fetched without an indicator variable.
• -911 / -913 - Deadlock or timeout. Roll back and retry the unit of work.

WHENEVER - letting DB2 check for you

• WHENEVER SQLERROR GO TO ERROR-PARA tells DB2 to jump to your error paragraph on any negative SQLCODE.
• WHENEVER NOT FOUND GO TO END-PARA (or CONTINUE) handles SQLCODE 100 automatically.
• WHENEVER SQLWARNING handles positive warning codes like 100 variants.
• WHENEVER applies from the point it is coded - place it before the cursor logic it should guard.
• Even with WHENEVER, many shops still code explicit SQLCODE checks because they make the program flow easier to read and debug.