DB2 NPAGES FPAGES REORGCHK 区别解释

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

db2 connect to mydb
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 ---