home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IT07432 Status: Geschlossen

QUERIES WITH "CASE WHEN" USING SUBQUERIES MAY LEAD TO PERFORMANCE
ISSUE.

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problembeschreibung:
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.
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* all                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* upgrade to version 9.7 Fix Pack 11                           * 
****************************************************************
Local-Fix:
Lösung
First fixed in version 9.7 Fix Pack 11
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
02.03.2015
08.10.2015
08.10.2015
Problem behoben ab folgender Versionen (IBM BugInfos)
9.7.FP11
Problem behoben lt. FixList in der Version
9.7.0.11 FixList