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

DB2 OPTIMIZER MIGHT CHOOSE SUBOPTIMAL JUMPSCAN INDEX ACCESS PLAN FOR
UPDATE/DELETE STATEMENT

Produkt:
DB2 FOR LUW / DB2FORLUW / A50 - DB2
Problembeschreibung:
The DB2 query optimizer might choose suboptimal jumpscan index 
access plan for UPDATE/DELETE statements when the following 
conditions are true: 
 
1) The only way to apply the predicate as start/stop key is 
using jumpscan i.e. there is no such index available that has 
the predicate-column as a leading column. 
2) The 'gap' column of the index is almost unique. 
3) The predicate column is a foreign key column. 
 
For example, consider the following DDLs: 
   CREATE TABLE T1( C1 INTEGER NOT NULL , ... ); 
   ALTER TABLE T1 ADD PRIMARY KEY (C1); 
 
   CREATE TABLE T2( C2 INTEGER NOT NULL , C3 INTEGER , C1 
INTEGER , C4 INTEGER , ... ); 
   ALTER TABLE T2 ADD PRIMARY KEY (C2); 
   CREATE INDEX I2 ON T2 (C3 ASC, C1 ASC, C4 ASC); 
   ALTER TABLE T2  ADD CONSTRAINT "REF1" FOREIGN KEY (C1) 
REFERENCES T1 (C1) 
   ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY 
OPTIMIZATION; 
 
For the following DELETE statement, the optimizer might choose a 
jumpscan index access plan. 
   DELETE FROM T1 WHERE C1 = ? 
 
The db2exfmt output will show the following: 
        5) IXSCAN: (Index Scan) 
                ... 
                Arguments: 
                --------- 
                ... 
                JUMPSCAN: (Jump Scan Plan) 
                        TRUE 
                ... 
                Predicates: 
                ---------- 
                3) Start Key Predicate, 
                ... 
                        Predicate Text: 
                        -------------- 
                        (? = Q5.C1) 
 
                3) Stop Key Predicate, 
                ... 
                        Predicate Text: 
                        -------------- 
                        (? = Q5.C1) 
 
 
                Gap Info:            Status 
                ---------            ------ 
                Index Column 1:      Gap 
                Index Column 2:      No Gap 
 
Here, C3 is the gap column and it is almost unique.
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Problem Description above.                               * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.5 Fix Pack 7.                      * 
****************************************************************
Local-Fix:
Any one of the following should help optimizer choose a good 
access plan: 
1) Create a new index with the predicate-column as leading 
column 
2) Disable optimizer rule that prunes good plan: 
db2set -im DB2_REDUCED_OPTIMIZATION=NO_JULIE 
3) Disable jumpscan itself: 
db2set -im DB2_REDUCED_OPTIMIZATION='JUMPSCAN OFF'
Lösung
First fixed in DB2 Version 10.5 Fix Pack 7.
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
29.07.2015
03.02.2016
03.02.2016
Problem behoben ab folgender Versionen (IBM BugInfos)
Problem behoben lt. FixList in der Version
10.5.0.7 FixList