APAR status
Closed as fixed if next.
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 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 ... Problem Summary: 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 ...
Problem conclusion
Temporary fix
see local fix.
Comments
APAR Information
APAR number
JR30818
Reported component name
DB2 UDB EXE WIN
Reported component ID
5724E4901
Reported release
810
Status
CLOSED FIN
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2008-10-20
Closed date
2008-10-20
Last modified date
2008-10-20
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Applicable component levels
R810 PSN
UP
R820 PSN
UP
R910 PSN
UP
R950 PSN
UP
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"810","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
20 October 2008