IBM Support

IC65082: IN DB2 V9.7, AUTOMATICALLY REWRITE OR PREDICATE IN SELECT DISTINCT QUERY INTO UNION OPERATOR

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • An OR predicate that involves a table join is evaluated as a
    Sargable predicate at runtime.
    
    The OR predicate can already be converted to UNION operator by
    DB2 query rewrite.  As result each
    OR subterm predicate can become efficient start-stop key
    predicate.
    
    Such rewrite requires certain conditions. One of them is the
    query must be SELECT with DISTINCT, and another is the OR
    hosting SELECT operator must have no join predicate between OR
    referenced tables and any other table.
    
    The latter condition is relaxed by this enhancement. An example
    is:
    
    SELECT DISTINCT ...
    WHERE
    T1.X = T2.X
    AND (
         T1.Y = T3.Y AND T2.Z=1
         OR T1.Y = T4.Y AND T4.Z = 1
    )
    
    The SELECT has a join predicate T1.X=T2.X, in which T1 is
    referenced by OR predicate
    but T2 is not. This enhancement enables the conversion from OR
    to UNION.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users with the mentioned query construction.             *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * An OR predicate that involves a table join is evaluated as   *
    * aSargable predicate at runtime.The OR predicate can already  *
    * be converted to UNION operatorby DB2 query rewrite.  As      *
    * result each OR subterm predicatecan become efficient         *
    * start-stop key predicate.Such rewrite requires certain       *
    * conditions. One of them is thequery must be SELECT with      *
    * DISTINCT, and another is the ORhosting SELECT operator must  *
    * have no join predicate betweenOR referenced tables and any   *
    * other table.The latter condition is relaxed by this          *
    * enhancement. Anexample is:SELECT DISTINCT ...WHERET1.X =     *
    * T2.XAND (T1.Y = T3.Y AND T2.Z=1OR T1.Y = T4.Y AND T4.Z =     *
    * 1)The SELECT has a join predicate T1.X=T2.X, in which T1     *
    * isreferenced by OR predicate but T2 is not. This             *
    * enhancementenables the conversion from OR to UNION.          *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to fix pack 3 or another release of DB2              *
    * whichcontains the fix.                                       *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in Version 9.7 Fix Pack 3.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC65082

  • 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

    2009-12-10

  • Closed date

    2010-09-27

  • Last modified date

    2010-09-27

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

    IC65078

  • 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

  • R820 PSN

       UP

  • R910 PSN

       UP

  • R950 PSN

       UP

  • R970 PSN

       UP

  • R980 PSN

       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:
27 September 2010