If a statistics profile is set having runstats option "ON KEY
COLUMNS", runstats using the profile won't collect basic and/or
distribution statistics for newly created indexes or remove
basic and/or distribution statistics for newly dropped indexes.
The problem can occur when the statistics profile contains "ON
KEY COLUMNS" for basic and/or distribution statistics
The root cause of the issue is, when an index is dropped or
created, the existing statistics profile isn't maintained
accordingly if the statistics profile has the runstats option
"ON KEY COLUMNS"
For example, the issue can be reproduced in two scenarios below.
Scenario 1:
db2 "create table myt1 (id smallint, name varchar(10))"
//collect statistics and set profile with option "WITH
DISTRIBUTION ON KEY COLUMNS"
db2 "runstats on table db2inst1.myt1 WITH DISTRIBUTION ON KEY
COLUMNS set profile"
//check if any columns' distribution statistics have been
collected. it should be nothing in this case as there is no
index.
db2 "select distinct substr(colname,1,10) colname from
syscat.coldist where tabname='MYT1'"
//create a new index
db2 "create index myt_idx on myt1(id)"
//runstats use profile
db2 "runstats on table db2inst1.myt1 use profile"
//check again if any columns' distribution statistics have been
collected. it's supposed to have distribution statistics for the
column "ID", however there is still nothing.
db2 "select distinct substr(colname,1,10) colname from
syscat.coldist where tabname='MYT1'"
Scenario 2:
db2 "create table myt1 (id smallint, name varchar(10))"
//create an index
db2 "create index myt_idx on myt1(id)"
//collect statistics and set profile with option "WITH
DISTRIBUTION ON KEY COLUMNS"
db2 "runstats on table db2inst1.myt1 WITH DISTRIBUTION ON KEY
COLUMNS set profile"
//check if any columns' distribution statistics have been
collected. The column "ID" should be there.
db2 "select distinct substr(colname,1,10) colname from
syscat.coldist where tabname='MYT1'"
COLNAME
----------
ID
1 record(s) selected.
//drop the index
db2 "drop index myt_idx"
//runstats use profile
db2 "runstats on table db2inst1.myt1 use profile"
//check again if any columns' distribution statistics have been
collected. it's supposed that the existing distribution
statistics for the index column "ID" should have been removed,
however it's still there.
db2 "select distinct substr(colname,1,10) colname from
syscat.coldist where tabname='MYT1'"
COLNAME
----------
ID
1 record(s) selected. |
