Multidimensional clustering extent management

Freeing data extents from within the multidimensional clustering (MDC) table is done through the reorganization of the MDC table.

Within an MDC table, a block map keeps track of all the data extents belonging to a table and indicates which blocks and extents have data on them and which do not. Blocks with data are marked as being "in use". Whenever deletions or rollouts happen, block entries with the block map are no longer marked "in use" but rather are freed for reuse by the MDC table.

However, these blocks and extents cannot be used by other objects within the table space. You can release these free data extents from the MDC table through the reorganization of the MDC table. The freeing of extents from the MDC table is only supported for MDC tables in DMS table spaces.

The REORG TABLE command uses the RECLAIM EXTENTS ONLY option to free extents from exclusive use by the MDC table and makes the space available for use by other database objects within the table space.

The option also allows for your control of concurrent access to the MDC table while the extents are being freed. Write access is the default, read access and no access are also choices to control concurrent access.

If the MDC table is also range or database partitioned, by default the freeing of extents occurs on all data or database partitions. You have the option to run the command to free extents only on a specific partition by specifying a partition name (for data partitions) or a partition number (for database partitions).

Not only can the REORG TABLE command be used to free extents, but the db2Reorg API also allows the reclaim extents option as well.

Automatic support is available to make the freeing of extents part of your automatic maintenance activities for the database. To enable a reorganization to free extents in an MDC table, the AUTO_MAINT, AUTO_TBL_MAINT, and AUTO_REORG database configuration parameters must all have a value of "ON". The configuring of these database configuration parameters can be carried out through the use of the Configure Automatic Maintenance wizard or the command line. On a DB2 instance where the database partitioning feature is enabled, the configuring of the parameters must be issued on the catalog partition.

A maintenance policy controls when an automatic reorganization of an MDC table takes place to free unused extents. The DB2 system stored procedures AUTOMAINT_SET_POLICY and AUTOMAINT_SET_POLICYFILE are used to set this maintenance policy. XML is used to store the automated maintenance policy.