PM81848: INCORROUT (OUT OF ORDER BY SEQUENCE) ON QUERY WITH ORDER-BY AND PLAN CHOOSING HYBRID JOIN WITH SORTN_JOIN=N

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • The output records may not be in the right Order By sequence
    on query with Order-by clause.  In such cases, it's Hybrid
    join plan with SORTN_JOIN=N being chosen.
    
    Additional Keywords: SQLINCORR SQLINCORROUT DB2INCORR/K
               SQLHYBRID SQLHYBRIDJOIN HYBRIDJOIN SQLORDERBY
    

Local fix

  • Use HINT or add OPTIMIZE FOR 1 ROW to force an access plan
    other than Hybrid join.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of DB2 10 for z/OS who have        *
    *                 queries containing ORDER BY, GROUP BY, or    *
    *                 set function DISTINCT with an index matching *
    *                 the ORDER BY, GROUP BY, or set function      *
    *                 DISTINCT columns.                            *
    ****************************************************************
    * PROBLEM DESCRIPTION: Possible incorrect output for a         *
    *                      query containing ORDER BY, GROUP BY,    *
    *                      or set function DISTINCT when hybrid    *
    *                      join is chosen with SORTN_JOIN=N, an    *
    *                      index matches the ORDER BY, GROUP BY,   *
    *                      or set function DISTINCT columns, and   *
    *                      no extra sort is added to handle this   *
    *                      sorting.                                *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    Incorrect output may occur for a query containing ORDER BY,
    GROUP BY, or set function DISTINCT when hybrid join is chosen
    with SORTN_JOIN=N, an index matches the ORDER BY, GROUP BY, or
    set function DISTINCT columns, and no extra sort is added
    to handle this sorting.
    
    The chosen access path included a RID list scan which did
    not preserve the order provided by the index.
    
    Additional Keywords:
    INCORROUT SQLINCORROUT SQLINCORR DB2INCORR/K SQLORDERBY
    SQLGROUPBY SQLDISTINCT SQLHYBRID SQLHYBRIDJOIN HYBRIDJOIN
    

Problem conclusion

  • Code is added to include the extra sort in the situation
    described above.  If the estimated cost of the new access path
    with the extra sort is higher than a different access path, an
    access path without hybrid join may be chosen.  An access
    path with an extra sort may be chosen if it is determined that
    the sort is needed to prevent incorrect output.
    

Temporary fix

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

Comments

APAR Information

  • APAR number

    PM81848

  • 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

    2013-01-31

  • Closed date

    2013-04-14

  • Last modified date

    2013-05-06

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

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

    UK93437 UK93438

Modules/Macros

  •    DSNXOTS1
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK93437

       UP13/04/30 P F304

  • R910 PSY UK93438

       UP13/04/30 P F304

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

PM81848

Modified date:

2013-05-06

Translate my page

Machine Translation

Content navigation