IBM Support

IC86716: PREDICATES MIGHT NOT BE PUSHED DOWN FOR QUERY containing UNION ALL and MULTIPLE nested SUBQUERIES

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Under rare scenarios, join predicates might not be pushed
    through a Union All if the following conditions are satisfied.
    
    1. The query contains 1 or more Union All.
    2. The Union All has a join to another table
    3. There are multiple subqueries present where 1 or more
    subqueries are complex. Subqueries are considered complex if
    they contain another subquery or have constructs like group by,
    outer join, Union All, etc.
    4. All the subqueries are either non-correlated or correlated to
    the same Union All.
    
    Ex. V1 is a view containing a basic UNION ALL :
    
    create view v1 as ( select * from tab1 where col1 = 0 union all
    select * from tab1 )
    
    -- Query:
    select distinct v1.col3
    from v1, tab2 t2
    where v1.col1 = t2.col1 and
       (
       exists ( select 1 from tab2 tt2 where tt2.col2 = v1.col2)
       )
       or
       (
       -- condition 3. Complex subquery
       exists ( select 1 from tab2 tt2 where tt2.col2 = v1.col1
                  and not exists ( select 1 from tab2 tt3  where
                  tt3.col3 = v1.col2))
       )
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * all                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * upgrade to version 9.7 fixpack 8                             *
    ****************************************************************
    

Problem conclusion

  • first fixed in version 9.7 fixpack 8
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC86716

  • 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

    2012-09-21

  • Closed date

    2013-04-01

  • Last modified date

    2013-04-01

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

    IV28417

  • 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



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC86716

Modified date: 01 April 2013