IBM Support

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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PH01144

  • 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

    2018-08-01

  • Closed date

    2018-10-16

  • Last modified date

    2018-11-01

  • 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.



Document information

More support for: DB2 for z/OS

Software version: 11.0

Reference #: PH01144

Modified date: 01 November 2018