A fix is available
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 number
PI22614
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
A10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2014-07-23
Closed date
2016-05-02
Last modified date
2017-01-03
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
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":"10.1","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":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
03 January 2017