IBM Support

IT23875: WRONG RESULTS ARE POSSIBLE WHEN EXECUTING A SQL STATEMENT ELIGIBLE FOR ZIGZAG JOIN ON A NON-PARTITIONED INDEX

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • 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.
    

Local 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.
    

Problem summary

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

Problem conclusion

  • Please, See Error Description and install this fix.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT23875

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2018-01-29

  • Closed date

    2018-03-16

  • Last modified date

    2018-03-16

  • APAR is sysrouted FROM one or more of the following:

    IT23341

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSY

       UP

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 March 2018