SQL DB2 for z/OS Version 8 Feature Part 2b: Multi-row INSERT & FETCH

Continuing on from where I left off yesterday, let's complete the discussion about multi-row processing in DB2 V8 with a few words about multi-row INSERT.
My discussion of multi-row FETCH and setting up the arrays used by multi-row processing can be found in my entry posted on January 21,2006.

On DB2 for z/OS we have had two ways, using SQL, to get rows into a table.
You could insert one row at a time using the INSERT with a VALUE clause or insert multiple rows via an INSERT that included a SELECT clause from another table.
Until now we have had no way to insert multiple new rows with a single statement although there have been attempts at doing this on other DB2's.

DB2 V8 will use arrays, similar to FETCH processing, to insert more than one row into a DB2 table with a single INSERT SQL statement.
To simplify describing how this will work, I am going to repeat my COBOL working storage section from my previous entry here to use as a reference.


         1         2         3         4         5         6         7         8 
12345678901234567890123456789012345678901234567890123456789012345678901234567890

       01  HOST-VARIABLES.		 
           05 HV-ACT-NMBR-ARRAY       PIC S9(04) USAGE COMP OCCURS 100 TIMES.
           05 HV-ACT-CITY-ARRAY       PIC  X(40)            OCCURS 100 TIMES.
           05 HV-ACT-NAME-ARRAY                             OCCURS 100 TIMES.
               49 HV-ACT-NMBR-ARRAY   PIC S9(04) USGE COMP.
               49 HV-ACT-NAME-ARRAY   PI C X(40).
           05 HV-IND-ARRAY            PIC S9(04) USAGE COMP OCCURS 100 TIMES.

Again, arrays are also available in PL/I, C, and C++ programs.

Multi-row INSERT has been extended with a new clause and a new keyword to accomplish inserting multiple rows through a single SQL statement.
FOR xyz ROWS, where xyz can be either an integer value or a host variable tells DB2 that this INSERT statement will be inserting multiple rows at one time.
The new keyword, ATOMIC, tells DB2 what to do if an error should occur during an INSERT.
FOR xyz ROWS can specify a value up to 32767 but also needs to be something less than or equal to the number of values defined to the applications array.
An example of an INSERT statement that will insert more than one row in DB2 V8 follows:


         1         2         3         4         5         6         7         8 
12345678901234567890123456789012345678901234567890123456789012345678901234567890

           EXEC SQL                                           
                INSERT INTO TEST-TABLE  (COL1, COL2, COL3)    
                VALUES (:HV-ACT-NMBR-ARRAY     
                ,       :HV-ACT-CITY-ARRAY            
                ,       :HV-ACT-NAME-ARRAY )     
                FOR 50 ROWS 
                ATOMIC  
           END-EXEC.

In this example, the INSERT into table column is the same as any other INSERT statement you might have used in previous versions of DB2.
Even the VALUES clause looks the same. However, in our example, the host variables used in the VALUES clause are all defined as arrays in our COBOL program.
Adding FOR 50 ROWS tells DB2 that the arrays will contain 50 values. And finally, the keyword ATOMIC tells DB2 to treat the entire INSERT as a single piece of work were all rows will be inserted or no rows will be inserted.

I think that a discussion on how ATOMIC works is appropriate at this point.
When coding a multiple row INSERT statement, you can specify ATOMIC, the default, or NOT ATOMIC CONTINUE ON SQLEXCEPTION.
What a mouth full.
As mentioned in the previous paragraph, ATOMIC turns on the all or nothing switch.
If any INSERT fails, all INSERTs for this SQL statement, even those that completed successfully, will be rolled back.
If NOT ATOMIC ... is specified, any successfully inserted row prior to the row in error remains, no rollback is attempted.
The INSERT will also continue in attempting to insert the remaining rows in the array.
Only the individual row with an error is not inserted.
You can use the SQL GET DIAGNOSTICS statement to determine which row (or rows) was not successfully inserted.
There are SQLCODEs and warnings to tell you whether the inserts were successfully, partially successful and which rows did not work, or if all of the inserts failed.
If you have questions about which option to use, keep in the mind the number of rows you are going to insert and how your application will react (reporting, restarting, etc...) to a partial set of inserted rows.
All or nothing for 10 rows is probably not a big deal for DB2.
However, all of nothing for 20,000 rows may impact you applications performance.
There are also coding considerations for dynamically using this keyword and using multi-row INSERT in general.
The ATOMIC clause and FOR MUTIPLE ROWS clause must be coded on the PREPARE statement if a multi-row INSERT statement is to be dynamically prepared.
In addition, the FOR n ROWS clause will be coded on the EXCUTE statement.

Multi-row INSERT is absolutely supported for distributed applications and should be considered to improve the overall performance of INSERT processing.
Significant performance improvements were measured at SVL when multiple inserts were preformed locally of remotely.

If you would like to read more about the performance studies performed by SVL for multi-row INSERT and FETCH along with the rest of the functionality delivered in Version 8, check out the Redbook DB2 UDB for z/OS Version 8 Performance Topics, SG24-6465.
This Redbook was first made available the second half of 2005.

One final thought.
There are a few APARs that will affect your successful implementation of multi-row INSERT and, to a lesser degree, multi-row FETCH.
Many are kind of old in DB2 Version 8 terms, but you need to make sure they are all applied.
A few are mentioned in the Performance Topics Redbook.

That's it for now
Thanks for stopping by
Willie