A fix is available
APAR status
Closed as program error.
Error description
In V10, EXITS predicate is allowed to be used in CASE statement, when there is EXITS predicate in the select list and predicate transitive closure is triggered, wrong accesspath may be generated.
Local fix
N/A
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 10 and 11 for z/OS user of SQL * * statement containing EXISTS predicate in * * WHEN clause of CASE expression. * **************************************************************** * PROBLEM DESCRIPTION: A non-optimal access path may be * * selected when an SQL statement * * satisfies all of the following * * conditions: * * * * 1. EXISTS predicate is used in WHEN * * clause of CASE expression; * * 2. multiple tables are referenced in * * FROM clause. * * 3. predicate transitive closure happens * **************************************************************** * RECOMMENDATION: * **************************************************************** A non-optimal access path may be selected when an SQL statement satisfies all of the following conditions: 1. EXISTS predicate is used in WHEN clause of CASE expression; 2. multiple tables are referenced in FROM clause. 3. predicate transitive closure happens. The following example helps to illustrate problem: SELECT CASE WHEN EXISTS (SELECT 1 FROM SYSIBM.SYSDUMMY1) THEN 1 ELSE 0 END AS COL1 FROM TAB1 AS A, TAB2 AS B, TAB3 AS C WHERE A.C1 = B.C1 AND B.C2. = C.C2 AND A.C1 = 11 AND B.C2 = 22; The join sequence or join method may not be optimal selected when EXISTS predicate appears in WHEN clause of CASE expression.
Problem conclusion
Code in DB2 has been correctly modified to handle EXISTS predicate in WHEN clause of CASE expression. Additional keyword: SQLCASE SQLEXISTS SQLACCESSPATH SQLPERFORMANCE
Temporary fix
* HIPER * *********
Comments
APAR Information
APAR number
PM90188
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
A10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt
Submitted date
2013-05-31
Closed date
2013-08-06
Last modified date
2013-09-03
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK96469 UK96470
Modules/Macros
DSNXOEXT DSNXOTCP
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 September 2013