A fix is available
APAR status
Closed as program error.
Error description
Description: The problem is when a same expression alias appears in the selection list multiple times and this query runs on parallelism, then a reset information for the multiple expression columns may be missed in the parallelism child task. Hence, cause the incorrect output. An example query looks like the following, the expression is a CASE expression: SELECT C1, C2, EXP1 AS CX, EXP1 AS CY, C3, ... FROM (SELECT C1, C2, CASE WHEN ... END AS EXP1, C3, ... FROM T1 ... WHERE ... ) AS TX WHERE .... The CASE expression is defined AS EXP1, and it appears in the selection list twice (AS CX and CY). It could be used inside any build in function (ie. SUBSTR(EXP1, 1, 3) AS CX) or select out directly. The incorrect out is the value of the expression EXP1 will always contain the first qualified value. The same value will be returned from the same child task. If multiple child tasks found qualified rows, then the different child task may return the different first qualified row value. For example, if child task 1 found the first qualified value of EXP1 is "ABC", then all the rows returned by child task 1 on the column EXP1 will be "ABC". If child task 2 found the first qualified value of EXP1 is "XYZ", then all the rows returned by child task 2 on the column EXP1 will be "XYZ". Keywords: SQLCASE DB2PARALL/K PARALLELISM SQLPARALLELISM
Local fix
Disable the parallelism: SET CURRENT DEGREE = '1';
Problem summary
**************************************************************** * USERS AFFECTED: * * All Db2 11 for z/OS and Db2 12 for z/OS * * users of parallelism. * **************************************************************** * PROBLEM DESCRIPTION: * * An incorrect output may occur when * * the following condition met: * * 1. a query selection list contains an * * expression with an AS clause and this * * AS name is referenced multiple times * * in the selection list * * 2. and the query runs on parallel. * **************************************************************** * RECOMMENDATION: * * Apply corrective PTF when available * **************************************************************** An incorrect output may occur when the following condition met: 1. a query selection list contains an expression with an AS clause and this AS name is referenced multiple times in the selection list (Note: this AS name may be referenced directly, or this AS name may be referenced in a SETFUNCTION or SCALAR FUNCTION, etc.). 2. and this query runs on parallel. When child task processing the expression, a re-set function is missing in the action. Hence, the child task only returns the first qualified value without re-set then re-evaluate the next row. The nature of the incorrect output is returning the same number of rows as correct output, but the selection list columns that referencing this AS name may contains incorrect value. Following is an example of such failing query: SELECT TX.CX, SUBSTR(TX.CX, 1, 5), TX.C1, ... FROM (SELECT C1, ..., CASE WHEN ... ELSE ... THEN .. END AS CX, ... FROM T1, ... WHERE ... ) AS TX Note: TX.CX is referenced twice in the selectin list and those two columns may return incorrect value. The same child task will reurn the same value within that child task, but each child task could return different value. For example, assuming 3 rows returned from child task 1 and the correct value is 'A1', 'A2', and 'A3'; 2 rows returned from child task 2 and the correct value is 'B1' and 'B2'. Then the incorrect output for the CX column could be: 'A1', 'A1', 'A1' (from child task 1) and 'B1', 'B1' (from child task 2).
Problem conclusion
Db2 has been modified to add the missing re-set function in the child task, so it will process the re-set and then re-evaluate the expression for each row. Additional Keywords: DB2PARALL/K PARALLELISM SQLPARALLELISM DB2INCORR/K INCORROUT SQLINCORR SQLINCORROUT SQLCASE
Temporary fix
Comments
APAR Information
APAR number
PH32690
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
C10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2020-12-15
Closed date
2021-02-18
Last modified date
2021-03-05
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI74052 UI74060
Modules/Macros
DSNXGTSK
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.
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0"}]
Document Information
Modified date:
06 March 2021