Automatic table and index maintenance
After many changes to table data, a table and its indexes can become fragmented. Logically sequential data might be found on nonsequential pages, forcing additional read operations by the database manager to access data.
The statistical information that is collected by the RUNSTATS utility shows the distribution of data within a table. Analysis of these statistics can indicate when and what type of reorganization is necessary.
The automatic reorganization process determines the need for table or index reorganization by using formulas that are part of the REORGCHK utility. It periodically evaluates tables and indexes that had their statistics updated to see whether reorganization is required, and schedules such operations whenever they are necessary.
The automatic reorganization feature can be enabled or disabled through the auto_reorg, auto_tbl_maint, and auto_maint database configuration parameters.
Automatic reorganization utilizes the REORG utility to perform the recommended reorg as per REORGCHK. Tables are reorganized using REORG TABLE with the CLASSIC option, while indexes use REORG INDEX/INDEXES with the REBUILD option.
In a partitioned database environment, the initiation of automatic reorganization is done on the catalog database partition. These configuration parameters are enabled only on the catalog database partition. The REORG operation, however, runs on all of the database partitions on which the target tables are found.
Reorganization of indexes can happen in an online fashion using REORG INDEX/INDEXES with the ALLOW WRITE ACCESS option.
You can reorganize multidimensional clustering (MDC), insert time clustering (ITC) tables, and column-organized tables to reclaim space. The freeing of extents from MDC and ITC tables is only supported for tables in DMS table spaces and automatic storage. Freeing extents from your MDC, ITC tables, and column-organized tables can be done in an online fashion with the RECLAIM EXTENTS option of the REORG TABLE command.
You can also schedule an alternate means to reclaim space from your indexes. The REORG INDEX command has an index clause in which you can specify space-reclaim-options. When you specify RECLAIM EXTENTS in space-reclaim-options, space is released back to the table space in an online fashion. This operation provides space reclamation without the need for a full rebuild of the indexes. The REBUILD option of the REORG INDEX command also reclaims space, but not necessarily in an online fashion.
Automatic reorganization on data partitioned tables
For Db2® Version 9.7 Fix Pack 1 and earlier releases, automatic reorganization supports reorganization of a data partitioned table for the entire table. For Db2 V9.7 Fix Pack 1 and later releases, automatic reorganization supports reorganizing data partitions of a partitioned table and reorganizing the partitioned indexes on a data partition of a partitioned table.
To avoid placing an entire data partitioned table into ALLOW NO ACCESS mode, automatic reorganization performs REORG INDEXES ALL operations at the data partition level on partitioned indexes that need to be reorganized. Automatic reorganization performs REORG INDEX operations on any nonpartitioned index that needs to be reorganized.
- If any nonpartitioned indexes (except system-generated XML path indexes) are defined on the table and there is only one partition that needs to be reorganized, automatic reorganization performs a REORG TABLE operation with the ON DATA PARTITION clause to specify the partition that needs to be reorganized. Otherwise, automatic reorganization performs a REORG TABLE on the entire table without the ON DATA PARTITION clause.
- If no nonpartitioned indexes (except system-generated XML path indexes) are defined on the table, automatic reorganization performs a REORG TABLE operation with the ON DATA PARTITION clause on each partition that needs to be reorganized.
Automatic reorganization on volatile tables
You can enable automatic index reorganization for volatile tables. The automatic reorganization process determines whether index reorganization is required for volatile tables and schedules a REORG INDEX CLEANUP. Index reorganization is performed periodically on volatile tables and releases space that can be reused by the indexes defined on these tables.
Statistics cannot be collected in volatile tables because they are updated frequently. To determine what indexes need to be reorganized, automatic reorganization uses the numInxPseudoEmptyPagesForVolatile attribute instead of REORGCHK. The number of pseudo empty pages is maintained internally, visible through mon_get_index, and does not require a RUNSTATS operation like REORGCHK. This attribute in the AUTO_REORG policy indicates how many empty index pages with pseudo deleted keys an index must have so index reorganization is triggered.
- The DB2_WORKLOAD registry variable must be set to SAP.
- Automatic reorganization must be enabled.
- The numInxPseudoEmptyPagesForVolatile attribute must be set.