DB2 FOR LUW / DB2FORLUW / 970 - DB2

Queries in the following form may show bad performance when a 
CASE WHEN condition contains subqueries: 
 
SELECT COUNT(*) 
FROM 
   (SELECT T1.COL1, T1.COL2, T1.COL3, T1.COL4, 
           T1.COL5, T1.COL6, T1.COL7, T1.COL8 
   FROM TAB01 T1 INNER JOIN TAB02 T2 INNER JOIN session.#tt4 T3 
           ON 1=1 ON (T1.COL2 = T2.COL2) 
   WHERE ((T2.COL6 = 'My string' AS CHAR(16)FOR BIT DATA))) AND 
   CASE 
   WHEN (T3.COL55 > CAST(2 AS NUMERIC(1, 0))) 
   THEN CAST(x'01' AS CHAR(1) FOR BIT DATA) 
   ELSE 
      CASE 
      WHEN (((T2.MyCol_A IN (SELECT T4.MyCol_B AS MyCol_B FROM 
session.#tt5 T4)) 
          OR (T2.MyCol_C IN (SELECT T5.MyCol_B AS MyCol_B FROM 
session.#tt5 T5))) 
          OR (T2.MyCol_D IN (SELECT T6.MyCol_B AS MyCol_B FROM 
session.#tt5 T6))) 
      THEN CAST(x'01' AS CHAR(1) FOR BIT DATA) 
      ELSE CAST(x'00' AS CHAR(1) FOR BIT DATA) 
      END 
   END = CAST(x'01' AS CHAR(1) FOR BIT DATA)) 
 
The suboptimal performance arises when there are a lot of rows 
being evaluated and they all need to evaluate all subexpressions 
in the case statement. 
This apar fix will introduce an internal optimization by 
generating extra boolean predicates that can be evaluated prior 
to having to execute the relatively expensive subselects.

**************************************************************** 
* USERS AFFECTED:                                              * 
* all                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* upgrade to version 9.7 Fix Pack 11                           * 
****************************************************************

First fixed in version 9.7 Fix Pack 11

not known / see Local fix

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

02.03.2015
08.10.2015
08.10.2015

9.7.FP11