Fixes are available
DB2 Version 9.1 Fix Pack 7 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5 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
A query referencing a UNION ALL view is not routed to a MQT by DB2 since the number of branches of the MQT varies from the number of branches in the UNION ALL view. This can happen if the original query against the UNION ALL view undergoes branch elimination through the predicates specified in the query. Branch elimination can result in the query having less branches than the original UNION ALL view. At this point, the MQT definition is on the original UNION ALL view and this results in the MQT definition not matching the current Optimized Statement which has undergone branch elimination.
Local fix
Create a MQT that spans the same number of branches seen in the Optimized Statement of the query. For example, if the original query results in branch elimination that reduces a UNION ALL view of 5 branches to 2 branches. Create a MQT on the 2 branches.
Problem summary
USERS AFFECTED:ALL PROBLEM DESCRIPTION: A query referencing a UNION ALL view is not routed to a MQT by DB2 since the number of branches of the MQT varies from the number of branches in the UNION ALL view. This can happen if the original query against the UNION ALL view undergoes branch elimination through the predicates specified in the query. Branch elimination can result in the query having less branches than the original UNION ALL view. At this point, the MQT definition is on the original UNION ALL view and this results in the MQT definition not matching the current Optimized Statement which has undergone branch elimination. PROBLEM SUMMARY: Gathering the db2exfmt output for the query in question and the db2look output should reveal the presence of a UNION ALL View and branch elimination occuring. For more details on branch elimination read the following article: http://www.ibm.com/developerworks/db2/library/techarticle/0202zu zarte/0202zuzarte.pdf
Problem conclusion
First fixed in Version 9.1, FixPak 5
Temporary fix
Comments
APAR Information
APAR number
LI72465
Reported component name
DB2 UDE ESE LIN
Reported component ID
5765F4104
Reported release
910
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2007-06-25
Closed date
2009-10-01
Last modified date
2009-10-01
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 UDE ESE LIN
Fixed component ID
5765F4104
Applicable component levels
R810 PSN
UP
R820 PSN
UP
R910 PSN
UP
R950 PSN
UP
Document Information
Modified date:
16 October 2021