DB2 FOR LUW / DB2FORLUW / B10 - DB2

The optimizer might incorrectly favour a full scan of a table
over an index scan for a SQL statement containing one or more
joins and one or more subquery predicates.  This can lead to
suboptimal query execution performance.

The following is an example of a query containing a join between
two tables and an IN subquery predicate:

select Col1, Col2, Col3
from
 Table1 T1,
 Table2 T2
where
  T2.C1 =  and
  T2.C2=T1.C1
  T2.C3 not in
  (SELECT val FROM
     TABLE (db2inst1.udf('val')) AS t(val)
  )

In a typical scenario, a NLJOIN with an index access of T1 on
the inner is the likely optimal choice for this query.

If the optimizer instead chooses a different access plan with a
full table scan of T1, you can try forcing the index access with
an optimization guideline.  You can identify this issue when the
total cost of the access plan with the index access is set to
3.40282e+38.

****************************************************************
* USERS AFFECTED:                                              *
* all platforms                                                *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* upgrade to db2_v111m1fp2                                     *
****************************************************************

As a temporary workaround, an optimization guideline can be used
to force the index access

not known / see Local fix

forerunner  : IT16070 
follow-up : 

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

03.08.2017
09.10.2017
09.10.2017