IBM Support

IZ85082: 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.5 Fixpack 8.
    

Temporary fix

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

Comments

APAR Information

  • APAR number

    IZ85082

  • Reported component name

    DB2 UDB ESE SOL

  • Reported component ID

    5765F4102

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-09-16

  • Closed date

    2011-07-21

  • Last modified date

    2011-07-21

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

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

    IC72243

Fix information

  • Fixed component name

    DB2 UDB ESE SOL

  • Fixed component ID

    5765F4102

Applicable component levels

  • R950 PSY

       UP

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950"}]

Document Information

Modified date:
07 October 2021