LI72465: PERFORMANCE IMPACT FROM NOT ROUTING A QUERY AGAINST A UNION ALL VIEW TO A MQT

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 is sysrouted FROM one or more of the following:

  LI72105

• 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