PH01144: INCORROUT FOR QUERY WITH MULTIPLE OLAPS SPECIFIED ORDER BY AND ABEND04E AT DSNXOWIN P020 MAY ISSUE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• INCORROUT FOR QUERY WITH MULTIPLE OLAPS SPECIFIED ORDER BY
  AND ABEND04E AT DSNXOWIN P020 MAY ISSUE
  
  We will provide some sample query in short time
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Db2 11 and 12 for z/OS users of          *
  *                 OLAP functions with GROUPING SETS,           *
  *                 ROLLUP, or CUBE.                             *
  ****************************************************************
  * PROBLEM DESCRIPTION: Two problems are fixed by this apar:    *
  *                      (1)                                     *
  *                      Incorrect output may be returned for    *
  *                      a query containing multiple OLAP        *
  *                      functions and GROUPING SETS,            *
  *                      ROLLUP, or CUBE.                        *
  *                      (2)                                     *
  *                      ABEND04E RC00E70005 at DSNXOWIN  P020   *
  *                      may occur for an SQL statement that     *
  *                      satisfies all of the following          *
  *                      conditions:                             *
  *                      1. The query references OLAP functions. *
  *                      2. The OLAP functions contain an ORDER  *
  *                      BY or PARTITION BY clause.              *
  *                      3.In the GROUP BY clause, there exists  *
  *                      GROUPING SETS, ROLLUP, or CUBE.         *
  *                      4. The column in the GROUP BY clause    *
  *                      is used in the ORDER BY or PARTITION BY *
  *                      clause of the OLAP functions.           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Example 1:
  
    SELECT   COALESCE(COLTYPE,'TOTAL')
             ,COUNT(*) OVER(PARTITION BY COLTYPE)
    FROM     SYSIBM.SYSCOLUMNS
    WHERE    TBCREATOR  =  'SYSIBM'
      AND    TBNAME     =  'SYSCOLUMNS'
      AND    COLNO     <=   10
    GROUP BY ROLLUP ((COLTYPE, COLNO));
  
  ABEND04E RC00E70005 AT DSNXOWIN P001 may occur for the statement
  above because Db2 did not process the PARTITION BY clause
  correctly.
  
  Example 2:
  
    SELECT   COLTYPE
            ,#ROWS
            ,ROW_NUMBER() OVER(ORDER BY COLTYPE ASC)
            ,ROW_NUMBER() OVER(ORDER BY COLTYPE DESC)
    FROM    (SELECT   COALESCE(COLTYPE,'TOTAL')  AS COLTYPE
                     ,COUNT(*)  AS #ROWS
             FROM     SYSIBM.SYSCOLUMNS CL1
             WHERE    TBCREATOR  =  'SYSIBM'
               AND    TBNAME  LIKE  'SYST%'
               AND    COLTYPE   >=  'S'
             GROUP BY ROLLUP((COLTYPE))
            )AS CL2
    ORDER BY COLTYPE ASC
    FOR FETCH ONLY
    WITH UR;
  
  Incorrect output may be returned for the statement above because
  Db2 did not process multiple OLAP functions with ROLLUP clause
  correctly.
  

Problem conclusion

• Db2 was modified to avoid the abend and the incorrect output
  above.
  
  Additional Keywords: SQLGROUPINGSET SQLGROUPBY SQLOLAP
                       INCORROUT  DB2INCORR/K SQLINCORR
                       SQLINCORROUT
  

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:

  UI59140 UI59144

Modules/Macros

•    DSNXOSUB DSNXOWIN
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI59144

     UP18/11/01 P F810 Ž

• RC10 PSY UI59140

     UP18/10/31 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.