DB2 FOR LUW / DB2FORLUW / 970 - DB2

A query predicate may not be optimized fully if the following 
conditions are satisfied: 
- predicate contains one or more nested conjuncts (nested AND) 
- Nested AND has one or more OR sub-terms (nested OR sub-term) 
- sub-terms of Nested OR can be proven TRUE or FALSE at 
compile-time. 
 
An example of this: 
 
-- DDL 
CREATE TABLE T1 (ID INT); 
CREATE TABLE T2 LIKE T1; 
 
-- Query 1 
SELECT * 
FROM   T1, 
       T2 
WHERE  T2.ID = 1             -- <-- P1 
AND    ( 
          ( 
           TI.ID = 1         -- <-- Standalone via conjunction 
           AND               -- <-- Nested conjunct (resolves to 
first sub-term) 
              ( 
               T2.ID = 1     -- <-- TRUE via equivalence to P1 
               OR            -- <-- Nested OR (resolves to TRUE) 
               T2.ID IS NULL -- <-- FALSE via comparison to P1 
              ) 
          ) 
        OR                   -- <-- Resolves to first sub-term 
          ( 
           T1.ID = 2 
           AND               -- <-- Nested conjunct (resolves to 
FALSE) 
              ( 
               T2.ID = 2     -- <-- FALSE via comparison to P1 
               OR            -- <-- Nested OR (resolves to 
FALSE) 
               T2.ID IS NULL -- <-- FALSE via comparison to P1 
              ) 
          ) 
       ) 
; 
 
Query 1 above is semantically equivalent to Query 2 below: 
 
-- Query 2 
SELECT * 
FROM   T1, 
       T2 
WHERE  T2.ID = 1 
AND    T1.ID = 1

Users Affected: Any with a query that involves a a nested OR 
 
inside a nested AND statement 
Problem Description: Query rewrite may not choose the optimial 
rewrite strategy for a particular statement 
Problem Summary: see above.

Manually rewriting the predicate(s) in question as indicated for 
Query 1 in the description example.

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in Version 9.7 FixPak 1

Manually rewriting the query may resolve this (see APAR details)

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

18.08.2009
28.12.2009
28.12.2009

9.7.