IBM Support

IT04394: INCORRECT QUERY RESULTS OR SQL0901N REASON "BAD OUTER COMPARE" POSSIBLE IN DPF WHEN SQL PLAN HAS MDTQ AND MERGE JOIN LOLEPOPS

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Due to a timing issue in DPF environments, a statement may fail
    with this error :
    
    SQL0901N The SQL statement failed because of a non-severe system
    error. Subsequent SQL statements can be processed. (Reason "bad
    outer compare".)
    
    The same conditions can however also lead to incorrect results
    being returned.
    
    This issue may occur only if the statement access plan involves
    a merge-join. Specifically, if the access plan has a MDTQ
    operator ( Merging Directed Table Queue ) on the outer (left)
    side of the merge-join (MSJOIN).
    
    In the FODC_AppErr that is generated, the stack file of the
    failing EDU
    has "sqlri_mj" before failure.
    There is a chance that other types of SQL0901 errors may occur
    because of this problem.
    

Local fix

  • The following setting disables a performance feature for MDTQ
    and avoids the issue, but it may cause some queries to run
    slower:
    
    db2set DB2_SPILL=NO_HINTS
    
    This change requires an instance recycle to take effect.
    Another option is to set the following registry value :
    db2set DB2_SORT_AFTER_TQ=YES
    this can also be supplied specific to the statement using an
    optimizer profile :
    <OPTGUIDELINES><REGISTRY><OPTION NAME='DB2_SORT_AFTER_TQ'
    VALUE='YES'/></REGISTRY></OPTGUIDELINES>
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Update to v10.1 Fix Pack 5.                                  *
    ****************************************************************
    

Problem conclusion

  • Fixed in v10.1 Fix Pack 5.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT04394

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2014-09-15

  • Closed date

    2015-07-14

  • Last modified date

    2016-09-02

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

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

    IT08456

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA10 PSN

       UP

  • RA50 PSN

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
02 September 2016