IBM Support

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

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

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

    IT23341

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A50

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-11-30

  • Closed date

    2018-07-16

  • Last modified date

    2018-07-16

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

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

    IT23874 IT23875

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

[{"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":"10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 July 2018