DB2 NPAGES FPAGES REORGCHK 区别解释

不念不忘少年蓝@ 2022-09-20 12:59 160阅读 0赞

**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 ---