[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) < 100
F7: 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 access
DB20000I
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 db2reo
db2inst1
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) < 100
F7: 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.Please refer to :
http://www.xifenfei.com/3013.html