DB2 runstats和reorg操作

浏览数：39 / 时间：2015年06月12日

[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 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‘)"
 
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.
 
--收集表和索引统计信息,包括数据分布
[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‘)"
 
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.904759
2012-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 
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    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 > 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                 
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_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
[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.
 
--证明异步操作完成
[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 
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    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 > 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                 
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_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