IBM Support

IC71402: SQL COMPILER CAN TAKE LONG TIME TO PROCESS DATA PARTITION ELIMINATION WHEN MANY JOINS ARE MADE ON A DATA PARTITION KEY

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • The DB2 optimizer may take a long time to compile or exhaust
    statement heap if
    the query has a large number of table joins on one common column
    which is also
    the range partitioning column. For example, consider the
    following SQL where all the
    base tables are range partitioned on column A.
    
    select T1.A, T2.B, T3.C, T4.D, T5.E, T6.F, T7.G, T8.H, T9.I,
    T10.J
    from T1,T2,T3,T4,T5,T6,T7,T8,T9,T10
    where T1.A = T2.A and T2.A = T3.A and T3.A = T4.A and T4.A =
    T5.A
    and T5.A = T6.A and T6.A = T7.A and T7.A = T8.A and T8.A = T9.A
    and T9.A = T10.A;
    
    The optimizer will generate all the implied join predicates,
    i.e. T1.A = T3.A, T1.A = T4.A and etc.,
    and all these predicates can be used to perform data partition
    elimination, as a result it may
    take a long time to compile.
    

Local fix

  • rewrite the join predicates to prevent generating implied
    predicates, for example T1.A = T2.A can be rewritten to
    T1.A = (T2.A + 0).
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * same as error description                                    *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * This issue will be addressed in version 9.7                  *
    * Please review version 9.7 corresponding APAR IC71402 for     *
    * further information.                                         *
    ****************************************************************
    

Problem conclusion

  • This issue is fixed in version 9.7 Fixpack 5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC71402

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-09-22

  • Closed date

    2011-12-13

  • Last modified date

    2011-12-13

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

    IC70860

  • 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

  • R970 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
13 December 2011