IZ08760: UNION ALL QUERY POOR PERFORMANCE WHEN THERE ARE CORRELATED SUBQUERIES DUE TO PREDICATE PUSHDOWN NOT HAPPENNING

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

• For a query involving a Union all between different tables, the
  predicates pushdown will not happen during query rewrite when
  there is a correlated subquery to the tables  (such as
  subqueries after a EXISTS or NOT EXISTS clause).
  

Local fix

Problem summary

• Users affected = Users having Union all queries with correlated
  subqueries
  Problem Description = Poor query performance due to inadequate
  query access plan generated by the db2 optimizer for union all
  queries with correlated subqueries (predicate pushdown not
  happening).
  

Problem conclusion

• Problem first fixed in V9.5, Fixpak 1
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  IY92702

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  DB2 UDB WSE AIX

• Fixed component ID

  5765F3500

Applicable component levels

• R810 PSN

     UP

• R820 PSN

     UP

• R910 PSN

     UP

• R950 PSN

     UP