Fixes are available
DB2 Version 9.5 Fix Pack 3b for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
APAR status
Closed as program error.
Error description
The query optimizer may underestimate the cost of a merge join (MSJOIN) or nested loop join (NLJOIN) operator when atleast one column in the join predicate contains a large percentage of nulls. The following is a simple scenario that can hit this problem: create table t1 (a int); create table t2 (a int); create index ix1 on t1 (a); Data in the tables: T1 (a) T2 (a) 1 1 2 3 3 4 4 5 5 6 - - - - - - - - - - - - - - - - - - - - Query: select t2.* from t1,t2 where t1.a=t2.a; When considering the NLJOIN with T2 on the outer, and T1 (via index access) on the inner: NLJOIN / \ T2 IX1 the optimizer will underestimate the cost of this NLJN operator due to the large number of NULLs in the column T1.a. This cost misestimation may lead to poor performance if it results in the optimizer choosing a non-optimal query execution plan.
Local fix
lower optimization level to 0 may avoid the issue.
Problem summary
see problem description
Problem conclusion
First fixed in DB2 UDB Version 9.5, FixPak 1
Temporary fix
Comments
APAR Information
APAR number
IZ08093
Reported component name
DB2 UDB ESE SOL
Reported component ID
5765F4102
Reported release
950
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2007-11-06
Closed date
2008-05-30
Last modified date
2008-05-30
APAR is sysrouted FROM one or more of the following:
IY99607
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
DB2 UDB ESE SOL
Fixed component ID
5765F4102
Applicable component levels
R910 PSY
UP
R950 PSY
UP
Document Information
Modified date:
01 October 2021