Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC65934 Status: Closed

QUERY PERFORMANCE MAY DEGRADE IN PRESENCE OF NON-EQUALITY PREDIC ATE ON
MORE THAN ONE TABLE THAT CAN BE REWRITTEN TO ONE TABLE

product:
DB2 FOR LUW / DB2FORLUW / 910 - DB2
Problem description:
Query performance may degrade if it contains an non-equality 
predicate which references more than one table, but which can be 
rewritten to reference only one table using equivalence. 
 
Conditions required for such a scenario are: 
- Two or more tables referenced in a query 
- An non-equality predicate that references two or more tables 
(NONEQPRD) 
- Column equivalence via one or more equi-joins that makes it 
possible to rewrite NONEQPRD to reference only one table 
 
A trivial query that can be used to demonstrate this is: 
SELECT * 
FROM   T1, T2, T3 
WHERE  T1.C1 = T2.C1 
AND    T3.C2 = T2.C2 
AND    (T1.C1 = 'abcd' OR T3.C2 = 1 OR T2.C3 = 5)
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* EE or EEE                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description.                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.1 Fix Pack 9 or later.              * 
****************************************************************
Local Fix:
Manually rewrite any problematic non-equality predicates to 
their 
semantically equivalent forms for a single table. 
For the query above, this can be done like so: 
SELECT * 
FROM   T1, T2, T3 
WHERE  T1.C1 = T2.C1 
AND    T3.C2 = T2.C2 
AND    (T2.C1 = 'abcd' OR T2.C2 = 1 OR T2.C3 = 5)
available fix packs:
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

Solution
First fixed in DB2 Version 9.1 Fix Pack 9.
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
28.01.2010
19.04.2010
19.04.2010
Problem solved at the following versions (IBM BugInfos)
9.1.FP9
Problem solved according to the fixlist(s) of the following version(s)
9.1.0.9 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.