IY93789: A VERY COMPLEX QUERY MAY RETURN A WARNING MESSAGE SQL0437W AFTER UPGRADING TO FIX PACK 10 OR LATER

DB2 Version 9.1 Fix Pack 4a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 4 for Linux, UNIX and Windows
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

• Due to changes in the DB2 optimizer code that started in DB2 9.1
  a very complex query that used to work fine before an upgrade to
  DB2 9.1, may return the following warning after the upgrade:
  
    SQL0437W Performance of this complex query may be sub-optimal.
    Reason code: "1". SQLSTATE=01602.
  
    Explanation:
    The statement may achieve sub-optimal performance since
    the complexity of the query requires resources that are
    not available or optimization boundary conditions were
    encountered.  The following is a list of reason codes:
  
    1 The join enumeration method was altered due to
      memory constraints
  

Local fix

• Two options to Workaround the issue.
  
  Set the DB2_REDUCED_OPTIMIZATION registry variable to YES
  
    db2set db2_reduced_optimization=yes
  
  or
  
  Reduce the current query optimization level
    (e.g. set current query optimization 2)
  

Problem summary

• Users Affected:
  
  All
  
  Problem Description:
  
  Query may return a warning message SQL0437W after upgrading to
  Fix Pack 10 or later
  
  
  Error Description:
  
  
  Due to changes in the DB2 optimizer code that started in Fix
  Pack 10,
  a query that used to work fine before the upgrade, it may return
  the following warning after the upgrade:
  
  SQL0437W Performance of this complex query may be sub-optimal.
  Reason code: "1". SQLSTATE=01602.
  
  Problem Summary:
  
  Query may return a warning message SQL0437W after upgrading to
  Fix Pack 10 or later
  
  
  Error Description:
  
  
  Due to changes in the DB2 optimizer code that started in Fix
  Pack 10,
  a query that used to work fine before the upgrade, it may return
  the following warning after the upgrade:
  
  SQL0437W Performance of this complex query may be sub-optimal.
  Reason code: "1". SQLSTATE=01602.
  
  Explanation:
  
  The statement may achieve sub-optimal performance since the
  complexity of the query requires resources that are not
  available
  or optimization boundary conditions were encountered.  The
  following is a list of reason codes:
  
  
   1 The join enumeration method was altered due to memory
  constraints
  

Problem conclusion

• Problem was first fixed in version 9 fixpack 4  (s071028)
  

Temporary fix

• db2set db2_reduced_optimization=yes
  
  or
  
  Reduce the current query optimization level
  (e.g. set current query optimization 2)
  

Comments

APAR Information

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

  IY93788

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

  IZ09197

Fix information

• Fixed component name

  DB2 UDB ESE SOL

• Fixed component ID

  5765F4102

Applicable component levels

• R810 PSN

     UP

• R820 PSN

     UP

• R910 PSN

     UP

• R950 PSN

     UP