PI90126: LOOP in DSNXXQS UK93068 OFFSET290 TO OFFSET308 WITH SQL STATEMENT USING ROLLUP CUBE XMLAGG GROUPING SETS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Endless loop in module DSNXXQS UK93068 OFFSET290 TO
  OFFSET308 SQL query uses ROLLUP/CUBE and XMLAGG / Grouping
  Sets
  
  Simplified SQL example:
  
  WITH FIRST(F11,F12)
  AS(
  SELECT 1, 'A'
       FROM SYSIBM.SYSDUMMY1
      UNION ALL
     SELECT 1, 'E'
       FROM SYSIBM.SYSDUMMY1
      UNION ALL
     SELECT 2, 'B'
       FROM SYSIBM.SYSDUMMY1
      UNION ALL
     SELECT 3, 'D'
       FROM SYSIBM.SYSDUMMY1
      UNION ALL
     SELECT 3, 'C'
       FROM SYSIBM.SYSDUMMY1
    )
  SELECT F11,
  CAST(
         EBCDIC_STR(
         STRIP(
         CAST(
         XMLSERIALIZE(
         XMLAGG(XMLTEXT(FIRST.F12 CONCAT ',')
                ORDER BY FIRST.F12)
         AS CLOB)
         AS VARCHAR(200))
         ,TRAILING,','))
         AS VARCHAR(200))
    FROM FIRST
    GROUP BY ROLLUP(F11)
  
  keywords:
  LOOPDSNXXQS
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All DB2 11 and DB2 12 for z/OS users of                      *
  * queries with an SQL statement requesting                     *
  * GROUPING SETS/ROLLUP/CUBE with an XMLAGG                     *
  * function.  Also, for DB2 12, included are                    *
  * GROUPING SETS/ROLLUP/CUBE with functions                     *
  * PERCENTILE_DISC or PERCENTILE_CONT with an                   *
  * XMLAGG function and/or a column that is                      *
  * variable length.                                             *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A tight loop in module DSNXXQS                               *
  * OFFSET290 to OFFSET308 or an                                 *
  * incorrect result can be returned                             *
  * when running anSQL statement                                 *
  * that requests GROUPING                                       *
  * SETS/ROLLUP/CUBE along with                                  *
  * the XMLAGG function.                                         *
  *                                                              *
  * In DB2 12, also possible is an                               *
  * ABEND04E rc00C90101 at                                       *
  * DSNIMOFR ERQUAL5006 if the                                   *
  * SQL statement contains a GROUPING                            *
  * SETS/ROLLUP/CUBE clause along with                           *
  * the PERCENTILE_CONT or                                       *
  * PERCENTILE_DISC function, and the                            *
  * XMLAGG function or a variable length                         *
  * column.                                                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  A tight loop in module DSNXXQS OFFSET290 to OFFSET308 can occur
  for an SQL statement that requests GROUPING SETS/ROLLUP/CUBE
  along with the XMLAGG function.  Also possible, an incorrect
  result can be returned from the same kind of query.
  
  In DB2 12, a possible ABEND04E rc00C90101 can occur at location
  DSNIMOFR ERQUAL5006 if the SQL statement uses GROUPING SETS/
  ROLLUP/CUBE along with the PERCENTILE_CONT or PERCENTILE_DISC
  function and the XMLAGG function or a variable length column.
  
  Below is an example query that can cause the looping condition.
  
  WITH FIRST(F11,F12)
  AS(
  SELECT 1, 'A' FROM SYSIBM.SYSDUMMY1
  UNION ALL
  SELECT 1, 'E' FROM SYSIBM.SYSDUMMY1
  UNION ALL
  SELECT 2, 'B' FROM SYSIBM.SYSDUMMY1
  UNION ALL
  SELECT 3, 'D' FROM SYSIBM.SYSDUMMY1
  UNION ALL
  SELECT 3, 'C' 3 FROM SYSIBM.SYSDUMMY1)
  SELECT F11,
  CAST(EBCDIC_STR(STRIP(CAST(
  XMLSERIALIZE(
  XMLAGG(XMLTEXT(FIRST.F12||',')
  ORDER BY FIRST.F12)
  AS CLOB) AS VARCHAR(200)) ,TRAILING,','))
  AS VARCHAR(200))
  FROM FIRST GROUP BY ROLLUP(F11) ORDER BY F11 DESC;
  
  
  The expected results for the above query are as follows.
  
  +------------------------------
  |      F11       |            |
  +------------------------------
  1_| ?              | A,B,C,D,E  |
  2_|              3 | C,D        |
  3_|              2 | B          |
  4_|              1 | A,E        |
  +------------------------------
  

Problem conclusion

• DB2 has been modified to prevent the looping condition and
  return the correct results as expected.
  
  Additional Keywords: SQLXMLAGG SQLGROUPINGSET SQLGROUPBY
                       SQLPERCENTILE LOOPDSNXXQS
                       SQLINCORR SQLINCORROUT DB2INCORR/K
  

Temporary fix

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

Comments

APAR Information

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

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

  UI52382 UI52384

Modules/Macros

• DSNXSIFG DSNXSOGS DSNXSINP DSNXSILA DSNXSIND DSNXSBUC DSNXSING
  DSNXSOPC DSNXSIGS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI52382

     UP17/12/20 P F712 ¢

• RB10 PSY UI52384

     UP17/12/20 P F712 ¢

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.