Diagnosing Multi-Row Insert Errors

Errors encountered on an ATOMIC multi-row insert will be reflected on the SQLCODE. For example, if the ATOMIC multi-row insert failed due to duplicate rows, SQLCODE will be -803.

A NON-ATOMIC multi-row insert can return the following SQLCODE:
   

• 0 - Successful
  
• +252 - Successful but with Warnings
  
• -253 - At least one row (but not all) failed
  
• -254 - All inserts failed. No rows were inserted.
  

In order to determine how many rows were successfully inserted, which row failed, and to find out what errors were encountered, you need to execute the GET DIAGNOSTICS statement.

To get the number of rows and the number of error conditions encountered, you can execute GET DIAGNOSTICS this way:

EXEC SQL GET DIAGNOSTICS
    :ROWS-INSERTED = ROW_COUNT,
    :ERROR-CONDITIONS = NUMBER
END-EXEC

Now that you know the number of error conditions encountered you can perform a loop to check out those errors:

PERFORM VARYING I FROM 1 BY 1
  UNTIL I > ERROR-CONDITIONS

        EXEC SQL GET DIAGNOSTICS
            :WS-SQLCODE = DB2_RETURNED_SQLCODE,
            :WS-ROWNUM = DB2_ROW_NUMBER
        END-EXEC

        PERFORM Z900-DB2-ERRORS

END-PERFORM