PI22614: ABEND0C4 RC11 IN DSNXOP0 OR DSNXORFN OFFSET4ED8 OR ABEND04E RC00E70005 IN DSNXUTL M100

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• This APAR addresses the following abends:
  ABEND0C4 RC11 in DSNXOP0 at various offsets
  ABEND0C4 RC04 in DSNXORFN +4ED8
  ABEND04E RC00E70005 in DSNXUTL :M100
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and DB2 11 users of               *
  *                 the following functions:                     *
  *                                                              *
  *                 1. UNION ALL or materialized query table     *
  *                    (MQT) with AVG aggregate function         *
  *                                                              *
  *                 2. OLAP specifications with the              *
  *                    window-order-clause where the             *
  *                    sort-key-expression in the ORDER BY       *
  *                    clause has ASC NULLS FIRST or             *
  *                    DESC NULLS LAST                           *
  *                                                              *
  *                 3. XMLTABLE, UNNEST, or user-defined         *
  *                    functions.                                *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1. SQLCODE20478 can be mistakenly       *
  *                         issued if the statement contains     *
  *                         one of the following:                *
  *                                                              *
  *                           a. UNION ALL and an AVG aggregate  *
  *                              function in the subselect of    *
  *                              UNION ALL                       *
  *                                                              *
  *                           b. an AVG aggregate function and   *
  *                              the statement can be rewritten  *
  *                              to use a materialized query     *
  *                              table (MQT)                     *
  *                                                              *
  *                           c. an OLAP specifications with the *
  *                              window-order-clause where the   *
  *                              sort-key-expression in the      *
  *                              ORDER BY clause has ASC NULLS   *
  *                              FIRST or DESC NULLS LAST.       *
  *                                                              *
  *                      2. ABEND0C4 in DSNXORFN OFFSET515C when *
  *                      using DSN_VIRTUAL_KEYTARGETS.           *
  *                                                              *
  *                      3. ABEND04E in DSNXUTL:M100 when doing  *
  *                      a CREATE TRIGGER.                       *
  *                                                              *
  *                      4. ABEND0C4 in DSNXOP0 OFFSET0CC6 when  *
  *                      doing a SELECT from a view with a       *
  *                      table expression that doesn't have      *
  *                      a column name.                          *
  *                                                              *
  *                      5. ABEND04E 00C90101 in DSNITCUS 5002   *
  *                      when doing a CREATE VIEW.               *
  *                                                              *
  *                      6. ABEND0C4-00000011 in                 *
  *                      DSNXGRDS.DSNXOFD OFFSET6F20 can occur   *
  *                      on SQL that references XMLTABLE or      *
  *                      UNNEST.                                 *
  *                                                              *
  *                      7. Incorrect SQLCODE204 can occur on    *
  *                      SQL that references a user-defined      *
  *                      function.                               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  This apar addresses several potential problems.
  
  1. SQLCODE20478 can be mistakenly issued if the statement
     contains one of the following:
  
       a. UNION ALL and an AVG aggregate function in the
          subselect of UNION ALL
  
       b. an AVG aggregation function and the statement can be
          written to use an MQT
  
       c. an OLAP specifications with the window-order-clause
          where the sort-key-expression in the ORDER BY clause
          has ASC NULLS FIRST or DESC NULLS LAST
  
  
  2. ABEND0C4 in DSNXORFN OFFSET515C' when
  using DSN_VIRTUAL_KEYTARGETS.
  
  3. ABEND04E in DSNXUTL:M100 when doing
  a CREATE TRIGGER.
  
  4. ABEND0C4 in DSNXOP0 OFFSET0CC6 when
  doing a SELECT from a view with a
  table expression that doesn't have
  a column name.
  
  5. ABEND04E 00C90101 in DSNITCUS 5002
  when doing a CREATE VIEW.
  
  6. ABEND0C4-00000011 in DSNXGRDS.DSNXOFD OFFSET6F20 can occur
  on SQL that references XMLTABLE or UNNEST.
  
  7. Incorrect SQLCODE204 can occur on SQL that references a
  user-defined function.  The function does exist, but DB2
  incorrecly issues SQLCODE204.
  

Problem conclusion

• DB2 has been fixed to handle these types of problems.
  
  Additional Keywords:  SQLVIEW SQLTRIGGER SQLUNIONALL
                        SQLMQT SQLAVG SQLOLAP SQLWINDOW
                        SQLORDER SQLNULLSFIRST SQLNULLSLAST
                        SQLXMLTABLE SQLUNNEST SQLUDF
  

Temporary fix

Comments

• ×**** PE16/12/09 FIX IN ERROR. SEE APAR PI64711  FOR DESCRIPTION
  

APAR Information

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

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

  UI37474 UI37475

Modules/Macros

• DSNXOACL DSNXOFD  DSNXOOS2 DSNXOPDS DSNXOP0
  DSNXORFN DSNXOTDA DSNXOVC  DSNXOVD  DSNXOVM  DSNXOV1  DSNXOXRW
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI37474

     UP16/05/17 P F605

• RB10 PSY UI37475

     UP16/05/17 P F605

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.