Blog
DB2utor

Categories

August 06, 2007

MERGE Statement Simplifies Batch Processing and Improves Performance

Recently on the DB2-L list serve someone asked the following question: “I have a batch job that reads in a record that will either be inserted or updated on a DB2 table. Should I SELECT from the table first to see if the row exists? If it exists then do the UPDATE otherwise do the INSERT.” My answer is to DO the INSERT and if you get the sqlcode -803 due to a duplicate row then do the UPDATE. This goes back to the 80/20 rule. If 80 percent of the time the row will need to be inserted then do the INSERT first, otherwise do the UPDATE first. With DB2 V9 you now have the option of letting DB2 make the decision for you. You pass MERGE one or more rows and it will do the INSERT or UPDATE processing. This not only simplifies the code since you don’t have to program all of the IF THEN ELSE statements to handle the different conditions, but it also greatly improves the performance since you can pass an ARRAY of rows in one pass to DB2 to be processed.

To test my theory, I wrote three simple programs. I ran these three programs in three separate tests. The first test is 100-percent INSERT, the second test is 100-percent UPDATE and the third test is 50/50 INSERT/UPDATE.

All runs with 100,000 input records.
Job 1 through 4 100% INSERT processing (deleted 100 percent of rows to force INSERT)
Job 5 through 8 100% UPDATE processing (left all rows on the table to force UPDATE)
Job 9 through 12 50% Insert, 50% Update (deleted 50 percent of rows before each test)

Job 1, 5, 9    - SELECT, INSERT or UPDATE
Job 2, 6, 10  - INSERT, if duplicate SQLCODE -803 then UPDATE
Job 3, 7, 11  - MERGE with array size of 1,000
Job 4, 8, 12  - MERGE with array size of 10,000

The following output shows the elapse time and CPU for each job. Notice the decrease in job CPU. The Merge job4 has a 37-percent CPU savings over INSERT/DELETE job3 and 61-percent savings over SELECT, INSERT, UPDATE job1.
Based on these number use MERGE but if you're not on V9 then do INSERT/DELETE.

  SoftBase Systems       Batch Analyzer Version 3.1.0                         
                   === SQL Performance Analysis (Jobs) ===                     
  Command ===>                                                 Scroll ===> PAGE
  Filter: ON  Intervals: ON  From: 07/24/07 To: 07/27/07 Loctn: DB8A          
                                                                             ->
                       +--- RUN TIME ---+---CPU UTILIZED--+                   
   CSBPJOB*  Job Start |   Job    DB2 % |    Job    DB2 % |   DB2 CPU%   Total
   Jobname   Timestamp | Elapsed of Et  |    CPU   of CPU | I all Jobs DB2 Cost
   -------- ----------- -------- ------   -------- ------  -- -------- --------
_ CSBPJOB1 07/26 17:32 00:14:29   63.3   00:12:13   62.7   1      6.3 $ 459.98
_ CSBPJOB2 07/26 17:54 00:09:22   69.0   00:07:36   68.2   1      4.3 $ 311.21
_ CSBPJOB3 07/26 18:10 00:05:59   97.3   00:04:49   98.8   1      3.9 $ 285.87
_ CSBPJOB4 07/26 18:17 00:06:01   97.8   00:04:48   99.0   1      3.9 $ 285.99

_ CSBPJOB5 07/26 18:25 00:13:06   59.1   00:11:01   58.5   1      5.3 $ 387.22
_ CSBPJOB6 07/26 19:22 00:15:49   65.7   00:13:21   64.8   1      7.2 $ 519.23
_ CSBPJOB7 07/26 19:39 00:03:51   97.5   00:03:11   98.5   1      2.6 $ 188.35
_ CSBPJOB8 07/26 19:44 00:03:37   97.7   00:03:03   98.5   1      2.5 $ 180.79

_ CSBPJOB9 07/26 22:36 00:15:25   63.9   00:12:57   63.4   2     26.9 $ 493.56
_ CSBPJOBA 07/26 22:58 00:32:33   60.1   00:18:06   65.2   2     38.7 $ 709.58
_ CSBPJOBB 07/26 23:41 00:08:50   98.0   00:05:38   99.0   2     18.3 $ 335.07
_ CSBPJOBC 07/26 23:55 00:08:19   97.9   00:05:31   99.1   2     15.2 $ 328.68

I wrote these programs in COBOL and I have to say you have to read the Application Programming & SQL Guide to figure out the requirements for a Host-Variable-Array when using multi-row processing. The first thing you have to remember is that the array doesn't contain a row but instead contains a column. So if you have 200 columns on the table, you'll have 200 arrays, one for each column. If these columns can contain NULL then you need an indicator array for each column. The following is an example of host variable definitions in COBOL. WARNING: You must follow this format. Read the book!

*****************************************************************
* Table Column Arrays                                           *
*****************************************************************
01  WS-EMP-ARRAY.                                          
     05  WS-EMPNO         PIC X(06)  OCCURS 10000 TIMES.   
     05  WS-FIRSTNME                 OCCURS 10000 TIMES.   
         49 WS-FIRSTNME-LEN      PIC S9(4) USAGE COMP SYNC.
         49 WS-FIRSTNME-TEXT     PIC X(12).                
     05  WS-MIDINIT       PIC X(01)  OCCURS 10000 TIMES.   
     05  WS-LASTNAME                 OCCURS 10000 TIMES.   
         49 WS-LASTNAME-LEN      PIC S9(4) USAGE COMP SYNC.
         49 WS-LASTNAME-TEXT     PIC X(15).                
     05  WS-WORKDEPT      PIC X(03)  OCCURS 10000 TIMES.   
     05  WS-PHONENO       PIC X(04)  OCCURS 10000 TIMES.   
     05  WS-HIREDATE      PIC X(10)  OCCURS 10000 TIMES.   
     05  WS-JOB           PIC X(08)  OCCURS 10000 TIMES.

*****************************************************************
* INDICATOR ARRAY                                               *
*****************************************************************
01  WS-EMPNO-IND-ARRAY.                                          
     05  WS-WORKDEPT-IND  PIC S9(04) COMP                        
                                 OCCURS 10000 TIMES VALUE ZEROES.
     05  WS-PHONENO-IND   PIC S9(04) COMP                        
                                 OCCURS 10000 TIMES VALUE ZEROES.
     05  WS-HIREDATE-IND  PIC S9(04) COMP                        
                                 OCCURS 10000 TIMES VALUE ZEROES.
     05  WS-JOB-IND       PIC S9(04) COMP                        
                                 OCCURS 10000 TIMES VALUE ZEROES.

Sample MERGE Statement. I use alias “E” for the EMP table and “IT” for the intermediate table holding the host variables.
EXEC SQL                                             
  MERGE INTO EMP AS E                                 
      USING ( VALUES (  :WS-EMPNO                     
                      , :WS-FIRSTNME                  
                      , :WS-MIDINIT                  
                      , :WS-LASTNAME                  
                      , :WS-WORKDEPT:WS-WORKDEPT-IND 
                      , :WS-PHONENO:WS-PHONENO-IND   
                      , :WS-HIREDATE:WS-HIREDATE-IND 
                      , :WS-JOB:WS-JOB-IND            
                      , :WS-EDLEVEL:WS-EDLEVEL-IND   
                      , :WS-SEX:WS-SEX-IND            
                      , :WS-BIRTHDATE:WS-BIRTHDATE-IND
                      , :WS-SALARY:WS-SALARY-IND      
                      , :WS-BONUS:WS-BONUS-IND       
                      , :WS-COMM:WS-COMM-IND         
                     )                               
             FOR 1000 ROWS)                           
  AS IT ( EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT
        , PHONENO, HIREDATE, JOB, EDLEVEL, SEX
        , BIRTHDATE, SALARY, BONUS, COMM                      
       )                            
ON (E.EMPNO = IT.EMPNO)             
WHEN MATCHED THEN UPDATE SET         
             FIRSTNME  = IT.FIRSTNME, MIDINIT   = IT.MIDINIT 
           , LASTNAME  = IT.LASTNAME, WORKDEPT  = IT.WORKDEPT
           , PHONENO   = IT.PHONENO , HIREDATE  = IT.HIREDATE
           , JOB       = IT.JOB     , EDLEVEL   = IT.EDLEVEL 
           , SEX       = IT.SEX     , BIRTHDATE = IT.BIRTHDATE
           , SALARY    = IT.SALARY  , BONUS     = IT.BONUS   
           , COMM      = IT.COMM    
WHEN NOT MATCHED THEN          
  INSERT (  EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT            
          , PHONENO, HIREDATE, JOB, EDLEVEL, SEX                
          , BIRTHDATE, SALARY, BONUS, COMM               
         )                     
    VALUES (  IT.EMPNO, IT.FIRSTNME, IT.MIDINIT, IT.LASTNAME
            , IT.WORKDEPT, IT.PHONENO, IT.HIREDATE, IT.JOB         
            , IT.EDLEVEL, IT.SEX, IT.BIRTHDATE, IT.SALARY      
            , IT.BONUS, IT.COMM         
           )                  
   NOT ATOMIC CONTINUE ON SQLEXCEPTION QUERYNO 61000
END-EXEC.