PM75571: QUERY MAY GET +395 (REASON '48') WHEN USING OPTIMIZATION HINTS IF THE QUERY REFERENCES THE SAME TABLE MORE THAN ONCE

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • A query using optimization hints could get +395 reason '48' if
    the query references the same table more than once.  (d152131)
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of DB2 10 for z/OS Optimization    *
    *                 Hints.                                       *
    ****************************************************************
    * PROBLEM DESCRIPTION: Optimization Hints could receive        *
    *                      SQL code +395 with reason code '48' or  *
    *                      other various reason codes even if      *
    *                      the hint matches the access path chosen *
    *                      by DB2 when no hint is used.            *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    Optimization hints could receive SQL code +395 with
    reason code '48' or various other reason codes even if
    the access path chosen by DB2 when no hints are applied is
    the same as the hint.  Optimization hints from DB2 V7 or older
    may have a different query block structure than newer releases
    of DB2.  Optimization hints that are read in will go through
    code that attempts to match the query blocks from the hint
    to the expected query block structure using DB2 10.  Two
    similar query blocks containing the same set of tables,
    the same number of child query blocks, and the same parent
    query blocks were mistakenly swapped in an attempt to
    match the hint, resulting in +395.
    
    Additional Keywords:
    OPTHINT OPTHINTS HINT SQLCODE395
    

Problem conclusion

  • Code is changed to first compare query blocks directly
    one-to-one before swapping any query block numbers.  If
    the query block numbers and general structures match
    directly, then the hint is used without changing any
    query block numbers.  The function of this code is to
    attempt to use hints when they are provided even when
    there is some ambiguity as to which query blocks should
    match up.  There is a small chance that this change
    could match the query blocks incorrectly since we cannot
    always determine definitively whether two query blocks are
    intended to match solely based on information available
    in the PLAN_TABLE.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PM75571

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-10-22

  • Closed date

    2013-04-19

  • Last modified date

    2013-06-04

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

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

    UK93629

Modules/Macros

  •    DSNXOPTJ
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK93629

       UP13/05/07 P F305

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

PM75571

Modified date:

2013-06-04

Translate my page

Machine Translation

Content navigation