DB2 FOR LUW / DB2FORLUW / A10 - DB2

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

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users on DB2 v101 FP4 and lower                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 v101 FP5                                      * 
****************************************************************

Use only tablesample or indexsample alone to get better card 
estimation

First Fixed in DB2 v101 FP5

not known / see Local fix

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

17.09.2014
17.07.2015
17.07.2015