IBM Support

IC87185: QUERY CONTAINING DUPLICATE BRANCHES MIGHT CAUSE PERFORMANCE DEGRADATION

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • DB2 has a component called Query ReWrite (QRW) which rewrites
    the query to improve query performance.
    One of the optimizations done is to find common expressions and
    tables and combine them to save on processing and I/O costs.
    Under rare scenarios, this can prevent some subsequent
    optimizations from making progress and cause performance
    degradation.
    
    eg.
    CREATE VIEW v1 AS SELECT t3.* FROM t3 INNER
    JOIN (SELECT c1 FROM t5 WHERE t5.c1=APPLICATION_ID()
    and c2 > 0) tmp ON tmp.c1 = t3.c1;
    
    ALTER VIEW v1 ENABLE QUERY OPTIMIZATION;
    
    with cte1 as ( select t1.* from t4, t1 left join (t2 join v1 on
    t2.c1 = v1.c1) on t1.c2 = t2.c2 and t1.c1 is null where t4.c1 =
    t1.c1),
    cte2 as ( select t1.* from t1 left join (t2 join v1 on t2.c1 =
    v1.c1) on t1.c2 = t2.c2 and t1.c1 is null)
    select o.cnt old_cnt, n.cnt new_cnt, o.cnt - n.cnt diff from
    (select count(1) cnt from cte1) o
    join
    (select count(1) cnt from cte2) n
    on 1=1;
    

Local fix

  • db2set DB2COMPOPT=8716421 and restart instance
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Problem Description above.                               *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 9.7 Fix Pack 9.                       *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 Version 9.7 Fix Pack 9.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC87185

  • 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-10-12

  • Closed date

    2013-12-17

  • Last modified date

    2013-12-17

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

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

    IC87186 IC96746

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC87185

Modified date: 17 December 2013