DB2 FOR LUW / DB2FORLUW / 970 - DB2

Join pushdown through complex UNION ALL may not occur in DPF if 
any of the inputs to the UNION ALL are OUTER JOIN operators. 
Priori to DB2 version 9.7 fixpack 7, there is no capability for 
DB2 to 
automatically push the join below the UNION to get early 
filtering of the tables participating in join. 
 
To verify if you are affected, obtain the explain output from 
db2exfmt and look at the plan to determine if there are OUTER 
JOINs below the UNION ALL and if the filtering joins are left 
above the UNION ALL in the plan graph. Below there is execution 
plan snippet, where you can see situation in subject: 
 
                                 1.07441e+08 
                                     HSJOIN 
                                      (  12) 
                                  8.86317e+06 
                                  3.67748e+06 
                          /------------+-----------\ 
                  1.16703e+09                   116 
                     FILTER                         BTQ 
                      (13)                            (29) 
                 8.75289e+06                 7.71536 
                 3.67747e+06                      1 
                        |                                 | 
                 1.16704e+09                     29 
                     UNION                       TBSCAN 
                      (  14)                          (30) 
          8.32995e+06                  7.61313 
                 3.67747e+06                      1 
             /--------+--------\                    | 
  1.1099e+09         5.71335e+07         29 
  >^HSJOIN            >^HSJOIN   TABLE: SESSION 
      (15)                       (22)            ZZRF03 
 7.79279e+06             267330           Q16 
 3.45164e+06             225837 
 
 
In example above, result sets of outer joins ( >^HSJOIN ) 
operations are not filtered by pushing down join predicate on 
underlying tables. As a consequence cardinality is overestimated 
and plan is suboptimal. 
 
 
There are two parts required by DB2 to enable join pushdown 
through complex UNION ALL operator: 
 
1. Unique index must be defined on the session table. If unique 
index is created, looking at SESSION.ZZMQ01 in the db2exfmt 
output: 
 
 Schema: SESSION 
 Name:  ZZMQ01 
 Type:  Table 
   Time of creation:   2012-08-13-11.15.14.581620 
   ... 
   Distinct row values:   Yes 
   ... 
 
2. Setting the registry variable: 
db2set DB2_UNION_OPTIMIZATION=ENABLE_BT_PD_COMPLEX_UA=true

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.7 Fix Pack 7                        * 
****************************************************************

Manually rewrite the query so that the filtering joins are 
pushed into each arm of the UNION ALL (ex. it can be done by 
breaking up the query so that it is done as two parts, 
corresponding to each leg of the UNION ALL, and creating the 
view in conjunction with the join that couldn't be pushed down).

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
 

First fixed in version 9.7 Fix Pack 7

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC88340 
follow-up : 

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

25.06.2012
25.10.2012
03.01.2013

9.7.FP7