NPAGES :Total number of pages on which the rows of the table exist; -1 for a view or alias, or if statistics are not collected; -2 for a subtable or hierarchy table.
FPAGES :Total number of pages; -1 for a view or alias, or if statistics are not collected; -2 for a subtable or hierarchy table.
CARD:Total number of rows in the table; -1 if statistics are not collected.
npage 当前表有效数据占用页,fpage 当前表数据占用页,card 表中行数
举例(每行占用一页时):表占用6页,N=F=6 。删除两行后 N=4 F=6。以上三项可以在SYSCAT.TABLES表中查询到
REORGCHK:Calculates statistics on the database to determine if tables or indexes, or both, need to be reorganized or cleaned up.
计算数据库的表或者索引的统计信息,判断是否表或者索引或者两者都需要重组或者清理。
另外关于REORGCHK 中的REORG标志,还有NPAGES FPAGES的变化,引用一段ITPUB 中 askgyliu 的帖子内容(其中表数据初始化脚本有少许更改)
=============================================================================
总结内容::
reorgchk update statistics inplace notruncate 后 NPAGE 会减少 FPAGE不变
reorgchk update statistics不加参数情况下两者都会更新
(****inplace:: 联机reorg标志)
=============================================================================
db2 -v "create table mytab (f1 integer not null, f2 char(100) not null)"
create table mytab (f1 integer not null, f2 char(100) not null)
DB20000I The SQL command completed successfully.
cat run
idx=1
while [ $idx -le 10000 ]
do
#db2 "insert into mytab values($idx,'$idx')"
echo "$idx,\"$idx\"" >> imp
let idx="$idx+1"
if [ $[$idx%1000] -eq 0 ]
then
echo $idx
fi
done
db2 -v "import from imp of del insert into mytab"
db2 -v "runstats on table db2inst.mytab "
db2 -v "select npages, fpages, stats_time from syscat.tables where tabname='MYTAB'"
select npages, fpages, stats_time from syscat.tables where tabname='MYTAB'
NPAGES FPAGES STATS_TIME
----------- ----------- --------------------------
286 286 2007-10-12-13.22.17.665385
1 record(s) selected.
db2 -v "reorgchk update statistics on table db2inst.mytab"
reorgchk update statistics on table db2inst.mytab
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: DB2INST.MYTAB
DB2INST MYTAB 10000 0 286 286 - 1140000 0 99 100 ---
db2 -v "delete from mytab where mod(f1,100) between 0 and 19"
delete from mytab where mod(f1,100) between 0 and 19
DB20000I The SQL command completed successfully.
db2 -v "select count(*) from mytab"
select count(*) from mytab
1
-----------
8000
1 record(s) selected.
db2 -v "reorgchk update statistics on table db2inst.mytab"
reorgchk update statistics on table db2inst.mytab
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: DB2INST.MYTAB
DB2INST MYTAB 8000 0 286 286 - 912000 0 79 100 ---
db2 -v "reorg table db2inst.mytab inplace allow write access notruncate table start"
reorg table db2inst.mytab inplace allow write access notruncate table start
DB20000I The REORG command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
/home/db2inst/Scripts > db2 -v "reorgchk update statistics on table db2inst.mytab"
reorgchk update statistics on table db2inst.mytab
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: DB2INST.MYTAB
DB2INST MYTAB 8000 0 229 286 - 912000 0 79 80 --*
db2 -v "delete from mytab where mod(f1,100) between 0 and 27"
delete from mytab where mod(f1,100) between 0 and 27
DB20000I The SQL command completed successfully.
db2 -v "reorgchk update statistics on table db2inst.mytab"
reorgchk update statistics on table db2inst.mytab
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: DB2INST.MYTAB
DB2INST MYTAB 7200 0 229 286 - 820800 0 71 80 --*
db2 -v "reorgchk update statistics on table db2inst.mytab"
reorgchk update statistics on table db2inst.mytab
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: DB2INST.MYTAB
DB2INST MYTAB 7000 0 229 286 - 798000 0 69 80 -**
db2 -v "reorg table db2inst.mytab inplace allow write access notruncate table start"
reorg table db2inst.mytab inplace allow write access notruncate table start
DB20000I The REORG command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
db2 -v "reorgchk update statistics on table db2inst.mytab"
reorgchk update statistics on table db2inst.mytab
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: DB2INST.MYTAB
DB2INST MYTAB 7000 0 201 286 - 798000 0 69 70 -**
db2 -v "reorg table db2inst.mytab inplace allow write access start"
reorg table db2inst.mytab inplace allow write access start
DB20000I The REORG command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
db2 -v "reorgchk update statistics on table db2inst.mytab"
reorgchk update statistics on table db2inst.mytab
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: DB2INST.MYTAB
DB2INST MYTAB 7000 0 201 201 - 798000 0 99 100 ---