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

TABLE CARDINALITY VARIES SIGNIFICANTLY COMPARED TO ACTUAL TABLE ROW COUNT,
WHEN BOTH TABLESAMPLE AND INDEXSAMPLE IS SPECIFIED.

Produkt:
DB2 FOR LUW / DB2FORLUW / A10 - DB2
Problembeschreibung:
1.  Problem description 
 
Table card is significantly high compared to the actual row 
count when indexsampling option is provided. 
This issue occurs if table data pages are highly skewed and we 
have both 'tablesample' and 'indexsample' options specified. 
 
Ex: 
 
CREATE TABLE TABLE1( a       BIGINT NOT NULL, b        INTEGER 
NOT NULL, c        VARCHAR(254), d 
    VARCHAR(254), e        VARCHAR(254), f        VARCHAR(254) ) 
  COMPRESS YES 
  STATIC VALUE COMPRESSION 
  DISTRIBUTE BY HASH(a) in TBS6 ORGANIZE BY ROW USING (b) 
 
 
CREATE UNIQUE INDEX INDEX1 ON DB2INST1.TABLE1 (b asc, c asc, a 
asc) COMPRESS YES INCLUDE NULL KEYS ALLOW REVERSE SCANS 
 
 
load from data.del of del messages msg1.txt insert into 
db2inst1.table1 
 
Number of rows read         = 250000 
Number of rows skipped      = 0 
Number of rows loaded       = 250000 
Number of rows rejected     = 0 
Number of rows deleted      = 0 
Number of rows committed    = 250000 
 
 
select count(*) from db2inst1.table1 
 
1 
----------- 
     250000 
 
  1 record(s) selected. 
 
 
 
RUNSTATS ON TABLE db2inst1.table1 on all columns with 
distribution on all columns and sampled detailed indexes all 
 
 
SELECT substr(tabschema,1,10) as tabschema, substr(tabname,1,10) 
as tabname, card, active_blocks,npages, fpages from 
syscat.tables where tabname='TABLE1' and tabschema='DB2INST1' 
 
TABSCHEMA  TABNAME    CARD                 ACTIVE_BLOCKS 
NPAGES               FPAGES 
 
---------- ---------- -------------------- -------------------- 
-------------------- --------------- 
 
DB2INST1   TABLE1                    250000                  611 
18982            19584 
 
 
 
 
-- carefully deleted 154993 rows to create skew intable data 
distribution. After delete we have 95007 rows 
 
select count(*) from db2inst1.table1 
 
1 
----------- 
      95007 
 
 
 
 
-- runstats without sampling results in good card value 
 
RUNSTATS ON TABLE db2inst1.table1 on all columns with 
distribution on all columns and sampled detailed indexes all 
 
 
SELECT substr(tabschema,1,10) as tabschema, substr(tabname,1,10) 
as tabname, card, active_blocks,npages, fpages from 
syscat.tables where tabname='TABLE1' and tabschema='DB2INST1' 
 
TABSCHEMA  TABNAME    CARD                 ACTIVE_BLOCKS 
NPAGES               FPAGES 
 
---------- ---------- -------------------- -------------------- 
-------------------- --------------- 
 
DB2INST1   TABLE1                     95007                  427 
13269          19584 
 
 
 
 
-- CARD is almost double compared to original row count when we 
have both tablesample and indexsample option specified. 
 
RUNSTATS ON TABLE db2inst1.table1 on all columns with 
distribution on all columns and sampled detailed indexes all 
TABLESAMPLE SYSTEM(1) REPEATABLE(4196) INDEXSAMPLE SYSTEM(1) 
 
 
SELECT substr(tabschema,1,10) as tabschema, substr(tabname,1,10) 
as tabname, card, active_blocks, npages, fpages from 
syscat.tables where tabname='TABLE1' and tabschema='DB2INST1' 
 
TABSCHEMA  TABNAME    CARD                 ACTIVE_BLOCKS 
NPAGES               FPAGES 
 
---------- ---------- -------------------- -------------------- 
-------------------- --------------- 
 
DB2INST1   TABLE1                    166988                  598 
19136         19584
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* All users on DB2 v101 FP4 and lower                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 v101 FP5                                      * 
****************************************************************
Local-Fix:
Use only tablesample or indexsample alone to get better card 
estimation
Lösung
First Fixed in DB2 v101 FP5
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
17.09.2014
17.07.2015
17.07.2015
Problem behoben ab folgender Versionen (IBM BugInfos)
Problem behoben lt. FixList in der Version
10.1.0.5 FixList