PH58403: ABEND04E 00C90101 IN DSNISRTW 5064

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Running complex query with multiple DISTINCT operators
  ABEND04E RC00C90101 in DSNISRTW ERQUAL5064 can occur on
  workfile table-space. When Db2 built the final workfile, it
  needs to reset the SRTINMEM bit if it was set when doing the
  final merge of distincts when building the final workfile to
  return. The DM in-memory workfile was fine for the other
  multiple distincts since it fit within 32K; but when combined
  together for the final workfile, it goes over 32K which would
  cause the abend in DSNISRTW.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 13 for z/OS users of queries that                    *
  * contain multiple distincts or multiple                       *
  * LISTAGGs in their query.                                     *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A possible ABEND04E 00C90101 may occur                       *
  * in DSNISRTW ERQUAL5064 when there are                        *
  * multiple distincts or multiple                               *
  * LISTAGGs in the query.                                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  A possible ABEND04E 00C90101 may occur in DSNISRTW ERQUAL5064
  when there are multiple distincts or multiple LISTAGGs in the
  query.
  As sort goes through each distinct or LISTAGG, depending on the
  number of rows and key size for each, there could be a scenario
  where the last set of results can be placed in an in-memory
  workfile and then when sort processes the final merge of all
  the distincts or LISTAGGs, it may not be able to fit in an
  in-memory workfile.  The problem is that sort did not reset the
  flag and recalculate for this in-memory workfile usage.
  Below is an example that can illustrate the possible error.
  1. First create table and insert 23 rows as follows
  CREATE TABLE TBL1
     (C1       INT,
      C2       INT,
      C3       VARCHAR(820));
  COMMIT;
  INSERT INTO TBL1 VALUES (20, 20, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (19, 19, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 18, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 17, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 16, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 15, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 14, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 13, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 12, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 11, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 10, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 9, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 8, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 7, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 6, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 5, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 4, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 3, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 2, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 1, 'R'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (20, 20, 'T'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (20, 19, 'S'||REPEAT('T',819));
  INSERT INTO TBL1 VALUES (18, 18, 'Z'||REPEAT('T',819));
  COMMIT;
  2. Run query with multiple distincts
  SELECT COUNT(DISTINCT C3),
         COUNT(DISTINCT C1), SUM(DISTINCT C1)
  FROM TBL1 GROUP BY C2;
  3. During the sort process, as it calculates the number of rows
  and the size of the data and key for the first distinct (C3),
  sort determines that the result will not fit in-memory.  But as
  sort processes the second set of distincts (C1), this will fit
  in an in-memory workfile.  And, finally when it needs to
  combine these results together, it realizes it can't use an
  in-memory workfile and would have abended because it didn't
  reset the flag and recalculate.
  

Problem conclusion

• Db2 has been modified to reinitialize the in-memory flag and
  recalculate during the final merge process to see if an
  in-memory workfile can be used or not.
  Additional keywords: LISTAGG
  

Temporary fix

Comments

APAR Information

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

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

  UI95239

Modules/Macros

• DSNXSOLA DSNXSORM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RD10 PSY UI95239

     UP24/01/24 P F401

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.