Logic to insert Multiple Rows using Arrays in application program

In DB2 we can insert rows in two ways. One is insert single row with Values. The second one is multi row insert.

Store for Db2 books.

Arrays is the concept which was introduced in DB2 v8.

How to use arrays?

Process:

1. Declare variable to hold no of rows
2. Host variable declaration, write each variable in an array
3. Move value to no of rows
4. Insert statement give host variable array

Example:

01  HV-NUM-ROWS                     PIC S9(4) COMP.
01  HOST-VARIABLES.
05  HV-EMPNO-ARRAY PIC X(6)  OCCURS 100 TIMES.
05  HV-LASTNAME-ARRAY OCCURS 100 TIMES.
49  HV-LASTNAME-LENGTH  PIC S9(4) COMP.
49  HV-LASTNAME-TEXT      PIC X(25).
05  HV-SALARY-ARRAY       PIC S9(7)V9(2) OCCURS 100 TIMES.
05  HV-BONUS-ARRAY    PIC S9(7)V9(2)  OCCURS 100 TIMES.
05 HV-BONUS-NI-ARRAY PIC S9(4) COMP OCCURS 100 TIMES.

The program loads up the arrays with data to be inserted and then executes the following SQL statement:

MOVE +100 TO HV-NUM-ROWS.
INSERT INTO EMP
  (EMPNO, LASTNAME, FIRSTNME,
    MIDINIT, SALARY, BONUS)
VALUES (:HV-EMPNO-ARRAY,
        :HV-LASTNAME-ARRAY,
        :HV-SALARY-ARRAY,
        :HV-BONUS-ARRAY:HV-BONUS-NI-ARRAY)
FOR :HV-NUM-ROWS
ATOMIC

Atomic: Tells Db2 if an error occurs during insert, all the rows will be rolled back

NOT ATOMIC CONTINUE ON SQLEXCEPTION: It will continue insert all the rows,except erred rows.

To find rows which are not inserted, use SQL GET DIAGNOSTICS statement.

For Dynamic SQL, developers must specify For Multiple Rows during the PREPARE and For N Rows and Using for the list of variables in the EXECUTE. See the IBM manuals for further information.