Multi-Row Inserts for DB2 Version 8

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.