DB2 FOR LUW / DB2FORLUW / B10 - DB2

This issue applies to a query that includes one or more inner
joins together with 2 or more correlated scalar subqueries and
only when the Db2 query optimizer uses the greedy method for
join enumeration.  If the estimated cost of the inner join is
larger than the cost of the subqueries, the greedy can produce a
join order considering the cartesian product of the subqueries
first which might lead to a suboptimal access plan chosen.

The following is an example of a query containing one inner join
and three correlated scalar subqueries:

select
  t1.a,
  (select max(t3.a) from t3 where t3.b=t1.b),
  (select max(t4.a) from t4 where t4.b=t1.c),
  (select max(t5.a) from t5 where t5.b=t1.d)
from t1,t2 where t1.a=t2.a

The Db2 query optimizer can use a faster, greedy method for join
enumeration, depending on the optimization class used or the
complexity of the query.  If the query was compiled with query
optimization classes 0, 1, or 2, or if you received a SQL0437W
warning with reason code 1 or 2, then the query was compiled
using the greedy method for join enumeration. Additionally if
you have the DB2_REDUCED_OPTIMIZATION registry variable set,
then the greedy method can be used under certain circumstances.

The fix will be enabled only under registry variable protection.

****************************************************************
* USERS AFFECTED:                                              *
* ALL                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 Version 11.1 Mod 4 Fix Pack 6.                *
****************************************************************

An optimization profile can be used to request a different
access plan.  If you received a SQL0437W with reason code 1,
increasing the STMTHEAP might allow the optimizer to complete
using dynamic programming method of join enumeration, avoiding
the issue.

****************************************************************
* USERS AFFECTED:                                              *
* ALL                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 Version 11.1 Mod 4 Fix Pack 6.                *
****************************************************************

First fixed in Db2 Version 11.1 Mod 4 Fix Pack 6.

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

13.11.2020
16.03.2021
16.03.2021