Fixes are available
DB2 Version 9.1 Fix Pack 7 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12 for Linux, UNIX and Windows
APAR status
Closed as program error.
Error description
An SQL statement that contains a tautology or true predicate that is ORed with an existential predicate may cause the DB2 instance to abend. For such an abend to occur, the following conditions must be satisfied: 1) The query contains a true local predicate (TLP) 2) The TLP is ORed with a correlated existential predicate (EP) 3) The fullselect of the EP refers to a common table expression (CTE) 4) The CTE refers to a table that is used as a source of correlation with 2 or more (not necessarily distinct) tables An example scenario follows: -- DDL CREATE TABLE T1 (C1 INT); CREATE TABLE T2 LIKE T1; CREATE TABLE T3 LIKE T1; CREATE TABLE T4 LIKE T1; -- Query WITH CTE (C1, C2) AS ( SELECT (SELECT C1 FROM T1 WHERE T3.C1 = T1.C1), (SELECT C1 FROM T4 WHERE T3.C1 = T4.C1) FROM T3 ) SELECT C1 FROM T2 WHERE (EXISTS (SELECT C1 FROM CTE C WHERE C.C1 = T2.C1) OR 1 = 1 ) ; The stack trace from the abend may look something like: UDT_colequiv_exp sqlnr_build_equiv sqlnr_theorem_prover sqlnr_qrwprep_phase2 sqlnr_exe sqlnn_cmpl sqlnn_cmpl ...
Local fix
If semantics permit, rewrite the problematic query to an equivalent form that does not have the 4 characteristics described above. Lowering the value of the CURRENT QUERY OPTIMIZATION special register to a value less than 5 will also circumvent the issue.
Problem summary
Users Affected: ALL Problem Description: An SQL statement that contains a tautology or true predicate that is ORed with an existential predicate may cause the DB2 instance to abend. For such an abend to occur, the following conditions must be satisfied: 1) The query contains a true local predicate (TLP) 2) The TLP is ORed with a correlated existential predicate (EP 3) The fullselect of the EP refers to a common table expressio (CTE) 4) The CTE refers to a table that is used as a source of correlation with 2 or more (not necessarily distinct) table An example scenario follows: -- DDL CREATE TABLE T1 (C1 INT); CREATE TABLE T2 LIKE T1; CREATE TABLE T3 LIKE T1; CREATE TABLE T4 LIKE T1; -- Query WITH CTE (C1, C2) AS ( SELECT (SELECT C1 FROM T1 WHERE T3.C1 = T1.C1), (SELECT C1 FROM T4 WHERE T3.C1 = T4.C1) FROM T3 ) SELECT C1 FROM T2 WHERE (EXISTS (SELECT C1 FROM CTE C WHERE C.C1 = T2.C1) OR 1 = 1 ) ; The stack trace from the abend may look something like: UDT_colequiv_exp sqlnr_build_equiv sqlnr_theorem_prover sqlnr_qrwprep_phase2 sqlnr_exe sqlnn_cmpl sqlnn_cmpl ... Problem Summary: See Problem Description.
Problem conclusion
First fixed in DB2 UDB Version 9.1, FixPak 7 (build s090308)
Temporary fix
see LOCAL FIX.
Comments
APAR Information
APAR number
JR30819
Reported component name
DB2 UDB EXE WIN
Reported component ID
5724E4901
Reported release
910
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2008-10-20
Closed date
2009-04-07
Last modified date
2009-04-07
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
DB2 UDB EXE WIN
Fixed component ID
5724E4901
Applicable component levels
R810 PSN
UP
R820 PSN
UP
R910 PSN
UP
R950 PSN
UP
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910"}]
Document Information
Modified date:
06 October 2021