SQLCODE +100

What SQLCODE +100 means

• +100 is a positive SQLCODE, so the statement executed - but with a special condition.
• It means 'no data': no row found, or the end of a cursor's result set.
• The SQLSTATE for this condition is 02000.
• It is NOT an error. Programs should expect it and handle it.

When +100 happens

• A singleton SELECT INTO finds no matching row.
• A FETCH reaches the end of the cursor because all rows were already fetched.
• An UPDATE or DELETE matches zero rows.
• A searched UPDATE or DELETE whose WHERE clause matches nothing returns +100.

Handling +100 in a FETCH loop

• Loop the FETCH until SQLCODE is not 0. +100 means close the cursor and continue.
• For a singleton SELECT, +100 means 'not found'. Code the not-found business logic.
• Example:
  
  FETCH-PARA. EXEC SQL FETCH C1 INTO :DCLDEPT2 END-EXEC IF SQLCODE = 0 DISPLAY DNO DNAME ELSE IF SQLCODE = 100 PERFORM CLOSE-PARA ELSE DISPLAY 'Fetch failed. SQLCODE = ' SQLCODE STOP RUN END-IF END-IF.