IBM Support

PH01437: ABEND04E RC00C90101 AT DSNIDM DSNITCUS ERQUAL5002 MAY OCCUR ON A QUERY WITH HYBRID JOIN, ACCESSTYPE = 'O' AND WITH HOLD CURSOR

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • During Hybrid join RID phase, overflow happens due to reaching
    the end of intermediate table ( ACCESSTYPE = 'O'  in
    PLAN_TABLE) in memory, then ABEND04E RC00C90101 at DSNIDM
    DSNITCUS ERQUAL5002 may occur on a query having WITH HOLD
    cursor.
    Additional keywords:
    SQLHYBRIDJOIN SQLHYBRID ZSA3
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All Db2 12 for z/OS users of queries                         *
    * involving hybrid join that contain a table                   *
    * expression with a group by that is eligible                  *
    * to be rewritten to use correlated virtual                    *
    * table access.                                                *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * An ABEND04E rc00C90101 at DSNIDM                             *
    * DSNITCUS M5002 or an incorrect result                        *
    * of "less rows than expected" can be                          *
    * returned for a query that involves                           *
    * hybrid join and correlated virtual                           *
    * table access (indicated by access type                       *
    * 'O' in the plan table).                                      *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Apply corrective PTF when available                          *
    ****************************************************************
    An ABEND04E rc00C90101 at DSNIDM DSNITCUS M5002 can occur or an
    incorrect result (less rows than expected) can be returned for
    a query that involves hybrid join and correlated virtual table
    access (indicated by access type 'O' in the plan table).
    
    Furthermore,
    
    1. When CURSOR HOLD is enabled, it could encounter the ABEND04E
    rc00C90101 at DSNIDM DSNITCUS M5002.
    
    2. When CURSOR HOLD is not enabled, an incorrect result of
    "less rows than expected" could be returned.
    
    Here is an example query to help understand the problem.
    
      SELECT A.C1, A.C2, B.C2, C.C3
      FROM TAB_A AS A,
           (SELECT TAB_D.C1, TAB_E.C2
            FROM TAB_D
            JOIN TAB_E
            ON TAB_D.C1 = TAB_E.C1
            GROUP BY TAB_D.C1 ) AS B,
           TAB_C AS C
      WHERE A.C3 = 5
      AND   A.C1 = B.C1
      AND   A.C2 = C.C2;
    
    TAB_A and TAB_C are base tables while B is a table expression
    containing GROUP BY. A joins B first on A.C1 = B.C1, the result
    then joins C on A.C2 = C.C2. If join predicate push down
    happens when A joins B and correlated virtual table access is
    used for accessing B, and hybrid join is used to join C, the
    above mentioned abend or incorrect result of "less rows than
    expected" might happen.
    

Problem conclusion

  • Db2 code has been modified to correct the problem identified
    to prevent the above mentioned abend or incorrect result.
    
    Additional Keywords: SQLHYBRID SQLHYBRIDJOIN HYBRIDJOIN
                         DB2INCORR/K INCORROUT SQLINCORR
                         SQLINCORROUT SQLGROUPBY
    

Temporary fix

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

Comments

APAR Information

  • APAR number

    PH01437

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    C10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2018-08-07

  • Closed date

    2018-10-24

  • Last modified date

    2018-12-12

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

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

    UI59312

Modules/Macros

  • DSNXRGBJ DSNXRSFJ DSNXRT1J DSNXRFN  DSNXRSFN DSNXRSGB DSNXROJ1
    DSNXREOJ
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RC10 PSY UI59312

       UP18/11/08 P F811 ¢

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.



Document information

More support for: DB2 for z/OS

Software version: 12.0

Reference #: PH01437

Modified date: 12 December 2018