Fixes are available
APAR status
Closed as program error.
Error description
The DB2 Query optimizer can use a faster method called Greedy for determining the order in which to join the tables referenced in the query when the query contains many join operations. This APAR is only applicable when using Greedy join enumeration. If the statement was compiled with query optimization levels 0, 1, or 2, or if you received a SQL0437W reason code 1 or reason code 2, then the query was compiled using Greedy join enumeration. Additionally if you have the DB2_REDUCED_OPTIMIZATION registry variable set, then we can choose to use Greedy join enumeration under certain circumstances. The Greedy method favours the joins with the lowest cost and does not consider higher cost alternatives which may lead to a better performance. When the query contains many tables being joined with many transitive join predicates this enumeration method can lead to a less-than-optimal plan selection. For example in the following query SELECT a1, a2, a3, b2, b3 FROM A,B,C,D WHERE a1=b1 AND b1=c1 AND d2=a2 In this query there is an implicit join predicate a1 = c1. As a result the optimizer could consider an initial join of table A to any of B or C or D. If the join of A and B is the least expensive join it will be chosen using this faster join planning method, and once this is chosen we will no longer consider a join of A with C or A with D even though those might lead to a lower cost plan overall. For example if the join of A with B results in 1,000,000 rows but is very inexpensive and the join A with D result in 100 rows but it very expensive, the faster join planning method will chose the join A with B even though that means processing a very large number of rows. The problem addressed by this APAR is most often characterized by a series of joins using only the predicates 'a1=b1 and a1=c1 and b1=c1' with rapidly increasing cardinality estimates shown in the plan. Eventually the join to table D will occur and the cardinality estimate will drop. This APAR will enable the optimizer to choose a better join order under the specified conditions. The behaviour change is enabled using either of the following settings: The setting DB2_EXTENDED_OPTIMIZATION=GY_DELAY_EXPAND Causes the faster join planning method to delay adding expanding joins until all the filtering and lookup joins have been added to the plan. In practical terms this means that the intermediate results sizes of the joins will not grow beyond the largest table involved in the join until the optimizer has no other choice. This prevent us from generating the huge intermediate result set, but the intermediate result set is still the size of the largest table, until it becomes cheapest to join in the filtering table(s). The risk of this setting, since planning is not strictly based on cost estimates, is that we are limiting our choices and thus may be forced into more expensive joins. or DB2_EXTENDED_OPTIMIZATION=GY_DELAY_LOOKUP Causes the greedy join enumeration algorithm to delay adding expanding and lookup joins until all the filtering joins have been added to the plan. What this means is that we will try to generate a plan that does the filtering as early as possible, thus shrinking the result set early. The risk of this setting, since planning is not strictly based on cost estimates, is that we are limiting our choices and thus may be forced into more expensive joins. The GY_DELAY_LOOKUP permits fewer choices then the GY_DELAY_EXPAND mode.
Local fix
If you are experiencing this problem you can work around the problem using the following methods: 1) Use an optimization profile to indicate the first join should be between A and D 2) If you are using query optimization level 0, 1 or 2, then attempt to compile the statement using optimization level 3 or higher so that we use the Dynamic Programming join enumeration method. 3) If you have received a SQL0437W reason code 1 then attempt to increase the value of the STMTHEAP configuration parameter to give more memory to Dynamic Programming join enumeration. 4) If you have DB2_REDUCED_OPTIMIZATION set then you may want to attempt to unset and determine if that helps the problem.
Problem summary
USERS AFFECTED All PROBLEM DESCRIPTION see ERROR DESCRIPTION PROBLEM SUMMARY see ERROR DESCRIPTION
Problem conclusion
The complete fix for this problem first appears in DB2 UDB Version 9.1 FixPak 9.
Temporary fix
If you are experiencing this problem you can work around the problem using the following methods: 1) Use an optimization profile to indicate the first join should be between A and D 2) If you are using query optimization level 0, 1 or 2, then attempt to compile the statement using optimization level 3 or higher so that we use the Dynamic Programming join enumeration method. 3) If you have received a SQL0437W reason code 1 then attempt to increase the value of the STMTHEAP configuration parameter to give more memory to Dynamic Programming join enumeration. 4) If you have DB2_REDUCED_OPTIMIZATION set then you may want to attempt to unset and determine if that helps the problem.
Comments
APAR Information
APAR number
IZ53209
Reported component name
DB2 UDB ESE SOL
Reported component ID
5765F4102
Reported release
910
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2009-06-12
Closed date
2010-04-14
Last modified date
2010-04-14
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 UDB ESE SOL
Fixed component ID
5765F4102
Applicable component levels
R810 PSN
UP
R820 PSN
UP
R910 PSN
UP
R950 PSN
UP
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
14 April 2010