Fixes are available
DB2 Version 9.1 Fix Pack 7 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a 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
MQT fails to be matched when 2 or more equality predicates exist with the same literal an example is -- create base tables CREATE TABLE T1 ( "ID1" INTEGER , "X1" INTEGER , "C1" INTEGER ); ; CREATE TABLE T2 ( "ID2" INTEGER , "C2" INTEGER ); CREATE TABLE T3 ( "ID3" INTEGER , "X3" INTEGER , "C3" INTEGER ); CREATE TABLE T4 ( "X4" INTEGER , "C4" INTEGER ); CREATE TABLE T5 ( "ID5" INTEGER ); -- create a view CREATE VIEW V1 AS ( SELECT COUNT(*) AS COUNT, T3.ID3 AS VID3 FROM T3, T4 WHERE T3.C3 = T4.C4 AND T4.X4 = 100 AND T3.X3 = 100 GROUP BY T3.ID3 ); -- create MQT CREATE TABLE MQT AS ( SELECT * FROM V1) DATA INITIALLY DEFERRED REFRESH IMMEDIATE; REFRESH TABLE MQT; -- Query is not routed to MQT SELECT 1 FROM T5, V1 WHERE T5.ID5 = V1.VID3 AND T5.ID5 IN ( SELECT T5.ID5 FROM T2 INNER JOIN T5 ON T5.ID5 = T2.ID2 INNER JOIN V1 ON V1.VID3 = T2.ID2 LEFT OUTER JOIN T1 AS T1 ON T1.ID1 = T2.ID2 AND T1.X1 = 100 WHERE ( T1.C1 = 200 OR T1.C1 = 300 ) AND T2.C2 = 400 GROUP BY T5.ID5 ) ; Two characteristics to get MQT not be matched in this case (1) MQT has >=2 local equality preds whose literal are same, called literal_x (100 in the example). (2) Query has a CSE (Common Sub-Expression) that can be matched with MQT. One CSE consumer is a row preserving side of OUTER JOIN, and NULL PRODUCING side of this OJ has a local equality pred whose constant is literal_x too. The other constructs are also needed to force a particular path of QRW rule sequences to trigger the problem.
Local fix
n/a
Problem summary
Users affected : Users with MQT tables Problem summary : suboptimal performance may exists for some queries Problem conclusion : This apar fix will ensure optimal performance
Problem conclusion
First fixed in DB2 UDB Version 9, FixPak 6
Temporary fix
Comments
APAR Information
APAR number
IZ16820
Reported component name
DB2 UDB ESE SOL
Reported component ID
5765F4102
Reported release
910
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2008-03-03
Closed date
2008-11-03
Last modified date
2008-11-03
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 ESE SOL
Fixed component ID
5765F4102
Applicable component levels
R910 PSY
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":"910","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
03 November 2008