DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
After many changes to table data, the table and its indexes can become fragmented. Logically sequential data might reside on nonsequential pages, forcing the database manager to perform additional read operations 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 kind 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 have had their statistics updated to see if 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.
In a partitioned database environment, the initiation of automatic reorganization is done on the catalog database partition, and these configuration parameters need only be enabled on that partition. The reorg operation, however, runs on all of the database partitions on which the target tables reside.
If you are unsure about when and how to reorganize your tables and indexes, you can incorporate automatic reorganization as part of your overall database maintenance plan.
You can also reorganize multidimensional clustering (MDC) tables to reclaim space. The freeing of extents from an MDC table is only supported for MDC tables in DMS table spaces. Freeing extents from your MDC tables can be part of the automatic maintenance activities for your database.
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.
Starting with Version 9.7 Fix Pack 4, you can enable automatic index reorganization in volatile tables. The automatic reorganization process determines whether index reorganization is required in volatile tables and schedules the necessary operation. Index reorganization will be performed periodically on volatile tables and will release space that can be reused by the indexes defined on these tables.
Statistics cannot be collected in volatile tables because they are updated very frequently. To determine what indexes need to be reorganized, automatic reorganization uses the numInxPseudoEmptyPagesForVolatile attribute instead of statistics. This attribute in the AUTO_REORG policy was introduced in Version 9.7 Fix Pack 4 and indicates how many empty index pages with pseudo deleted keys an index should have to trigger index reorganization.
To enable automatic index reorganization in volatile tables, the DB2_WORKLOAD registry variable must be set to SAP, automatic reorganization must be enabled, and the numInxPseudoEmptyPagesForVolatile attribute must be set.