A fix is available
APAR status
Closed as program error.
Error description
ABEND04E RC00E70005 DSNXRBND M101 caused by query which predicate is on UNIQUE index and the group by contains same columns, one must have condition is there should be a COALESCE or VALUE function on one or several index column. For example: CREATE UNIQUE INDEX IX1 ON USER.TESTTB (C1 , C2, C3) ; SELECT C1, COALESCE (C3, C2) FROM USER.TESTTB WHERE C1='2015-01-01-00.00.00.000000' GROUP BY C1, COALESCE (C3, C2) ;
Local fix
N/A
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 10 and 11 for z/OS users of query * * statements that contain an equal predicate * * with a column (having a unique index) that * * is also included in a group by clause along * * with a scalar function. * **************************************************************** * PROBLEM DESCRIPTION: An ABEND04E RC00E70005 can occur at * * location DSNXGRDS DSNXRBND M101 for an * * SQL statement that contains an equal * * predicate and group by clause that * * includes the same column and a scalar * * function. * **************************************************************** * RECOMMENDATION: * **************************************************************** An ABEND04E RC00E70005 can occur at location DSNXGRDS DSNXRBND M101 for a query that contains an equal predicate on a column that has a unique index which is included in a GROUP BY clause along with a COALESCE function. This problem occurs because DB2 doesn't handle an internal format for a data type variable properly. The following query illustrates an example of failing case. 1. Define a table and an index on the table. CREATE TABLE USER.TESTTB (C1 TIMESTAMP NOT NULL ,C2 VARCHAR(255) ,C3 VARCHAR(255) ) ; CREATE UNIQUE INDEX USER.XDMP1 ON USER.TESTTB (C1 ASC) ; 2. Insert a row into the table. INSERT INTO USER.TESTTB VALUES('2015-01-01-00.00.00.000000', NULL,NULL); 3. Process the following query statement. SELECT C1, COALESCE (C3, C2) FROM USER.TESTTB WHERE C1='2015-01-01-00.00.00.000000' GROUP BY C1, COALESCE (C3, C2) ; Please note that the above mentioned abend can occur during the execution of this query statement. Also, the COALESCE function can be replaced by some other scalar function which returns a value.
Problem conclusion
DB2 has been modified to process the internal variable correctly which will avoid the abend. Additional Keywords: SQLGROUPBY SQLCOALESCE SQLSCALAR ABEND04E
Temporary fix
Comments
APAR Information
APAR number
PI41168
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2015-05-18
Closed date
2015-11-19
Last modified date
2016-01-04
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI33097 UI33098
Modules/Macros
DSNXGSGP
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:
04 January 2016