PM74173: INCORROUT CAN OCCUR WHEN USING OPTIMIZER HINTS FOR A SELECT WITH A CORRELATED SUBQUERY

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Incorrect output can occur for a query with a correlated
    subquery that is using optimizer hints to determine the best
    access path.
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: Users of DB2 for z/OS executing a            *
    *                 query that has a correlated subquery         *
    *                 using optimization plan hints or             *
    *                 APREUSE.                                     *
    ****************************************************************
    * PROBLEM DESCRIPTION: THE PROBLEM THIS FIX ADDRESSES HAS THE  *
    *                      FOLLOWING REPORTED SYMPTOMS:            *
    *                                                              *
    *                         INCORRECT OUTPUT(LESS ROWS FOUND)    *
    *                                                              *
    *                      PROBLEM SCENARIO:                       *
    *                                                              *
    *                      The reported incorrect output occurred  *
    *                      when executing a query that has a       *
    *                      correlated subquery while using         *
    *                      optimization plan hints.                *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    SUMMARY OF DB2 EVENTS:
    DB2 was attempting to choose a table from plan table hints to
    use for the desired access path. While searching, DB2
    picked an incorrect match for a correlated subquery
    which caused DB2 to generate output that had zero rows.
    This could also cause a problem when using APREUSE.
    

Problem conclusion

  • PROBLEM CONCLUSION:
    DB2 has been fixed to make the correct selection when
    searching optimization plan table hints for the best match
    when using a query with correlated subquery. With this fix,
    there is a small possibility that when optimization hints are
    specified for the subquery only and not the parent query block
    that some hints that worked in the past may no longer work,
    causing a +395 SQLCODE.  The +395 is issued to prevent exposure
    to incorrect output for queries with such hints specified.
    
    ADDITIONAL KEYWORDS:
    SQLINCORR INCORROUT SQLSUBQUERY SQLCORRSUBQ DB2INCORR/K APREUSE
    SQLINCORROUT OPTHINT OPTHINTS SQLCODE395
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PM74173

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-10-02

  • Closed date

    2012-12-17

  • Last modified date

    2013-01-02

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

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

    UK90452

Modules/Macros

  •    DSNXOPCO
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK90452

       UP13/01/01 P F212

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.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM74173

Modified date:

2013-01-02

Translate my page

Machine Translation

Content navigation