DB2 FOR LUW / DB2FORLUW / 970 - DB2

A MERGE statement might run slower if all the conditions below 
are met: 
 
1. There is only one matching-condition (WHEN clause). 
2. The modification-operation (THEN clause) is an UPDATE 
operation. 
3. The search-condition (ON clause) is join that involves an 
expression. 
    The expression might not be seen as an expression at the ON 
clause 
    but could derive from the operation below it (please see the 
example below.) 
4. There is no index on the join column from the target table. 
 
An example is: 
 
MERGE INTO <tgt-table> T 
  USING ( 
    SELECT <expression> as C1, ... 
    FROM    <src-table> S 
    GROUP BY  <expression> 
  ) ON S.C1 = T.<col> 
WHEN MATCHED 
THEN 
UPDATE SET ... 
 
If there is no index on column T.<col> and the join involves 
expressions, the join between S and T will choose a NLJOIN plan 
with a TBSCAN on T. 
 
This potential performance regression is observed when upgrade 
DB2 from previous releases or from version 9.7 Fix Pack 5 to 
version 9.7 Fix Pack 6 or later. Prior to version 9.7 Fix Pack 
6, an alternate access plan using MSJOIN could be chosen.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All Users                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V97FP9 or high version                        * 
****************************************************************

Create a index on T.<col> to add an IXSCAN on T as an alternate 
choice for the NLJOIN between S and T.

DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Fixed on DB2 V97FP9

Create a index on T.<col> to add an IXSCAN on T as an alternate 
choice for the NLJOIN between S and T.

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

24.07.2013
16.12.2013
16.12.2013

9.7.FP9