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
One of the new features of DB2 for Z/OS Version 8 is the multi-row insert. It enables you to insert more than one row per execution of your EXEC SQL INSERT statement. Inserting more than one row at a time will improve the performance of your application.
In order to be able to use multi-row insert, you need to declare your host variables as arrays. Each host variable needs to have their own OCCURS clause. I fell to the temptation once of putting my host variables in a group item that is declared as an array. DB2 precomile was successful but it failed in the COBOL compile. DB2 wasn't able to interpret the host-variables as belonging to an array. Here's how your host variable should be declared: 01 HV-SAMPLE.
05 HV-ACCOUNT PIC X(08) OCCURS 100.
05 HV-NAME PIC X(20) OCCURS 100.
05 HV-AMOUNT PIC S9(09)V99 OCCURS 100. In addition to declaring your host variables as arrays, you need to add the new clause FOR N ROWS and the new keywords ATOMIC or NOT ATOMIC CONTINUE ON EXCEPTION.
Here's an example:
MOVE 100 TO WS-NUMROWS-TO-INSERT
EXEC SQL
INSERT INTO ACCOUNT-TABLE
VALUES(:HV-ACCOUNT,
:HV-NAME,
:HV-AMOUNT)
FOR :WS-NUMROWS-TO-INSERT ROWS
ATOMIC
END-EXEC.
You can see that the number of rows that will be inserted is controlled by the WS-NUMROWS-TO-INSERT variable. You can also use a literal if the number of rows to insert will always be the same.
The ATOMIC keyword tells DB2 to back out the INSERT if it fails to insert one of the rows including the other rows prior to the row that encountered the error. For example, your multi-row insert is trying to insert 100 rows but encountered an error on the 10th row. The nine rows that were already inserted will be backed out, and DB2 will no longer attempt to insert the 90 remaining rows. If you don't want DB2 to do this, you need to specify NOT ATOMIC CONTINUE ON EXCEPTION. This will treat each error encountered separately and will not back out the prior rows that were already inserted. Also, it will continue processing the remaining rows after the row that encountered the error. ATOMIC is the default.
