一次完整的DB2 reorg经历
来源：互联网 发布：第三方软件问题 编辑：程序博客网 时间：2024/06/05 17:23
事件：
ecmapp11的历史图片每天累积，TSM每天可以建立迁移任务，但却取不到任何数据；
与其关联的数据库rmdb11查询操作速度也超慢， 在和数据量相当的另一个数据库rmdb12上，同样的查询语句只需要花费1秒钟，但rmdb11要用十几分钟。
- 处理办法：
重建表和索引，清除叶子页碎片，可以有效提高数据库性能。
首先查询syscat.indexes表，查看STATS_TIME列，重要的用户表的索引在最近一次runstats的时间。 
从结果可以看到，几乎所有重要的用户表在创建之后，就从未做过runstats。
所以为了彻底的检查，哪些表和索引需要进行重建，需要对所有用户表做runstats检查。
一个完整的REORG表的过程应该是由下面的步骤组成的：
RUNSTATS:
- 登陆数据库：
db2 connect to rmdb11 user rmadmin using rmadmin对所有用户表执行runstats（reorgchk加update参数等同于runstats）
$ db2 reorgchk update statistics on table userDoing RUNSTATS ....REORG:
在检查结果中，所有带星号的表或分区表、以及索引都需要做reorg重建。
$ db2 reorg table RMADMIN.EXPLAIN_DIAGNOSTIC index SYSIBM.SQL120703164841960 use tempspace1DB20000I The REORG command completed successfully.$ db2 reorg table RMADMIN.EXPLAIN_DIAGNOSTIC_DATA index RMADMIN.EXP_DIAG_DAT_I1 use tempspace1DB20000I The REORG command completed successfully.$ db2 reorg table RMADMIN.EXPLAIN_PREDICATE index RMADMIN.PRD_I1 use tempspace1DB20000I The REORG command completed successfully.$ db2 reorg table RMADMIN.RMSTGGRPCLASS index SYSIBM.SQL120321193908820 use tempspace1DB20000I The REORG command completed successfully.$ db2 reorg table RMADMIN.RMOBJECTS use tempspace1SQL2217N The page size of the system temporary table space used by the REORG utility must match the page size of the table space(s) in which the table data resides (including the LONG or LOB column data). The cause is based on the following reason codes "1".SQL2217N
REORG 实用程序使用的系统临时表空间的页大小必须与表数据 （包括 LONG 或 LOB
列数据）所在表空间的页大小相匹配。原因基于下列原因码 原因码。 说明下面是原因码的列表：
1.原因与表的数据的临时表空间的选择相关。
2.原因与表的 LONG 或 LOB 数据的临时表空间的选择相关。 如果对 REORG 实用程序显式地指定了系统临时表，那么 REORG 实用程序使用的系统临时表空间的页大小必须与表数据（包括 LONG 或 LOB列数据）所在的表空间的页大小相匹配，否则必须为长数据指定适当的容器。下列其中一项违反了此限制：
表数据所在的表空间的页大小与指定的系统临时表空间的页大小不同。 该表包含 LONG 或 LOB列，这些列的数据驻留在页大小与系统临时表空间和表的规则数据的页大小不同的表空间中，但是，无法为 LONG 或 LOB数据对象找到具有正确页大小的表空间。 如果未对 REORG 实用程序指定系统临时表空间或 LONG临时表空间，那么该实用程序在内部查找系统临时表空间。在数据库中不存在使用与表数据页大小相同的页大小的系统临时表空间，或者该系统临时表空间此时不可用。用户响应
如果数据库中不存在使用与表数据页大小相同的页大小的系统临时表空间，请创建一个系统临时表空间，它使用与该表数据的页大小相匹配的页大小。如果表数据的页大小与
LOB 或 LONG 数据的页大小不同，那么应确保使用该页大小的系统临时表空间也存在。
如果数据库中存在使用与表数据页大小相同的页大小的系统临时表空间，但是发出命令时该临时表空间不可用，请在该系统临时表空间可用时重新发出该命令。
当前使用的临时表空间页大小和该表的页大小不符合，需要新建一个页大小和该表的页大小符合的系统临时表空间。
查看各个表空间的pagesize：
SELECT tbspace, pagesize FROM SYSIBM.SYSTABLESPACES查看当前bufferpool：
SELECT * FROM SYSCAT.BUFFERPOOLS新建一个页大小为32K的bufferpool
$ db2 CREATE BUFFERPOOL temppool32 PAGESIZE 32768DB20000I The SQL command completed successfully.新建一个临时表空间，使用刚才那个bufferpool
$ db2 "create system temporary tablespace tempspace3 pagesize 32K managed by system using ('/rmdb11data/rminst11/NODE0000/SQL00001/tmpspace3') BUFFERPOOL temppool32"DB20000I The SQL command completed successfully.重新执行reorg:
$ db2 reorg table RMADMIN.RMMIGRATIONTASKS index SYSIBM.SQL120321193909130 use tempspace3监视表重组：
select substr(tabname, 1, 15) as tab_name, substr(tabschema, 1, 15) as tab_schema, reorg_phase,reorg_max_phase, substr(reorg_type, 1, 20) as reorg_type, reorg_status, reorg_completion, dbpartitionnum from sysibmadm.snaptab_reorg order by dbpartitionnum或：
db2 GET SNAPSHOT FOR TABLES on rmdb11db2 list history reorg all for rmdb11db2pd -db rmdb11 -reorgs index
或：
$ db2pd -reorgs -db rmdb11Database Partition 0 -- Database RMDB11 -- Active -- Up 3 days 21:50:20 -- Date 10/30/2015 14:32:09Table Reorg Information:Address TbspaceID TableID PartID MasterTbs MasterTab TableName Type IndexID TempSpaceID0x070000024C0D1528 5 260 n/a n/a n/a EXPLAIN_PREDICATE Offline 1 1 0x070000024C0DEDA8 5 262 n/a n/a n/a EXPLAIN_DIAGNOSTIC Offline 1 1 0x070000024C0E6D28 5 263 n/a n/a n/a EXPLAIN_DIAGNOSTIC Offline 1 1 0x070000024B2C9628 7 5 n/a n/a n/a RMMIGRATIONTASKS Offline 1 13 0x070000024B2A64A8 5 17 n/a n/a n/a RMSTGGRPCLASS Offline 1 1 Table Reorg Stats:Address TableName Start End PhaseStart MaxPhase Phase CurCount MaxCount Status Completion0x070000024C0D1528 EXPLAIN_PREDICATE 10/30/2015 10:12:38 10/30/2015 10:12:38 10/30/2015 10:12:38 4 IdxRecreat 0 0 Done 0 0x070000024C0DEDA8 EXPLAIN_DIAGNOSTIC 10/30/2015 10:10:58 10/30/2015 10:10:59 10/30/2015 10:10:58 4 IdxRecreat 0 0 Done 0 0x070000024C0E6D28 EXPLAIN_DIAGNOSTIC 10/30/2015 10:12:13 10/30/2015 10:12:13 10/30/2015 10:12:13 4 IdxRecreat 0 0 Done 0 0x070000024B2C9628 RMMIGRATIONTASKS 10/30/2015 12:57:45 n/a 10/30/2015 14:17:16 4 IdxRecreat 202794 576060 Started 0 0x070000024B2A64A8 RMSTGGRPCLASS 10/30/2015 10:13:05 10/30/2015 10:13:05 10/30/2015 10:13:05 4 IdxRecreat 0 0 Done 0 $ 
完成！
参考资料：
重组处理期间将发生的最大重组阶段数。此项仅适用于经典（脱机）重组。值的范围为 2 到 4（SORT, BUILD,
REPLACE,INDEX_RECREATE）。此值还可能指示执行重组时为了从多维集群 (MDC)
表中回收扩展数据块而完成的工作总量。执行这样的重组时，此值是 3（SCAN、DRAIN 和 RELEASE）。对于分区表来说，重组是逐个数据分区进行的。对于传统表重组而言，可能的阶段如下所示（这些阶段与它们在 sqlmon.h
中的相应定义一起列示）： 排序：SQLM_REORG_SORT 构建：SQLM_REORG_BUILD
替换：SQLM_REORG_REPLACE 索引重新创建：SQLM_REORG_INDEX_RECREATE
字典构建：SQLM_REORG_DICT_SAMPLE
对于分区表而言，在数据分区的“替换”阶段完成后，可以直接进入分区索引（如果有的话）的“索引重建”阶段。仅当每个数据分区上的所有先前阶段成功完成后，reorg_phase
元素才会指示“索引重新创建”阶段。在 XDA 对象压缩期间，XML 数据重组阶段涉及识别表的 XML 存储器对象。XML 字典构建阶段涉及尝试为 XML
存储器对象创建压缩字典。对于 XDA 对象压缩而言，可能的两个阶段如下所示： XML 重组：SQLM_REORG_XML_DATA XML
字典构建：SQLM_REORG_XML_DICT_SAMPLE 对于分区表，在执行扩展数据块回收操作时，可能的阶段如下所示：
扫描：SQLM_REORG_SCAN 漏出：SQLM_REORG_DRAIN 释放：SQLM_REORG_RELEASE
$ db2 reorg indexes all for table RMADMIN.RMTRANSACTIONS ALLOW write ACCESS cleanup onlyDB20000I The REORG command completed successfully.分区表reorg
RMADMIN.RMOBJECTS是分区表，reorg需要指定data partition number名称，
总共有62个分区，标记F1的有39个分区，2个索引分区标记F8
Index: RMADMIN.IDX_OBJ_STATUS
Data Partition: EXCEP2
Index: SYSIBM.SQL120528224438680
Data Partition: EXCEP2
参考:
查询该表分区情况：
select * from SYSCAT.DATAPARTITIONS where tabname='RMOBJECTS'
db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P13_1
db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P13_2db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P13_3db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P13_4db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P13_5db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P13_6db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P13_7db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P13_8db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P13_9db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P13_10db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P13_11db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P13_12db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P13_13db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P12_10db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P12_11db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P12_12db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P12_6db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P12_7db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P12_8db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P12_9db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P14_1db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P14_2db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P14_3db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P14_4db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P14_5db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P14_6db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P14_7db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P14_8db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P14_9db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P14_10db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P14_11db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P14_12db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P15_1db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P15_2db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P15_3db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P15_4db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P15_5监控reorg状态：
$ db2pd -db rmdb11 -reorgs indexDatabase Partition 0 -- Database RMDB11 -- Active -- Up 5 days 04:54:34 -- Date 10/31/2015 21:36:23Table Reorg Information:Address TbspaceID TableID PartID MasterTbs MasterTab TableName Type IndexID TempSpaceID0x070000024B2DC028 3 12 6 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B2E0E28 3 13 7 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B65C928 3 14 8 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B2DF328 3 15 9 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B66BF28 3 16 10 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B66E928 3 17 11 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B6716A8 3 18 12 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B658028 3 19 13 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B4EEAA8 3 20 14 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B4E6DA8 3 21 15 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B6109A8 3 22 16 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B6305A8 3 23 17 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B5BD128 3 24 18 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B532528 3 25 19 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B519CA8 3 26 20 -6 -32768 RMOBJECTS Offline 2 3 0x070000024C2AAE28 3 27 21 -6 -32768 RMOBJECTS Offline 2 3 0x070000024C2C15A8 3 28 22 -6 -32768 RMOBJECTS Offline 2 3 0x070000024C2C42A8 3 29 23 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B516728 3 30 24 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B5A79A8 3 31 25 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B52A628 3 32 26 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B533A28 3 33 27 -6 -32768 RMOBJECTS Offline 2 3 0x070000024C2BE7A8 3 34 28 -6 -32768 RMOBJECTS Offline 2 3 0x070000024C2BFF28 3 35 29 -6 -32768 RMOBJECTS Offline 2 3 0x070000024C2C2C28 3 36 30 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B4F4FA8 3 37 31 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B4EC3A8 3 38 32 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B4ED828 3 39 33 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B306828 3 40 34 -6 -32768 RMOBJECTS Offline 2 3 0x07000000400101A8 3 41 35 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B670028 3 42 36 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B53DBA8 3 43 37 -6 -32768 RMOBJECTS Offline 2 3 0x070000024B517DA8 3 44 38 -6 -32768 RMOBJECTS Offline 2 3 0x070000024C0ACA28 3 45 39 -6 -32768 RMOBJECTS Offline 2 3 0x070000024C292028 3 46 40 -6 -32768 RMOBJECTS Offline 2 3 0x070000024C29ABA8 3 47 41 -6 -32768 RMOBJECTS Offline 2 3 0x070000024C0D1528 5 260 n/a n/a n/a EXPLAIN_PREDICATE Offline 1 1 0x070000024C0DEDA8 5 262 n/a n/a n/a EXPLAIN_DIAGNOSTIC Offline 1 1 0x070000024C0E6D28 5 263 n/a n/a n/a EXPLAIN_DIAGNOSTIC Offline 1 1 0x070000024B2C9628 7 5 n/a n/a n/a RMMIGRATIONTASKS Offline 1 13 0x070000024B2A64A8 5 17 n/a n/a n/a RMSTGGRPCLASS Offline 1 1 Table Reorg Stats:Address TableName Start End PhaseStart MaxPhase Phase CurCount MaxCount Status Completion0x070000024B2DC028 RMOBJECTS 10/31/2015 11:10:14 10/31/2015 20:52:09 10/31/2015 11:10:14 4 IdxRecreat 0 0 Done 0 0x070000024B2E0E28 RMOBJECTS 10/31/2015 11:10:15 10/31/2015 20:52:09 10/31/2015 11:44:30 4 IdxRecreat 0 0 Done 0 0x070000024B65C928 RMOBJECTS 10/31/2015 11:51:37 10/31/2015 20:52:09 10/31/2015 12:15:37 4 IdxRecreat 0 0 Done 0 0x070000024B2DF328 RMOBJECTS 10/31/2015 12:20:41 10/31/2015 20:52:09 10/31/2015 12:39:03 4 IdxRecreat 0 0 Done 0 0x070000024B66BF28 RMOBJECTS 10/31/2015 09:46:53 10/31/2015 20:52:09 10/31/2015 10:07:56 4 IdxRecreat 0 0 Done 0 0x070000024B66E928 RMOBJECTS 10/31/2015 10:13:55 10/31/2015 20:52:09 10/31/2015 10:34:53 4 IdxRecreat 0 0 Done 0 0x070000024B6716A8 RMOBJECTS 10/31/2015 10:41:15 10/31/2015 20:52:09 10/31/2015 11:04:26 4 IdxRecreat 0 0 Done 0 0x070000024B658028 RMOBJECTS 10/30/2015 16:34:06 10/31/2015 20:52:09 10/30/2015 17:49:05 4 IdxRecreat 0 0 Done 0 0x070000024B4EEAA8 RMOBJECTS 10/30/2015 19:49:59 10/31/2015 20:52:09 10/30/2015 20:55:18 4 IdxRecreat 0 0 Done 0 0x070000024B4E6DA8 RMOBJECTS 10/30/2015 21:06:51 10/31/2015 20:52:09 10/30/2015 22:29:49 4 IdxRecreat 0 0 Done 0 0x070000024B6109A8 RMOBJECTS 10/30/2015 22:46:58 10/31/2015 20:52:09 10/31/2015 00:20:22 4 IdxRecreat 0 0 Done 0 0x070000024B6305A8 RMOBJECTS 10/31/2015 00:37:26 10/31/2015 20:52:09 10/31/2015 02:09:15 4 IdxRecreat 0 0 Done 0 0x070000024B5BD128 RMOBJECTS 10/31/2015 02:26:12 10/31/2015 20:52:09 10/31/2015 03:49:52 4 IdxRecreat 0 0 Done 0 0x070000024B532528 RMOBJECTS 10/31/2015 04:04:29 10/31/2015 20:52:09 10/31/2015 05:23:13 4 IdxRecreat 0 0 Done 0 0x070000024B519CA8 RMOBJECTS 10/31/2015 05:38:18 10/31/2015 20:52:09 10/31/2015 06:46:21 4 IdxRecreat 0 0 Done 0 0x070000024C2AAE28 RMOBJECTS 10/31/2015 06:58:19 10/31/2015 20:52:09 10/31/2015 08:16:15 4 IdxRecreat 0 0 Done 0 0x070000024C2C15A8 RMOBJECTS 10/31/2015 08:23:40 10/31/2015 20:52:09 10/31/2015 08:47:02 4 IdxRecreat 0 0 Done 0 0x070000024C2C42A8 RMOBJECTS 10/31/2015 08:51:45 10/31/2015 20:52:09 10/31/2015 09:14:50 4 IdxRecreat 0 0 Done 0 0x070000024B516728 RMOBJECTS 10/31/2015 09:20:04 10/31/2015 20:52:09 10/31/2015 09:41:50 4 IdxRecreat 0 0 Done 0 0x070000024B5A79A8 RMOBJECTS 10/31/2015 12:44:04 10/31/2015 20:52:09 10/31/2015 12:57:16 4 IdxRecreat 0 0 Done 0 0x070000024B52A628 RMOBJECTS 10/31/2015 13:01:35 10/31/2015 20:52:09 10/31/2015 13:17:51 4 IdxRecreat 0 0 Done 0 0x070000024B533A28 RMOBJECTS 10/31/2015 13:21:33 10/31/2015 20:52:09 10/31/2015 13:48:53 4 IdxRecreat 0 0 Done 0 0x070000024C2BE7A8 RMOBJECTS 10/31/2015 13:56:51 10/31/2015 20:52:09 10/31/2015 14:18:49 4 IdxRecreat 0 0 Done 0 0x070000024C2BFF28 RMOBJECTS 10/31/2015 14:25:14 10/31/2015 20:52:09 10/31/2015 14:48:14 4 IdxRecreat 0 0 Done 0 0x070000024C2C2C28 RMOBJECTS 10/31/2015 14:54:35 10/31/2015 20:52:09 10/31/2015 15:15:41 4 IdxRecreat 0 0 Done 0 0x070000024B4F4FA8 RMOBJECTS 10/31/2015 15:21:16 10/31/2015 20:52:09 10/31/2015 15:42:21 4 IdxRecreat 0 0 Done 0 0x070000024B4EC3A8 RMOBJECTS 10/31/2015 15:48:09 10/31/2015 20:52:09 10/31/2015 16:08:41 4 IdxRecreat 0 0 Done 0 0x070000024B4ED828 RMOBJECTS 10/31/2015 16:13:59 10/31/2015 20:52:09 10/31/2015 16:36:37 4 IdxRecreat 0 0 Done 0 0x070000024B306828 RMOBJECTS 10/31/2015 16:42:19 10/31/2015 20:52:09 10/31/2015 17:06:28 4 IdxRecreat 0 0 Done 0 0x07000000400101A8 RMOBJECTS 10/31/2015 17:13:01 10/31/2015 20:52:09 10/31/2015 17:37:51 4 IdxRecreat 0 0 Done 0 0x070000024B670028 RMOBJECTS 10/31/2015 17:44:17 10/31/2015 20:52:09 10/31/2015 18:10:03 4 IdxRecreat 0 0 Done 0 0x070000024B53DBA8 RMOBJECTS 10/31/2015 18:16:50 10/31/2015 20:52:09 10/31/2015 18:42:39 4 IdxRecreat 0 0 Done 0 0x070000024B517DA8 RMOBJECTS 10/31/2015 18:48:40 10/31/2015 20:52:09 10/31/2015 19:11:39 4 IdxRecreat 0 0 Done 0 0x070000024C0ACA28 RMOBJECTS 10/31/2015 19:17:01 10/31/2015 20:52:09 10/31/2015 19:44:46 4 IdxRecreat 0 0 Done 0 0x070000024C292028 RMOBJECTS 10/31/2015 19:52:19 10/31/2015 20:52:09 10/31/2015 20:10:40 4 IdxRecreat 0 0 Done 0 0x070000024C29ABA8 RMOBJECTS 10/31/2015 20:15:35 10/31/2015 20:52:09 10/31/2015 20:44:10 4 IdxRecreat 0 0 Done 0 0x070000024C0D1528 EXPLAIN_PREDICATE 10/30/2015 10:12:38 10/30/2015 10:12:38 10/30/2015 10:12:38 4 IdxRecreat 0 0 Done 0 0x070000024C0DEDA8 EXPLAIN_DIAGNOSTIC 10/30/2015 10:10:58 10/30/2015 10:10:59 10/30/2015 10:10:58 4 IdxRecreat 0 0 Done 0 0x070000024C0E6D28 EXPLAIN_DIAGNOSTIC 10/30/2015 10:12:13 10/30/2015 10:12:13 10/30/2015 10:12:13 4 IdxRecreat 0 0 Done 0 0x070000024B2C9628 RMMIGRATIONTASKS 10/30/2015 12:57:45 10/30/2015 14:35:29 10/30/2015 14:17:16 4 IdxRecreat 0 0 Done 0 0x070000024B2A64A8 RMSTGGRPCLASS 10/30/2015 10:13:05 10/30/2015 10:13:05 10/30/2015 10:13:05 4 IdxRecreat 0 0 Done 0 Index Reorg Stats:$ 注：
查询每个RID（记录标识索引）都被标记为已删除的叶子页的数目，查询syscat.indexes和sysstat.indexes统计信息表的num_empty_leafs列。对于并非所有RID都标记为删除的叶子页，逻辑上已删除的RID的总数在numrids_deleted列中。
使用此信息来通过执行带cleanup all选项的reorg
indexes估计可以回收多少空间。要想只回收所有RID都被标记为已删除的页中的空间，执行带有cleanup only
pages选项的reorg indexes。
select indschema, indname, tabschema, tabname, num_empty_leafs from syscat.indexes where num_empty_leafs>'0'
db2 reorg indexes all for table RMADMIN.RMTRANSACTIONS ALLOW write ACCESS cleanup only pagesdb2 reorg indexes all for table RMADMIN.RMMIGRATIONTASKS ALLOW READ ACCESS cleanup only pages分别对各个表再次执行runstats更新信息：
RMADMIN.RMTRANSACTIONS
db2 runstats on table RMADMIN.RMTRANSACTIONS and indexes all分别对各个表检查reorg结果：
RMADMIN.RMTRANSACTIONS
$ db2 reorgchk current statistics on table RMADMIN.RMTRANSACTIONSTable statistics:F1: 100 * OVERFLOW / CARD < 5F2: 100 * (Effective Space Utilization of Data Pages) > 70F3: 100 * (Required Pages / Total Pages) > 80SCHEMA.NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG----------------------------------------------------------------------------------------Table: RMADMIN.RMTRANSACTIONS 3785509 0 216521 218144 - 5.00e+08 0 56 99 -*- ----------------------------------------------------------------------------------------Index statistics:F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80F5: 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) < 100F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20SCHEMA.NAME INDCARD LEAF ELEAF LVLS NDEL KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD PCT_PAGES_SAVED F4 F5 F6 F7 F8 REORG -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Table: RMADMIN.RMTRANSACTIONSIndex: RMADMIN.IDX1209281637520 3785509 25001 0 4 30 3723968 10 10 516 516 0 85 79 95 0 0 ----- Index: RMADMIN.IDX1209290422090 3785509 38684 0 5 122 3724039 21 21 370 370 0 85 77 +++ 0 0 ----- Index: RMADMIN.TRAN_ID_X1 3785509 90830 0 5 30890 3785509 49 49 354 354 0 53 64 241 0 0 *-*-- Index: RMADMIN.TRAN_TMP_ID_X0 3785509 9990 0 3 7488 1 11 11 566 566 0 100 75 1 0 0 ----- Index: SYSIBM.SQL120321193909330 3785509 74157 0 5 627 3785509 46 46 370 370 0 84 75 298 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.$db2 reorg table RMADMIN.RMTRANSACTIONS index RMADMIN.TRAN_ID_X1再次runstats检查reorg结果
$ db2 runstats on table RMADMIN.RMTRANSACTIONS and indexes allDB20000I The RUNSTATS command completed successfully.$ db2 reorgchk current statistics on table RMADMIN.RMTRANSACTIONSTable statistics:F1: 100 * OVERFLOW / CARD < 5F2: 100 * (Effective Space Utilization of Data Pages) > 70F3: 100 * (Required Pages / Total Pages) > 80SCHEMA.NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG----------------------------------------------------------------------------------------Table: RMADMIN.RMTRANSACTIONS 3785509 0 126252 126252 - 5.00e+08 0 98 100 --- ----------------------------------------------------------------------------------------Index statistics:F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80F5: 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) < 100F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20SCHEMA.NAME INDCARD LEAF ELEAF LVLS NDEL KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD PCT_PAGES_SAVED F4 F5 F6 F7 F8 REORG -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Table: RMADMIN.RMTRANSACTIONSIndex: RMADMIN.IDX1209281637520 3785509 21907 0 4 0 3723968 10 10 516 516 0 70 90 95 0 0 *---- Index: RMADMIN.IDX1209290422090 3785509 33371 0 4 0 3724039 21 21 370 370 0 70 90 29 0 0 *---- Index: RMADMIN.TRAN_ID_X1 3785509 65268 0 4 0 3785509 49 49 354 354 0 100 89 4 0 0 ----- Index: RMADMIN.TRAN_TMP_ID_X0 3785509 8124 0 3 0 1 11 11 566 566 0 100 92 1 0 0 ----- Index: SYSIBM.SQL120321193909330 3785509 62058 0 4 0 3785509 46 46 370 370 0 72 90 5 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.$ db2 reorg table RMADMIN.RMTRANSACTIONS index RMADMIN.IDX1209281637520DB20000I The REORG command completed successfully.$ db2 runstats on table RMADMIN.RMTRANSACTIONS and indexes allDB20000I The RUNSTATS command completed successfully.$ db2 reorgchk current statistics on table RMADMIN.RMTRANSACTIONSTable statistics:F1: 100 * OVERFLOW / CARD < 5F2: 100 * (Effective Space Utilization of Data Pages) > 70F3: 100 * (Required Pages / Total Pages) > 80SCHEMA.NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG----------------------------------------------------------------------------------------Table: RMADMIN.RMTRANSACTIONS 3785509 0 126252 126252 - 5.00e+08 0 98 100 --- ----------------------------------------------------------------------------------------Index statistics:F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80F5: 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) < 100F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20SCHEMA.NAME INDCARD LEAF ELEAF LVLS NDEL KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD PCT_PAGES_SAVED F4 F5 F6 F7 F8 REORG -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Table: RMADMIN.RMTRANSACTIONSIndex: RMADMIN.IDX1209281637520 3785509 21907 0 4 0 3723968 10 10 516 516 0 100 90 95 0 0 ----- Index: RMADMIN.IDX1209290422090 3785509 33371 0 4 0 3724039 21 21 370 370 0 100 90 29 0 0 ----- Index: RMADMIN.TRAN_ID_X1 3785509 65268 0 4 0 3785509 49 49 354 354 0 54 89 4 0 0 *---- Index: RMADMIN.TRAN_TMP_ID_X0 3785509 8124 0 3 0 1 11 11 566 566 0 100 92 1 0 0 ----- Index: SYSIBM.SQL120321193909330 3785509 62058 0 4 0 3785509 46 46 370 370 0 98 90 5 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.$ db2 reorg table RMADMIN.RMTRANSACTIONS index RMADMIN.TRAN_ID_X1db2 reorg index RMADMIN.TRAN_ID_X1 for table RMADMIN.RMTRANSACTIONS只有分区表的非分区索引才支持 REORG INDEX
RMADMIN.RMMIGRATIONTASKS
$db2 runstats on table RMADMIN.RMMIGRATIONTASKS and indexes all$db2 reorgchk current statistics on table RMADMIN.RMMIGRATIONTASKSRMADMIN.RMOBJECTS
$db2 runstats on table RMADMIN.RMOBJECTS and indexes allDB20000I The RUNSTATS command completed successfully.$ db2 reorgchk current statistics on table RMADMIN.RMOBJECTS Table statistics:F1: 100 * OVERFLOW / CARD < 5F2: 100 * (Effective Space Utilization of Data Pages) > 70F3: 100 * (Required Pages / Total Pages) > 80SCHEMA.NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG----------------------------------------------------------------------------------------Table: RMADMIN.RMOBJECTS 2.1e+09 4e+07 1e+07 1e+07 - 4.73e+11 1 99 98 --- Table: RMADMIN.RMOBJECTSData Partition: EXCEP1 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: EXCEP2 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P12_1 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P12_10 4.9e+07 0 345856 345856 - 1.11e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P12_11 4.7e+07 0 334529 334529 - 1.08e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P12_12 4.9e+07 0 344180 344180 - 1.11e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P12_2 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P12_3 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P12_4 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P12_5 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P12_6 12733 0 90 90 - 2890391 0 100 100 --- Table: RMADMIN.RMOBJECTSData Partition: P12_7 6.5e+07 0 461135 461135 - 1.49e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P12_8 5.1e+07 0 359681 359681 - 1.16e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P12_9 4.6e+07 0 321037 321037 - 1.03e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P13_1 4.2e+07 0 294971 294971 - 9.51e+09 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P13_10 4.6e+07 0 325151 325151 - 1.05e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P13_11 4.4e+07 0 311825 311825 - 1.01e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P13_12 4.5e+07 0 316732 316732 - 1.02e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P13_2 3.5e+07 0 249172 249172 - 8.03e+09 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P13_3 4.6e+07 0 325625 325625 - 1.05e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P13_4 4.6e+07 0 322961 322961 - 1.04e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P13_5 4.6e+07 0 322607 322607 - 1.04e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P13_6 4.5e+07 0 316496 316496 - 1.02e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P13_7 4.3e+07 0 302739 302739 - 9.76e+09 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P13_8 4.2e+07 0 293236 293236 - 9.45e+09 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P13_9 4.5e+07 0 315234 315234 - 1.02e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P14_1 3.6e+07 0 251428 251428 - 8.11e+09 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P14_10 5.8e+07 0 407705 407705 - 1.31e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P14_11 6.1e+07 0 427554 427554 - 1.38e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P14_12 6.4e+07 0 451487 451487 - 1.46e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P14_2 3.3e+07 0 234232 234232 - 7.55e+09 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P14_3 6.3e+07 0 440408 440408 - 1.42e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P14_4 5.1e+07 0 361129 361129 - 1.16e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P14_5 5.4e+07 0 378889 378889 - 1.22e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P14_6 5.0e+07 0 353851 353851 - 1.14e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P14_7 5.0e+07 0 354353 354353 - 1.14e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P14_8 5.2e+07 0 367597 367597 - 1.18e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P14_9 5.5e+07 0 387620 387620 - 1.25e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P15_1 6.2e+07 0 434088 434088 - 1.40e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P15_10 5.0e+07 0 298644 298644 - 1.15e+10 0 100 100 --- Table: RMADMIN.RMOBJECTSData Partition: P15_11 764519 0 4525 4525 - 1.74e+08 0 100 100 --- Table: RMADMIN.RMOBJECTSData Partition: P15_12 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P15_2 5.2e+07 0 367455 367455 - 1.18e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P15_3 6.5e+07 0 454604 454604 - 1.47e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P15_4 4.5e+07 0 314146 314146 - 1.01e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P15_5 6.7e+07 0 472224 472224 - 1.52e+10 0 98 100 --- Table: RMADMIN.RMOBJECTSData Partition: P15_6 6.8e+07 1e+07 405310 490372 - 1.55e+10 17 96 82 *-- Table: RMADMIN.RMOBJECTSData Partition: P15_7 6.3e+07 1e+07 376494 453310 - 1.44e+10 17 96 83 *-- Table: RMADMIN.RMOBJECTSData Partition: P15_8 7.2e+07 1e+07 427227 514812 - 1.63e+10 17 96 82 *-- Table: RMADMIN.RMOBJECTSData Partition: P15_9 8.0e+07 2e+06 470933 486173 - 1.81e+10 2 100 96 --- Table: RMADMIN.RMOBJECTSData Partition: P16_1 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P16_10 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P16_11 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P16_12 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P16_2 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P16_3 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P16_4 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P16_5 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P16_6 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P16_7 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P16_8 0 0 0 1 - 0 0 - 0 --- Table: RMADMIN.RMOBJECTSData Partition: P16_9 0 0 0 1 - 0 0 - 0 --- ----------------------------------------------------------------------------------------Index statistics:F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80F5: 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) < 100F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20SCHEMA.NAME INDCARD LEAF ELEAF LVLS NDEL KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD PCT_PAGES_SAVED F4 F5 F6 F7 F8 REORG Table: RMADMIN.RMOBJECTSIndex: RMADMIN.IDX_OBJ_STATUSData Partition: EXCEP1 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P12_1 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P12_2 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P12_3 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P12_4 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P12_5 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P12_6 12733 12 0 2 0 12733 15 15 2818 2818 0 100 92 - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P12_7 6.5e+07 57849 0 3 0 6.5e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P12_8 5.1e+07 45142 0 3 0 5.1e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P12_9 4.6e+07 40300 0 3 0 4.6e+07 15 15 2818 2818 0 100 90 3 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P12_10 4.9e+07 43420 0 3 0 4.9e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P12_11 4.7e+07 41997 0 3 0 4.7e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P12_12 4.9e+07 43208 0 3 0 4.9e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P13_1 4.2e+07 37028 0 3 0 4.2e+07 15 15 2818 2818 0 100 90 3 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P13_2 3.5e+07 31281 0 3 0 3.5e+07 15 15 2818 2818 0 100 90 3 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P13_3 4.6e+07 40878 0 3 0 4.6e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P13_4 4.6e+07 40544 0 3 0 4.6e+07 15 15 2818 2818 0 100 90 3 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P13_5 4.6e+07 40500 0 3 0 4.6e+07 15 15 2818 2818 0 100 90 3 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P13_6 4.5e+07 39733 0 3 0 4.5e+07 15 15 2818 2818 0 100 90 3 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P13_7 4.3e+07 38006 0 3 0 4.3e+07 15 15 2818 2818 0 100 90 3 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P13_8 4.2e+07 36813 0 3 0 4.2e+07 15 15 2818 2818 0 100 90 3 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P13_9 4.5e+07 39578 0 3 0 4.5e+07 15 15 2818 2818 0 100 90 3 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P13_10 4.6e+07 40823 0 3 0 4.6e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P13_11 4.4e+07 39151 0 3 0 4.4e+07 15 15 2818 2818 0 100 90 3 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P13_12 4.5e+07 39763 0 3 0 4.5e+07 15 15 2818 2818 0 100 90 3 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P14_1 3.6e+07 31565 0 3 0 3.6e+07 15 15 2818 2818 0 100 90 3 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P14_2 3.3e+07 29405 0 3 0 3.3e+07 15 15 2818 2818 0 100 90 4 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P14_3 6.3e+07 55299 0 3 0 6.3e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P14_4 5.1e+07 45338 0 3 0 5.1e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P14_5 5.4e+07 47573 0 3 0 5.4e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P14_6 5.0e+07 44424 0 3 0 5.0e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P14_7 5.0e+07 44486 0 3 0 5.0e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P14_8 5.2e+07 46148 0 3 0 5.2e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P14_9 5.5e+07 48670 0 3 0 5.5e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P14_10 5.8e+07 51181 0 3 0 5.8e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P14_11 6.1e+07 53672 0 3 0 6.1e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P14_12 6.4e+07 56679 0 3 0 6.4e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P15_1 6.2e+07 54490 0 3 0 6.2e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P15_2 5.2e+07 46122 0 3 0 5.2e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P15_3 6.5e+07 57062 0 3 0 6.5e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P15_4 4.5e+07 39440 0 3 0 4.5e+07 15 15 2818 2818 0 100 90 3 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P15_5 6.7e+07 59280 0 3 0 6.7e+07 15 15 2818 2818 0 100 90 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P15_6 6.8e+07 61978 0 3 71146 6.8e+07 15 15 2818 2818 0 100 88 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P15_7 6.3e+07 57693 0 3 54682 6.3e+07 15 15 2818 2818 0 100 87 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P15_8 7.2e+07 65746 0 3 59690 7.2e+07 15 15 2818 2818 0 100 87 1 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P15_9 8.0e+07 72191 0 3 64360 8.0e+07 15 15 2818 2818 0 99 88 1 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P15_10 5.0e+07 46007 0 3 14649 5.0e+07 15 15 2818 2818 0 99 87 2 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P15_11 764519 684 0 2 1 764388 15 15 2818 2818 0 99 89 - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P15_12 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P16_1 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P16_2 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P16_3 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P16_4 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P16_5 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P16_6 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P16_7 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P16_8 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P16_9 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P16_10 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P16_11 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: P16_12 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: RMADMIN.IDX_OBJ_STATUSData Partition: EXCEP2 0 1 1 1 0 0 15 15 2818 2818 0 100 - - 0 100 ----* Index: SYSIBM.SQL120528224438680Data Partition: EXCEP1 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P12_1 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P12_2 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P12_3 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P12_4 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P12_5 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P12_6 12733 20 0 2 0 12733 34 34 1614 1614 0 99 92 - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P12_7 6.5e+07 1e+05 0 3 0 6.5e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P12_8 5.1e+07 78210 0 3 0 5.1e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P12_9 4.6e+07 69808 0 3 0 4.6e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P12_10 4.9e+07 75204 0 3 0 4.9e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P12_11 4.7e+07 72742 0 3 0 4.7e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P12_12 4.9e+07 74840 0 3 0 4.9e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P13_1 4.2e+07 64140 0 3 0 4.2e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P13_2 3.5e+07 54181 0 3 0 3.5e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P13_3 4.6e+07 70805 0 3 0 4.6e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P13_4 4.6e+07 70226 0 3 0 4.6e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P13_5 4.6e+07 70149 0 3 0 4.6e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P13_6 4.5e+07 68820 0 3 0 4.5e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P13_7 4.3e+07 65829 0 3 0 4.3e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P13_8 4.2e+07 63762 0 3 0 4.2e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P13_9 4.5e+07 68555 0 3 0 4.5e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P13_10 4.6e+07 70711 0 3 0 4.6e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P13_11 4.4e+07 67816 0 3 0 4.4e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P13_12 4.5e+07 68877 0 3 0 4.5e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P14_1 3.6e+07 54681 0 3 0 3.6e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P14_2 3.3e+07 50933 0 3 0 3.3e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P14_3 6.3e+07 95790 0 3 0 6.3e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P14_4 5.1e+07 78527 0 3 0 5.1e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P14_5 5.4e+07 82399 0 3 0 5.4e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P14_6 5.0e+07 76951 0 3 0 5.0e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P14_7 5.0e+07 77053 0 3 0 5.0e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P14_8 5.2e+07 79935 0 3 0 5.2e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P14_9 5.5e+07 84307 0 3 0 5.5e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P14_10 5.8e+07 88654 0 3 0 5.8e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P14_11 6.1e+07 92970 0 3 0 6.1e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P14_12 6.4e+07 98181 0 3 0 6.4e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P15_1 6.2e+07 94400 0 3 0 6.2e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P15_2 5.2e+07 79902 0 3 0 5.2e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P15_3 6.5e+07 98855 0 3 0 6.5e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P15_4 4.5e+07 68326 0 3 0 4.5e+07 34 34 1614 1614 0 99 90 1 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P15_5 6.7e+07 1e+05 0 3 0 6.7e+07 34 34 1614 1614 0 99 90 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P15_6 6.8e+07 1e+05 0 3 0 6.8e+07 34 34 1614 1614 0 100 85 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P15_7 6.3e+07 1e+05 0 3 0 6.3e+07 34 34 1614 1614 0 100 86 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P15_8 7.2e+07 1e+05 0 3 2 7.2e+07 34 34 1614 1614 0 100 85 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P15_9 8.0e+07 1e+05 0 3 1 8.0e+07 34 34 1614 1614 0 100 84 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P15_10 5.0e+07 92837 0 3 11 5.0e+07 34 34 1614 1614 0 99 75 0 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P15_11 764519 1194 0 3 0 764519 34 34 1614 1614 0 100 88 64 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P15_12 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P16_1 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P16_2 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P16_3 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P16_4 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P16_5 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P16_6 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P16_7 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P16_8 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P16_9 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P16_10 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P16_11 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: P16_12 0 1 0 1 0 0 0 0 7356 7356 0 100 - - 0 0 ----- Index: SYSIBM.SQL120528224438680Data Partition: EXCEP2 0 1 1 1 0 0 34 34 1614 1614 0 100 - - 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.db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P15_6db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P15_7db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION P15_8db2 reorg table RMADMIN.RMOBJECTS index SYSIBM.SQL120528224438680 allow no access ON DATA PARTITION EXCEP2db2 reorg table RMADMIN.RMOBJECTS index RMADMIN.IDX_OBJ_STATUS allow no access ON DATA PARTITION EXCEP2- 继续对RMADMIN.RMOBJECTS执行runstat:
db2 runstats on table RMADMIN.RMOBJECTS and indexes alldb2 reorgchk current statistics on table RMADMIN.RMOBJECTS全部检查通过。
rebind
db2rbind$ db2rbind rmdb11 -l db2rbind.out Rebind done successfully for database 'RMDB11'.$ 结果：
ecmapp11历史图片已可以进行迁移！说明表和索引重建成功！
检查索引中还存在已被标识为删除的RID的索引：
查询结果为0，说明重建成功完成。
0 0
- 一次完整的DB2 reorg经历
- 完整的DB2 REORG案例
- DB2 reorg
- db2 reorg
- DB2 REORG TABLE
- DB2 循环 ，导入， Reorg
- db2 reorg优化及原因
- DB2 runstats、reorgchk、reorg 命令
- DB2 Online Reorg
- DB2 runstats、reorgchk、reorg 命令
- DB2日常维护 - 介绍REORG
- 经历的一次诈骗
- 一次全站挂马的经历
- 一次失败的经历
- 一次演讲的经历
- 一次惨痛的经历
- 一次完整的HTTP请求所经历的7个步骤
- 一次完整的HTTP请求所经历的7个步骤
- c++对文件读写的操作
- 解决GridView,ListView嵌套在ScrollView时只显示一行高度的问题
- CRC
- hdu5521Meeting(dijkstra最短路)icpc沈阳赛区M题
- JDK中JRE与Program Files中的JRE的区别
- 一次完整的DB2 reorg经历
- how nginx processes a request
- 【JAVA大作业开发记录（一）】
- 百度地图(基本定位和地理编码)
- 三个球的旋转的动画
- 虚幻引擎 4.9
- 素数
- 用JDK中的 jps、jinfo、jstat、jstack、jmap、jconsole等命令对JVM,内存，线程进行分析和故障诊断
- OKHttp源码解析（二）
