IBM Support

LI73162: Possible Poor Performance of Queries with FFNR/OFNR and DISTINCT

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • In rare circumstances a suboptimal query access plan may be
    selected for queries with all of the following properties:
    
    -         The query includes a FETCH FIRST N ROWS or OPTIMIZE
    FOR N ROWS clause
    
    -         The query does not include an ORDER BY or GROUP BY
    
    -         The query includes one DISTINCT clause
    
    -         The DISTINCT includes columns from multiple tables
    
    -         The join that links these tables is on at least one of
    these columns
    
    -         An index exists on one of the columns in the join and
    the DISTINCT.
    
    -         The order provided by the index is only useful in the
    join and in the DISTINCT
    
    -         On version 9.1 and below, the optimization level is 5
    or below. On version 9.5 and above, the optimization level is 7
    or below.
    

Local fix

  • This APAR provides a configuration setting which fixes this
    issue. Due to the risks involved, customers who believe they
    have encountered the issue described above should engage DB2
    Support.
    

Problem summary

  • Possible Poor Performance of Queries with FFNR/OFNR and DISTINCT
    

Problem conclusion

  • First fixed in DB2 UDB Version 9.5, FixPak 2
    

Temporary fix

  • This APAR provides a configuration setting which fixes this
    issue. Due to the risks involved, customers who believe they
    have encountered the issue described above should engage DB2
    Support.
    

Comments

APAR Information

  • APAR number

    LI73162

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-02-14

  • Closed date

    2008-11-04

  • Last modified date

    2008-11-04

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

    LI73160

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

Fix information

  • Fixed component name

    DB2 UDE ESE LIN

  • Fixed component ID

    5765F4104

Applicable component levels

  • R950 PSY

       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":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
04 November 2008