--删除部分表数据 [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. |