Enabling automatic table and index reorganization

Use automatic table and index reorganization to eliminate the worry of when and how to reorganize your data.

About this task

Having well-organized table and index data is critical to efficient data access and optimal workload performance. After many database operations, such as insert, update, and delete, logically sequential table data might be found on nonsequential data pages. When logically sequential table data is found on nonsequential data pages, additional read operations are required by the database manager to access data. Additional read operations are also required when accessing data in a table from which a significant number of rows are deleted. You can enable the database manager to reorganize system both catalog tables and user tables.

Procedure

To enable your database for automatic reorganization:

  1. Set the auto_maint, auto_tbl_maint, and auto_reorg database configuration parameters to ON. You can set the parameters to ON with these commands:
    • db2 update db cfg for <db_name> using auto_maint on
    • db2 update db cfg for <db_name> using auto_tbl_maint on
    • db2 update db cfg for <db_name> using auto_reorg on

      Replace <db_name> with the name of the database on which you want to enable automatic maintenance and reorganization.

  2. Connect to the database, <db_name>.
  3. Specify a reorganization policy. A reorganization policy is a defined set of rules or guidelines that dictate when automated table and index maintenance takes place. You can set this policy in one of two ways:
    1. Call the AUTOMAINT_SET_POLICY procedure.
    2. Call the AUTOMAINT_SET_POLICYFILE procedure.
    The reorganization policy is either an input argument or file both of which are in an XML format. For more information about both of these procedures, see the Related reference.

Example

For an example of this capability, see the Related concepts.