Database server = DB2/LINUX 9.5.9
SQL authorization ID = DB2INST1
Local database alias = XFF
[db2inst1@xifenfei ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
T_01XFF DB2INST1 T 2012-04-11-18.23.05.723478
T_02XFF DB2INST1 T 2012-04-11-18.30.26.639326
T_03XFF DB2INST1 T 2012-04-11-21.33.12.479480
3 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "select STATS_TIME from syscat.tables where tabname in('T_01XFF','T_02XFF','T_03XFF')"
--收集表和索引统计信息,包括数据分布
[db2inst1@xifenfei ~]$ db2 "runstats on table db2inst1.t_01xff on all columns
with distribution and detailed indexes all"
DB20000I The RUNSTATS command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select STATS_TIME from syscat.tables where tabname in('T_01XFF')"
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20
CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
for indexes that are not in the same sequence as the base table. When multiple
indexes are defined on a table, one or more indexes may be flagged as needing
REORG. Specify the most important index for REORG sequencing.
Tables defined using the ORGANIZE BY clause and the corresponding dimension
indexes have a '*' suffix to their names. The cardinality of a dimension index
is equal to the Active blocks statistic of the table.
--离线reorg index
[db2inst1@xifenfei ~]$ db2 reorg table db2inst1.t_01xff index DB2INST1.I_T_01XFF allow read access
DB20000I The REORG command completed successfully.
--在线reorg table
[db2inst1@xifenfei ~]$ db2 reorg table db2inst1.t_01xff inplace allow write access
DB20000I The REORG command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[db2inst1@xifenfei ~]$ db2 reorg table db2inst1.t_03xff inplace allow write access
DB20000I The REORG command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20
CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
for indexes that are not in the same sequence as the base table. When multiple
indexes are defined on a table, one or more indexes may be flagged as needing
REORG. Specify the most important index for REORG sequencing.
Tables defined using the ORGANIZE BY clause and the corresponding dimension
indexes have a '*' suffix to their names. The cardinality of a dimension index
is equal to the Active blocks statistic of the table.
Please refer to : http://www.xifenfei.com/3013.html