IBM Support

PH02774: ABEND04E RC00C90101 AT DSNITLPE ERQUAL5002 FOR MERGE STATEMENT

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • ABEND04E RC00C90101 at DSNITLPE ERQUAL5002 for MERGE statement
    during sort processing.
    The abend was issued as Db2 try to insert into workfile a row
    with larger length than maxium defined length
    
    This issue may cause overlays that could generate various abend
    symptoms include but are not limited to the followings:
    
    ABEND04E RC00C90101 DSNIDM DSNISRTW ERQUAL5362
    ABEND0C4 RC00000038 DSNSLD4 DSNSVSFB OFFSET00758 (caller is
    DSNXSORI)
    ABEND04E RC00E2000D DSNXEUFP DSNSVSFB OFFSET00A18
    ABEND04E RC00E2000E DSNXSTM DSNSVSFB OFFSET00A00
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All Db2 12 for z/OS users of queries that                    *
    * contain the MERGE statement.                                 *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * An ABEND04E RC00C90101 at DSNITLPE                           *
    * ERQUAL5002 can occur for a MERGE                             *
    * statement during the phase 1 internal                        *
    * merge processing of sort when sort                           *
    * attempts to insert the data from one                         *
    * internal work file into another                              *
    * internal work file which happens to be                       *
    * smaller in size (data length).                               *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Apply corrective PTF when available                          *
    ****************************************************************
    An ABEND04E RC00C90101 at DSNITLPE ERQUAL5002 can occur
    when processing a MERGE statement. This occurs because during
    the phase 1 internal merge processing of sort, sort attempts to
    insert the data from one internal work file into another
    internal work file of smaller size (because of data length).
    
    Here is an example as to how the error may occur:
    
    Step ONE. Create tables and views.
    
      CREATE TABLE T1(C1 INT, C2 INT);
      CREATE TABLE T2(C1 INT, C2 INT);
    
      CREATE VIEW VW1_T1
        AS
       SELECT C1, C2
       FROM T1
       WHERE C1 < 10 WITH CHECK OPTION;
    
      CREATE VIEW VW1_T2
        AS
       SELECT C1, C2
       FROM T2;
    
    Step TWO. Insert rows into Table T1.
    
      INSERT INTO T1 VALUES(0,0);
      INSERT INTO T1 VALUES(1,1);
      INSERT INTO T1 VALUES(2,2);
    
    Step THREE. Insert thousands of rows into Table T2 which can
    cause sort to create multiple work files during the input
    phase.
    
      INSERT INTO T2 VALUES(0,1);
      INSERT INTO T2 VALUES(1,1);
      INSERT INTO T2 VALUES(3,3);
      INSERT INTO T2 VALUES(3,3);
      INSERT INTO T2 VALUES(3,3);
      ...
      INSERT INTO T2 VALUES(3,3);
      INSERT INTO T2 VALUES(2,2);
    
    Step FOUR. Execute the MERGE statement using VIEWs.
    
      SELECT C1,C2
      FROM FINAL TABLE(MERGE INTO VW1_T1 AS T(C1,C2)
                  USING VW1_T2 AS S(C1,C2)
                  ON T.C1 = S.C1
                  WHEN MATCHED THEN
                        UPDATE SET (C1,C2) = (T.C1+S.C1,S.C2)
                   WHEN NOT MATCHED THEN
                        INSERT(C1,C2) VALUES(C1,C2)
                     ) AS FT ;
    
    Step FIVE. Above MERGE statement may trigger ABEND04E
               RC00C90101 at DSNITLPE ERQUAL5002.
    

Problem conclusion

  • Db2 has been modified to use the proper routine to move data
    from one work file to another in case the new work file is
    smaller than the original in data length.
    
    Additional keywords: SQLMERGE SQLSORT
    

Temporary fix

Comments

APAR Information

  • APAR number

    PH02774

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    C10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2018-09-13

  • Closed date

    2018-10-04

  • Last modified date

    2018-11-28

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

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

    UI58903

Modules/Macros

  • DSNXSORI DSNXSMRE
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RC10 PSY UI58903

       UP18/10/19 P F810

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

Modified date: 28 November 2018