A fix is available
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 number
PI90126
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2017-11-13
Closed date
2017-12-04
Last modified date
2018-01-03
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
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
03 January 2018