Using snapshot monitor data to monitor the reorganization of a partitioned table
The following information describes some of the most useful methods of monitoring the global status of a table reorganization.
About this task
There is no separate data group indicating the overall table reorganization status for a partitioned table. A partitioned table uses a data organization scheme in which table data is divided across multiple storage objects, called data partitions or ranges, according to values in one or more table partitioning key columns of the table. However, you can deduce the global status of a table reorganization from the values of elements in the individual data partition data group being reorganized. The following information describes some of the most useful methods of monitoring the global status of a table reorganization.
- Determining the number of data partitions being reorganized
- You can determine the total number of data partitions being reorganized on a table by counting the number of monitor data blocks for table data that have the same table name and schema name. This value indicates the number of data partitions on which reorganization has started. Examples 1 and 2 indicate that three data partitions are being reorganized.
- Identifying the data partition being reorganized
- You can deduce the current data partition being reorganized from the phase start time (reorg_phase_start). During the SORT/BUILD/REPLACE phase, the monitor data corresponding to the data partition that is being reorganized shows the most recent phase start time. During the INDEX_RECREATE phase, the phase start time is the same for all the data partitions. In Examples 1 and 2, the INDEX_RECREATE phase is indicated, so the start time is the same for all the data partitions.
- Identifying an index rebuild requirement
- You can determine if an index rebuild is required by obtaining the value of the maximum reorganize phase element (reorg_max_phase), corresponding to any one of the data partitions being reorganized. If reorg_max_phase has a value of 3 or 4, then an Index Rebuild is required. Examples 1 and 2 report a reorg_max_phase value of 3, indicating an index rebuild is required.
Examples
The following sample output is from a three-node server that contains a table with three data partitions:
CREATE TABLE sales (c1 INT, c2 INT, c3 INT)
PARTITION BY RANGE (c1)
(PART P1 STARTING FROM (1) ENDING AT (10) IN parttbs,
PART P2 STARTING FROM (11) ENDING AT (20) IN parttbs,
PART P3 STARTING FROM (21) ENDING AT (30) IN parttbs)
DISTRIBUTE BY (c2)Statement executed:
REORG TABLE sales ALLOW NO ACCESS ON ALL DBPARTITIONNUMS-
Example 1:
-
The output is modified to include table information for the relevant table only.GET SNAPSHOT FOR TABLES ON DPARTDB GLOBALTable Snapshot First database connect timestamp = 06/28/2005 13:46:43.061690 Last reset timestamp = 06/28/2005 13:46:47.440046 Snapshot timestamp = 06/28/2005 13:46:50.964033 Database name = DPARTDB Database path = /work/sales/NODE0000/SQL00001/ Input database alias = DPARTDB Number of accessed tables = 5 Table List Table Schema = NEWTON Table Name = SALES Table Type = User Data Partition Id = 0 Data Object Pages = 3 Rows Read = 12 Rows Written = 1 Overflows = 0 Page Reorgs = 0 Table Reorg Information: Node number = 0 Reorg Type = Reclaiming Table Reorg Allow No Access Recluster Via Table Scan Reorg Data Only Reorg Index = 0 Reorg Tablespace = 3 Long Temp space ID = 3 Start Time = 06/28/2005 13:46:49.816883 Reorg Phase = 3 - Index Recreate Max Phase = 3 Phase Start Time = 06/28/2005 13:46:50.362918 Status = Completed Current Counter = 0 Max Counter = 0 Completion = 0 End Time = 06/28/2005 13:46:50.821244 Table Reorg Information: Node number = 1 Reorg Type = Reclaiming Table Reorg Allow No Access Recluster Via Table Scan Reorg Data Only Reorg Index = 0 Reorg Tablespace = 3 Long Temp space ID = 3 Start Time = 06/28/2005 13:46:49.822701 Reorg Phase = 3 - Index Recreate Max Phase = 3 Phase Start Time = 06/28/2005 13:46:50.420741 Status = Completed Current Counter = 0 Max Counter = 0 Completion = 0 End Time = 06/28/2005 13:46:50.899543 Table Reorg Information: Node number = 2 Reorg Type = Reclaiming Table Reorg Allow No Access Recluster Via Table Scan Reorg Data Only Reorg Index = 0 Reorg Tablespace = 3 Long Temp space ID = 3 Start Time = 06/28/2005 13:46:49.814813 Reorg Phase = 3 - Index Recreate Max Phase = 3 Phase Start Time = 06/28/2005 13:46:50.344277 Status = Completed Current Counter = 0 Max Counter = 0 Completion = 0 End Time = 06/28/2005 13:46:50.803619 Table Schema = NEWTON Table Name = SALES Table Type = User Data Partition Id = 1 Data Object Pages = 3 Rows Read = 8 Rows Written = 1 Overflows = 0 Page Reorgs = 0 Table Reorg Information: Node number = 0 Reorg Type = Reclaiming Table Reorg Allow No Access Recluster Via Table Scan Reorg Data Only Reorg Index = 0 Reorg Tablespace = 3 Long Temp space ID = 3 Start Time = 06/28/2005 13:46:50.014617 Reorg Phase = 3 - Index Recreate Max Phase = 3 Phase Start Time = 06/28/2005 13:46:50.362918 Status = Completed Current Counter = 0 Max Counter = 0 Completion = 0 End Time = 06/28/2005 13:46:50.821244 Table Reorg Information: Node number = 1 Reorg Type = Reclaiming Table Reorg Allow No Access Recluster Via Table Scan Reorg Data Only Reorg Index = 0 Reorg Tablespace = 3 Long Temp space ID = 3 Start Time = 06/28/2005 13:46:50.026278 Reorg Phase = 3 - Index Recreate Max Phase = 3 Phase Start Time = 06/28/2005 13:46:50.420741 Status = Completed Current Counter = 0 Max Counter = 0 Completion = 0 End Time = 06/28/2005 13:46:50.899543 Table Reorg Information: Node number = 2 Reorg Type = Reclaiming Table Reorg Allow No Access Recluster Via Table Scan Reorg Data Only Reorg Index = 0 Reorg Tablespace = 3 Long Temp space ID = 3 Start Time = 06/28/2005 13:46:50.006392 Reorg Phase = 3 - Index Recreate Max Phase = 3 Phase Start Time = 06/28/2005 13:46:50.344277 Status = Completed Current Counter = 0 Max Counter = 0 Completion = 0 End Time = 06/28/2005 13:46:50.803619 Table Schema = NEWTON Table Name = SALES Table Type = User Data Partition Id = 2 Data Object Pages = 3 Rows Read = 4 Rows Written = 1 Overflows = 0 Page Reorgs = 0 Table Reorg Information: Node number = 0 Reorg Type = Reclaiming Table Reorg Allow No Access Recluster Via Table Scan Reorg Data Only Reorg Index = 0 Reorg Tablespace = 3 Long Temp space ID = 3 Start Time = 06/28/2005 13:46:50.199971 Reorg Phase = 3 - Index Recreate Max Phase = 3 Phase Start Time = 06/28/2005 13:46:50.362918 Status = Completed Current Counter = 0 Max Counter = 0 Completion = 0 End Time = 06/28/2005 13:46:50.821244 Table Reorg Information: Node number = 1 Reorg Type = Reclaiming Table Reorg Allow No Access Recluster Via Table Scan Reorg Data Only Reorg Index = 0 Reorg Tablespace = 3 Long Temp space ID = 3 Start Time = 06/28/2005 13:46:50.223742 Reorg Phase = 3 - Index Recreate Max Phase = 3 Phase Start Time = 06/28/2005 13:46:50.420741 Status = Completed Current Counter = 0 Max Counter = 0 Completion = 0 End Time = 06/28/2005 13:46:50.899543 Table Reorg Information: Node number = 2 Reorg Type = Reclaiming Table Reorg Allow No Access Recluster Via Table Scan Reorg Data Only Reorg Index = 0 Reorg Tablespace = 3 Long Temp space ID = 3 Start Time = 06/28/2005 13:46:50.179922 Reorg Phase = 3 - Index Recreate Max Phase = 3 Phase Start Time = 06/28/2005 13:46:50.344277 Status = Completed Current Counter = 0 Max Counter = 0 Completion = 0 End Time = 06/28/2005 13:46:50.803619 - Example 2:
The output is modified to include table information for the relevant table only.GET SNAPSHOT FOR TABLES ON DPARTDB AT DBPARTITIONNUM 2Table Snapshot First database connect timestamp = 06/28/2005 13:46:43.617833 Last reset timestamp = Snapshot timestamp = 06/28/2005 13:46:51.016787 Database name = DPARTDB Database path = /work/sales/NODE0000/SQL00001/ Input database alias = DPARTDB Number of accessed tables = 3 Table List Table Schema = NEWTON Table Name = SALES Table Type = User Data Partition Id = 0 Data Object Pages = 1 Rows Read = 0 Rows Written = 0 Overflows = 0 Page Reorgs = 0 Table Reorg Information: Node number = 2 Reorg Type = Reclaiming Table Reorg Allow No Access Recluster Via Table Scan Reorg Data Only Reorg Index = 0 Reorg Tablespace = 3 Long Temp space ID = 3 Start Time = 06/28/2005 13:46:49.814813 Reorg Phase = 3 - Index Recreate Max Phase = 3 Phase Start Time = 06/28/2005 13:46:50.344277 Status = Completed Current Counter = 0 Max Counter = 0 Completion = 0 End Time = 06/28/2005 13:46:50.803619 Table Schema = NEWTON Table Name = SALES Table Type = User Data Partition Id = 1 Data Object Pages = 1 Rows Read = 0 Rows Written = 0 Overflows = 0 Page Reorgs = 0 Table Reorg Information: Node number = 2 Reorg Type = Reclaiming Table Reorg Allow No Access Recluster Via Table Scan Reorg Data Only Reorg Index = 0 Reorg Tablespace = 3 Long Temp space ID = 3 Start Time = 06/28/2005 13:46:50.006392 Reorg Phase = 3 - Index Recreate Max Phase = 3 Phase Start Time = 06/28/2005 13:46:50.344277 Status = Completed Current Counter = 0 Max Counter = 0 Completion = 0 End Time = 06/28/2005 13:46:50.803619 Table Schema = NEWTON Table Name = SALES Table Type = User Data Partition Id = 2 Data Object Pages = 1 Rows Read = 4 Rows Written = 1 Overflows = 0 Page Reorgs = 0 Table Reorg Information: Node number = 2 Reorg Type = Reclaiming Table Reorg Allow No Access Recluster Via Table Scan Reorg Data Only Reorg Index = 0 Reorg Tablespace = 3 Long Temp space ID = 3 Start Time = 06/28/2005 13:46:50.179922 Reorg Phase = 3 - Index Recreate Max Phase = 3 Phase Start Time = 06/28/2005 13:46:50.344277 Status = Completed Current Counter = 0 Max Counter = 0 Completion = 0 End Time = 06/28/2005 13:46:50.803619- Example 3:
The output is modified to include a subset of table information for the relevant table only.SELECT * FROM SYSIBMADM.SNAPLOCK WHERE tabname = 'SALES';... TBSP_NAME TABNAME LOCK_OBJECT_TYPE LOCK_MODE LOCK_STATUS ... --------- ------- ------------------ ---------- ----------- ... PARTTBS SALES ROW_LOCK X GRNT ... ... - SALES TABLE_LOCK IX GRNT ... ... PARTTBS SALES TABLE_PART_LOCK IX GRNT ... ... PARTTBS SALES ROW_LOCK X GRNT ... ... - SALES TABLE_LOCK IX GRNT ... ... PARTTBS SALES TABLE_PART_LOCK IX GRNT ... ... PARTTBS SALES ROW_LOCK X GRNT ... ... - SALES TABLE_LOCK IX GRNT ... ... PARTTBS SALES TABLE_PART_LOCK IX GRNT ... 9 record(s) selected.Output from this query (continued).... LOCK_ESCALATION LOCK_ATTRIBUTES DATA_PARTITION_ID DBPARTITIONNUM --------------- --------------- ----------------- -------------- ... 0 INSERT 2 2 ... 0 NONE - 2 ... 0 NONE 2 2 ... 0 INSERT 0 0 ... 0 NONE - 0 ... 0 NONE 0 0 ... 0 INSERT 1 1 ... 0 NONE - 1 ... 0 NONE 1 1- Example 4:
The output is modified to include a subset of table information for the relevant table only.SELECT * FROM SYSIBMADM.SNAPTAB WHERE tabname = 'SALES';... TABSCHEMA TABNAME TAB_FILE_ID TAB_TYPE DATA_OBJECT_PAGES ROWS_WRITTEN ... ... --------- ------- ----------- ---------- ----------------- ------------ ... ... NEWTON SALES 2 USER_TABLE 1 1 ... ... NEWTON SALES 4 USER_TABLE 1 1 ... ... NEWTON SALES 3 USER_TABLE 1 1 ... 3 record(s) selected.Output from this query (continued).... OVERFLOW_ACCESSES PAGE_REORGS DBPARTITIONNUM TBSP_ID DATA_PARTITION_ID ... ----------------- ----------- -------------- ------- ----------------- ... 0 0 0 3 0 ... 0 0 2 3 2 ... 0 0 1 3 1- Example 5:
-
The output is modified to include a subset of table information for the relevant table only.SELECT * FROM SYSIBMADM.SNAPTAB_REORG WHERE tabname = 'SALES';;REORG_PHASE REORG_MAX_PHASE REORG_TYPE ... ------------- --------------- -------------------------------------------- INDEX_RECREATE 3 RECLAIM+OFFLINE+ALLOW_NONE+TABLESCAN+DATAONLY ... INDEX_RECREATE 3 RECLAIM+OFFLINE+ALLOW_NONE+TABLESCAN+DATAONLY ... INDEX_RECREATE 3 RECLAIM+OFFLINE+ALLOW_NONE+TABLESCAN+DATAONLY ... INDEX_RECREATE 3 RECLAIM+OFFLINE+ALLOW_NONE+TABLESCAN+DATAONLY ... INDEX_RECREATE 3 RECLAIM+OFFLINE+ALLOW_NONE+TABLESCAN+DATAONLY ... INDEX_RECREATE 3 RECLAIM+OFFLINE+ALLOW_NONE+TABLESCAN+DATAONLY ... INDEX_RECREATE 3 RECLAIM+OFFLINE+ALLOW_NONE+TABLESCAN+DATAONLY ... INDEX_RECREATE 3 RECLAIM+OFFLINE+ALLOW_NONE+TABLESCAN+DATAONLY ... INDEX_RECREATE 3 RECLAIM+OFFLINE+ALLOW_NONE+TABLESCAN+DATAONLY ... 9 record(s) selected.Output from this query (continued).... REORG_STATUS REORG_TBSPC_ID DBPARTITIONNUM DATA_PARTITION_ID -------------------------- -------------- ----------------- ... COMPLETED 3 2 0 ... COMPLETED 3 2 1 ... COMPLETED 3 2 2 ... COMPLETED 3 1 0 ... COMPLETED 3 1 1 ... COMPLETED 3 1 2 ... COMPLETED 3 0 0 ... COMPLETED 3 0 1 ... COMPLETED 3 0 2 - Example 6:
- The
Table Reorg Information includes information about reclaiming extents
as part of a reorganization operation. The example that follows shows
the relevant output.
db2 -v "get snapshot for tables on wsdb" Table Reorg Information: Reorg Type = Reclaim Extents Allow Write Access Reorg Index = 0 Reorg Tablespace = 0 Start Time = 10/22/2008 15:49:35.477532 Reorg Phase = 12 - Release Max Phase = 3Note: Any snapshot requests from a monitor version before SQLM_DBMON_VERSION9_7 will not return any Reclaim Reorg status to the requesting client.