IBM Support

IC72243: Optimizer may choose a non-optimal plan for queries using FETCH FIRST ROWS ONLY caluse, which can cause performance degradation.

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Optimizer may choose a non-optimal plan for queries using FETCH
    FIRST ROWS ONLY (FFNR) caluse, which can lead to performance
    degradation. This plan causes a large stream of data to be
    broadcasted across partitions. Users will find this evident in
    the query access plan, which will show the BTQ operation
    broadcasting large stream of data as following:
    
                 /--------
           1.07181e+07
             BTQ
             (  13)
             125531
             31015.2
               |
           2.67952e+06
             IXSCAN
             (  14)
             123504
             31015.2
               |
           2.67952e+06
         INDEX: <name of the index>
    
    In this example, aprox 1.07x10^7 rows are being broadcasted
    across partitions. This leads to poor performance.
    

Local fix

Problem summary

  • USERS AFFECTED:
    ===============
    Customer using version 9.5 and above on all platforms.
    
    PROBLEM DESCRIPTION:
    ====================
    Queries using FIRST FIRST ROWS ONLY (FFNR) clause may experience
    performance problem after migrating from version 8 to version 9.
    The APAR can be confirmed by checking the query access graph. If
    a BTQ operation is seen broadcasting large stream of data, it
    may point to this APAR.
    
    RECOMMENDATION:
    ===============
    There is currently no workaround other than not using the FFNR
    clause.
    

Problem conclusion

  • Problem is first fixed in version 9.7 Fixpack 4.
    

Temporary fix

  • There is currently no workaround other than not using the FFNR
    clause.
    

Comments

APAR Information

  • APAR number

    IC72243

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

  • Closed date

    2011-05-03

  • Last modified date

    2011-05-03

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

    IZ85082

  • 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 #: IC72243

Modified date: 03 May 2011