[db2inst1@xifenfei ~]$ db2 connect to xff Database
Connection Information Database
server = DB2/LINUX
9.5.9 SQL authorization
ID = DB2INST1 Local database
alias =
XFF[db2inst1@xifenfei ~]$ db2 list tablesTable/View
Schema
Type Creation time ------------------------------- --------------- -----
--------------------------T_01XFF
DB2INST1 T
2012-04-11-18.23.05.723478T_02XFF
DB2INST1 T
2012-04-11-18.30.26.639326T_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‘)"STATS_TIME
--------------------------2012-04-12-04.35.07.5397902012-04-11-19.55.12.0237482012-04-11-22.20.07.016905 3 record(s)
selected.--收集表和索引统计信息,包括数据分布[db2inst1@xifenfei ~]$ db2 "runstats on table
db2inst1.t_01xff on all columnswith
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‘)"STATS_TIME
--------------------------2012-04-28-23.43.23.904759 1 record(s)
selected.--收集索引统计信息,如果表没有被收集,也会同时对表收集统计信息,对不会收集数据分布信息[db2inst1@xifenfei ~]$ db2 "runstats on table db2inst1.t_02xff for indexes
all"DB20000I
The RUNSTATS command completed
successfully.[db2inst1@xifenfei ~]$ db2 "select STATS_TIME from syscat.tables where tabname
in(‘T_01XFF‘,‘T_02XFF‘)"STATS_TIME
--------------------------2012-04-28-23.43.23.9047592012-04-28-23.44.39.762858 2 record(s)
selected.db2 reorg操作
--删除部分表数据[db2inst1@xifenfei ~]$ db2 "delete from t_01xff"DB20000I
The SQL command completed
successfully.[db2inst1@xifenfei ~]$ db2 "delete from t_03xff"DB20000I
The SQL command completed
successfully.--reorgchk检查是否需要进行reorg[db2inst1@xifenfei ~]$ db2 reorgchk on schema
db2inst1Doing RUNSTATS
....Table
statistics:F1: 100 *
OVERFLOW / CARD < 5F2: 100 *
(Effective Space Utilization of Data Pages) > 70F3: 100 *
(Required Pages / Total Pages) > 80SCHEMA.NAME
CARD OV
NP FP ACTBLK TSIZE F1
F2 F3 REORG----------------------------------------------------------------------------------------Table:
DB2INST1.T_01XFF 0
0 0
42 -
0 0 0 0 -** Table:
DB2INST1.T_02XFF 371 0
42 42 -
152110 0 100 100 --- Table:
DB2INST1.T_03XFF 0
0 0
83 -
0 0 0 0 -** ----------------------------------------------------------------------------------------Index
statistics:F4:
CLUSTERRATIO or normalized CLUSTERFACTOR > 80F5: 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) < 100F7: 100 *
(Number of pseudo-deleted RIDs / Total number of RIDs) < 20F8: 100 *
(Number of pseudo-empty leaf pages / Total number of leaf pages) <
20SCHEMA.NAME
INDCARD LEAF ELEAF LVLS NDEL KEYS LEAF_RECSIZE
NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD F4 F5
F6 F7 F8 REORG --------------------------------------------------------------------------------------------------------------------------------------------------------------Table:
DB2INST1.T_01XFFIndex:
DB2INST1.I_T_01XFF 0 3
3 2
0
0
2
2
822
822 100 0 - 0 100 ----* --------------------------------------------------------------------------------------------------------------------------------------------------------------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 accessDB20000I
The REORG command completed
successfully.--在线reorg
table[db2inst1@xifenfei ~]$ db2 reorg table db2inst1.t_01xff
inplace allow write accessDB20000I
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 accessDB20000I
The REORG command completed
successfully.DB21024I
This command is asynchronous and may not be effective
immediately.--证明异步操作完成[db2inst1@xifenfei ~]$ ps -ef|grep db2reodb2inst1
1496 1311 0 00:24 pts/1 00:00:00
grep db2reo--检查reorg操作结果[db2inst1@xifenfei ~]$ db2 reorgchk on schema
db2inst1Doing RUNSTATS
....Table
statistics:F1: 100 *
OVERFLOW / CARD < 5F2: 100 *
(Effective Space Utilization of Data Pages) > 70F3: 100 *
(Required Pages / Total Pages) > 80SCHEMA.NAME
CARD OV
NP FP ACTBLK TSIZE F1
F2 F3 REORG----------------------------------------------------------------------------------------Table:
DB2INST1.T_01XFF 0
0 0
1 -
0 0 - 0 --- Table:
DB2INST1.T_02XFF 371 0
42 42 -
152110 0 100 100 --- Table:
DB2INST1.T_03XFF 0
0 0
1 -
0 0 - 0 --- ----------------------------------------------------------------------------------------Index
statistics:F4:
CLUSTERRATIO or normalized CLUSTERFACTOR > 80F5: 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) < 100F7: 100 *
(Number of pseudo-deleted RIDs / Total number of RIDs) < 20F8: 100 *
(Number of pseudo-empty leaf pages / Total number of leaf pages) <
20SCHEMA.NAME
INDCARD LEAF ELEAF LVLS NDEL KEYS LEAF_RECSIZE
NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD F4 F5
F6 F7 F8 REORG --------------------------------------------------------------------------------------------------------------------------------------------------------------Table:
DB2INST1.T_01XFFIndex:
DB2INST1.I_T_01XFF 0 1
0 1
0
0
2
2
822
822 100 - - 0 0 -----
--------------------------------------------------------------------------------------------------------------------------------------------------------------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.原文：http://www.cnblogs.com/sharepointhome/p/3536953.html