When an index's first (or only) column is
a varying-length column, the RUNSTATS utility fails to account
for the column in its calculation of AVGLEAFKEYSIZE and
AVGNLEAFKEYSIZE.
This problem affects the REORGCHK utility which uses the
AVGLEAFKEYSIZE and AVGNLEAFKEYSIZE statistical values.
In the following example, column i1 is a fixed-length column and
column v2 is a varying-length column. Index I2 and I4 have
incorrect AVGLEAFKEYSIZE and AVGNLEAFKEYSIZE.
create table S90933.T1 (i1 int, v2 varchar(10));
insert into S90933.T1 values
(1,'1'),(2,'22'),(3,'333'),(4,'4444'),(5,'55555');
create index S90933.I1 on S90933.T1 (i1);
create index S90933.I2 on S90933.T1 (v2);
create index S90933.I3 on S90933.T1 (i1,v2);
create index S90933.I4 on S90933.T1 (v2,i1);
runstats on table S90933.T1 and indexes all;
select substr(indname,1,5) as index,substr(colnames,1,10) as
columns, avgleafkeysize, avgnleafkeysize from syscat.indexes
where tabschema='S90933';
INDEX COLUMNS AVGLEAFKEYSIZE AVGNLEAFKEYSIZE
----- ---------- -------------- ---------------
I1 +I1 4 4
I2 +V2 0 0
I3 +I1+V2 7 7
I4 +V2+I1 4 4
4 record(s) selected.
Workaround: Manually update the statistics to the correct
values.
For example:
update sysstat.indexes set avgleafkeysize=3, avgnleafkeysize=3
where indschema='S90933' and indname='I2';
update sysstat.indexes set avgleafkeysize=7, avgnleafkeysize=7
where indschema='S90933' and indname='I4';
In DB2 Version 9.7, manually updating AVGLEAFKEYSIZE and
AVGNLEAFKEYSIZ will not disable automatic statistics collection
for the table. If automatic statistics collection for the
database is enabled, and if an automatic runstats occurs for the
table, the updated values will be overwritten by the values
calculated by the runstats utility. |
