DB2 FOR LUW / DB2FORLUW / 970 - DB2

The optimizer will sometimes select a Temporary Table ("TEMP" 
operator) on the inner of a Nested Loop Join (NLJOIN) in order 
to improve the performance of a query. The TEMP is intended to 
cache a complex inner's result set, to save re-computing the 
results multiple times. This fix addresses the optimizer 
undercosting the TEMP when the inner,  and the contents of the 
TEMP, can change often due to correlation that extends below the 
TEMP and into one or more joins that create that TEMP. This is 
only true for TEMPs that appear immediately on the inner of a 
NLJOIN.  This would be shown in db2exfmt output as: 
 
              NLJOIN 
             /      \ 
      operator(s)   TBSCAN(3) 
          .           | 
          .         TEMP 
          .           | 
        SCAN(1)    operators 
         Q1          . . . 
                     SCAN(2) 
                      Q2 
 
In addition, there would be one or more predicates on operator 
"SCAN(2)" that reference the table in operator "SCAN(1)". For 
example: "Q1.Column1 = Q2.Column5". Predicates of this form are 
referred to as correlated predicates. The correlated predicates 
need to appear below the TEMP, and not in "TBSCAN(3)" above the 
TEMP. Correlated predicates in "TBSCAN(3)" are common and 
expected on TEMPs on the inner of a NLJOIN that does not have 
correlation extending below the TEMP. 
 
To enable this fix, please set the following DB2 registry 
variable: 
 
 db2set DB2_EXTENDED_OPTIMIZATION=NLJN_CORR_TEMP 
 Recycle the DB2 instance (db2stop/db2start)

**************************************************************** 
* USERS AFFECTED:                                              * 
* All DB2 Linux Unix Windows v9.7 and up users with complex    * 
* correlated subqueries or Stored Procedures in their SQL.     * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* The optimizer will sometimes select a Temporary Table        * 
* ("TEMP" operator) on the inner of a Nested Loop Join         * 
* (NLJOIN) in order to improve the performance of a query. The * 
* TEMP is intended to cache a complex inner's result set, to   * 
* save re-computing the results multiple times. This fix       * 
* addresses the optimizer under costing the TEMP when the      * 
* inner, and the contents of the TEMP, can change often due to * 
* correlation that extends below the TEMP and into one or more * 
* joins that create that TEMP. This is only true for TEMPs     * 
* that appear immediately on the inner of a NLJOIN.  This      * 
* would be shown in db2exfmt output as:                        * 
*                                                              * 
*               NLJOIN                                         * 
*             /      \                                         * 
*       operator(s)  TBSCAN(3)                                 * 
*           .          |                                       * 
*           .        TEMP                                      * 
*           .          |                                       * 
*         SCAN(1)    operators                                 * 
*         Q1          . . .                                    * 
*                     SCAN(2)                                  * 
*                       Q2                                     * 
*                                                              * 
* In addition, there would be one or more predicates on        * 
* operator "SCAN(2)" that reference the table in operator      * 
* "SCAN(1)". For example: "Q1.Column1 = Q2.Column5".           * 
* Predicates of this form are referred to as correlated        * 
* predicates. The correlated predicates need to appear below   * 
* the TEMP, and not in "TBSCAN(3)" above the TEMP. Correlated  * 
* predicates in "TBSCAN(3)" are common and expected on TEMPs   * 
* on the inner of a NLJOIN that does not have correlation      * 
* extending below the TEMP.                                    * 
*                                                              * 
* To enable this fix, please set the following DB2 registry    * 
* variable:                                                    * 
*                                                              * 
* db2set DB2_EXTENDED_OPTIMIZATION=NLJN_CORR_TEMP              * 
* Recycle the DB2 instance (db2stop/db2start)                  * 
*                                                              * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7, Fixpack 5, and enable the fix by * 
* using: db2set DB2_EXTENDED_OPTIMIZATION=NLJN_CORR_TEMP       * 
****************************************************************

Use the following DB2 registry as a workaround: 
DB2_REDUCED_OPTIMIZATION=NO_CORR_NLJN

DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 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 first in DB2 for LUW Version 9.7 Fixpack 5. The 
Correlated TEMP on the inner of the Nested Loop Join will 
no longer appear in the plan.

not known / see Local fix

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

23.03.2011
03.01.2012
03.01.2012

9.7.FP5,
9.7.FP5