DB2 runstats和reorg操作
$ db2 connect to xffDatabase Connection Information
Database server = DB2/LINUX 9.5.9
SQL authorization ID = DB2INST1
Local database alias = XFF
$ db2 list tables
Table/View Schema TypeCreation 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.
$ db2 "select STATS_TIME from syscat.tables where tabname in('T_01XFF','T_02XFF','T_03XFF')"
STATS_TIME
--------------------------
2012-04-12-04.35.07.539790
2012-04-11-19.55.12.023748
2012-04-11-22.20.07.016905
3 record(s) selected.
--收集表和索引统计信息,包括数据分布
$ db2 "runstats on table db2inst1.t_01xff on all columns
with distribution and detailed indexes all"
DB20000IThe RUNSTATS command completed successfully.
$ 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.
--收集索引统计信息,如果表没有被收集,也会同时对表收集统计信息,对不会收集数据分布信息
$ db2 "runstats on table db2inst1.t_02xff forindexes all"
DB20000IThe RUNSTATS command completed successfully.
$ db2 "select STATS_TIME from syscat.tables where tabname in('T_01XFF','T_02XFF')"
STATS_TIME
--------------------------
2012-04-28-23.43.23.904759
2012-04-28-23.44.39.762858
2 record(s) selected. db2 reorg操作
--删除部分表数据
$ db2 "delete from t_01xff"
DB20000IThe SQL command completed successfully.
$ db2 "delete from t_03xff"
DB20000IThe SQL command completed successfully.
--reorgchk检查是否需要进行reorg
$ db2 reorgchk on schema db2inst1
Doing RUNSTATS ....
Table statistics:
F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80
SCHEMA.NAME CARD OV NP FP ACTBLK TSIZEF1F2F3 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 > 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
SCHEMA.NAME INDCARDLEAF ELEAF LVLSNDEL KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEADF4F5F6F7F8 REORG
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Table: DB2INST1.T_01XFF
Index: 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
$ db2 reorg table db2inst1.t_01xff index DB2INST1.I_T_01XFF allow read access
DB20000IThe REORG command completed successfully.
--在线reorg table
$ db2 reorg table db2inst1.t_01xff inplace allow write access
DB20000IThe REORG command completed successfully.
DB21024IThis command is asynchronous and may not be effective immediately.
$ db2 reorg table db2inst1.t_03xff inplace allow write access
DB20000IThe REORG command completed successfully.
DB21024IThis command is asynchronous and may not be effective immediately.
--证明异步操作完成
$ ps -ef|grep db2reo
db2inst1149613110 00:24 pts/1 00:00:00 grep db2reo
--检查reorg操作结果
$ db2 reorgchk on schema db2inst1
Doing RUNSTATS ....
Table statistics:
F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80
SCHEMA.NAME CARD OV NP FP ACTBLK TSIZEF1F2F3 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 > 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
SCHEMA.NAME INDCARDLEAF ELEAF LVLSNDEL KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEADF4F5F6F7F8 REORG
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Table: DB2INST1.T_01XFF
Index: 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
页:
[1]