IBM Support

LI73431: IMPROPER TABLE ELIMINATION BY OPTIMIZER ON SPECIFIC CONDITIONS

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Here are the necessary conditions that customer query must have
    to hit this bug.
    1. Query has equality join predicates among at least fix
    tables, called T1, T2, T3, T4, T5, T6. The minimum set of
    join predicates is {P12, P23, P34, P45, P56}, where Pxy is
    predicate between Tx and Ty.
    2. There is no RI relationship between join columns of P12,
    P23.
    3. There are at least three hierarchical RI (Referential
    Integrity) relationship defined. These RI, RI34, RI45,
    RI56, cover join columns of P34, P45 and P56 respectively,
    and their primary key tables are T3, T4 and T5 respectively.
    The symptom is table T5 is gone in the optimized statement of
    query access plan.
    

Local fix

  • Workaround
    =========
    Reduce query optimization level to 0 or 1. For instance, "db2 se
    t current query optimization 1".
    

Problem summary

  • Here are the necessary conditions that customer query must have
    to hit this bug.
    1. Query has equality join predicates among at least fix
    tables, called T1, T2, T3, T4, T5, T6. The minimum set of
    join predicates is {P12, P23, P34, P45, P56}, where Pxy is
    predicate between Tx and Ty.
    2. There is no RI relationship between join columns of P12,
    P23.
    3. There are at least three hierarchical RI (Referential
    Integrity) relationship defined. These RI, RI34, RI45,
    RI56, cover join columns of P34, P45 and P56 respectively,
    and their primary key tables are T3, T4 and T5 respectively.
    The symptom is table T5 is gone in the optimized statement of
    query access plan.
    

Problem conclusion

  • Firsr fixed in Db2 V9.5 FP 2
    

Temporary fix

  • Reduce query optimization level to 0 or 1. For instance, "db2 se
    t current query optimization 1".
    

Comments

APAR Information

  • APAR number

    LI73431

  • Reported component name

    DB2 UDB WSE LIN

  • Reported component ID

    5765F3504

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-05-09

  • Closed date

    2009-10-08

  • Last modified date

    2009-10-08

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

    LI73430

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

Fix information

  • Fixed component name

    DB2 UDB WSE LIN

  • Fixed component ID

    5765F3504

Applicable component levels

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

Document Information

Modified date:
08 October 2009