IBM Support

PI49557: INEFFICIENT ACCESS PATH FOR QUERY WITH FUNCTION MIN OR MAX BY INCORRECT REDUCTION FOR EARLY OUT

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Inefficient access path for query with function MIN or MAX by
    incorrect reduction for early out
    

Local fix

  • OPTHINT
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 11 for z/OS users that have a query  *
    *                 having join with function MAX or MIN.        *
    ****************************************************************
    * PROBLEM DESCRIPTION: DB2 may select an inefficient access    *
    *                      path for a query having join with       *
    *                      function MAX or MIN.                    *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    The problem here is that DB2 underestimates the cost of the
    access path which could provide the order for column in the
    function MAX or MIN, and the query contains a join. Due to the
    incorrect costing of the access path, DB2 may select an
    inefficient access path.
    
    Here is an example query:
    
    SELECT MIN ( T1.C1 )
    FROM T1, T2
    WHERE T1.C2 = T2.C2
      AND T2.C1 = ?;
    
    There is an index IX1(C1) on table T1. DB2 may underestimate
    the cost of the access path which uses join sequence T1 to T2,
    and T1 uses index IX1 which could provide the order for T1.C1.
    
    Additional Keywords:
    SQLACCESSPATH  SQLMAX  SQLMIN
    

Problem conclusion

  • DB2 corrects the problem where it underestimates the cost of a
    query with MAX or MIN.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PI49557

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2015-09-30

  • Closed date

    2015-12-02

  • Last modified date

    2016-01-04

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

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

    UI33463

Modules/Macros

  • DSNXOPNR
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RB10 PSY UI33463

       UP15/12/19 P F512

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
04 January 2016