IZ64196: POSSIBLE PERFORMANCE REGRESSION FROM SCALAR SUBQUERY PUSH DOWN INTO OUTER JOIN

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 SQL staement containing a scalar subquery used in the context
  of >=, >, <=, <, !=, or as an expression of other expression
  rather than an equality predicate may be pushed down under an
  Outer Join operation. If the whole expression results in an
  expanding join because of the push down, it could potentially
  degrade the performance of the statement.
  

Local fix

• Upgraded to DB2 Version 9.1 Fix Pack 9
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A SQL statement containing a scalar subquery used in the     *
  * context                                                      *
  * of >=, >, <=, <, !=, or as an expression of other expression *
  *                                                              *
  * rather than an equality predicate may be pushed down under   *
  * an                                                           *
  * Outer Join operation. If the whole expression results in an  *
  *                                                              *
  * expanding join because of the push down, it could            *
  * potentially                                                  *
  * degrade the performance of the statement.                    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgraded to DB2 Version 9.1 Fix Pack 9.                      *
  ****************************************************************
  

Problem conclusion

• This problem has been fixed in DB2 Version 9.1 Fix Pack 9.
  

Temporary fix

Comments

APAR Information

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

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

  IC65405 IC65406

Fix information

• Fixed component name

  DB2 POFE

• Fixed component ID

  5724N7700

Applicable component levels

• R910 PSN

     UP

• R950 PSN

     UP

• R970 PSN

     UP