DB2 FOR LUW / DB2FORLUW / A50 - DB2

Wrong results are possible when executing a SQL statement that
fits the required criteria for a zigzag join on a data
partitioned fact table, where the index used is not partitioned,
and partition elimination is eligible through a join predicate
between one or more dimension tables and the fact table.

Under these conditions, if you collect an EXPLAIN of the SQL
statement, you will see a ZZJOIN operator and at the fact table
index access you will see the following in the plan operator
details:


                DPESTFLG: (Number of data partitions accessed
are Estimated)
                        TRUE
                DPNUMPRT: (Number of data partitions accessed)
                        1


                DP Elim Predicates:
                ------------------
                Range 1)
                        Start Predicate: (Q1.DIM_ID = Q2.DIM_ID)
                        Stop  Predicate: (Q1.DIM_ID = Q2.DIM_ID)

Where DIM_ID can be any column joined between the dimension and
fact table and it doesn't need to exist in the fact table index
as long as there is at least one zigzag eligible join with that
dimension on one of the fact table's index key columns.

****************************************************************
* USERS AFFECTED:                                              *
* All                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Please, see error  Description and install this fix.         *
****************************************************************

You can avoid the problem by setting
DB2_REDUCED_OPTIMIZATION="ZZJN OFF".  This can be applied using
db2set or as a REGISTRY guideline. If using db2set, then static
sections should be recompiled.  In addition, if using the
-immediate option of db2set, the package cache must be flushed
to ensure re-compilation of the impacted SQL statements.

not known / see Local fix

forerunner  : 
follow-up : IT23874 IT23875 

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

30.11.2017
16.07.2018
16.07.2018