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 IC79173 Status: Geschlossen

DB2 SQL Optimizer might choose a suboptimal access plan for query with
'OPTIMIZE FOR N ROWS' clause and IS NULL predicate

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problembeschreibung:
For a query with an 'optimize for N rows' clause and a highly 
filtering IS NULL predicate, the DB2 SQL Optimizer might choose 
a suboptimal query access plan as in the following example: 
 
select * 
from fact left outer join d1 on f1 = id1 
          left outer join d2 on f2 = id2 
          left outer join d3 on f3 = id3 
          left outer join d4 on f4 = id4 
where d3.c31 is null; 
 
with access plan graph for this query ( db2exfmt output ): 
 
                                        RETURN 
                                          | 
                                        >HSJOIN 
                                 /--------+---------\ 
                             >HSJOIN                TBSCAN 
                       /-------+-------\              | 
                   >HSJOIN             TBSCAN         D4 
                 /---+----\              | 
             FILTER       TBSCAN         D2 
               |            | 
            >HSJOIN         D1 
         /-----+------\ 
     TBSCAN           TBSCAN 
       |                | 
      FACT              D3 
 
The IS NULL predicate is applied by the FILTER.  The MAXPAGES 
argument described in the details of the TBSCAN operator(s) 
below the FILTER is a potential indicator of the problem.  If 
the MAXPAGES argument is small and the IS NULL predicate is 
filtering many records, then a large amount of synchronous IO 
might be required, resulting in suboptimal performance.
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.7 Fix Pack 6                        * 
****************************************************************
Local-Fix:
verfügbare FixPacks:
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows

Lösung
Problem was first fixed in DB2 version 9.7 Fix Pack 6
Workaround
keiner bekannt / siehe Local-Fix
Bug-Verfolgung
Vorgänger  : APAR is sysrouted TO one or more of the following: IC84597 
Nachfolger : 
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
11.10.2011
14.06.2012
14.06.2012
Problem behoben ab folgender Versionen (IBM BugInfos)
9.7.FP6
Problem behoben lt. FixList in der Version
9.7.0.6 FixList