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.
To enable your database for automatic reorganization:
- Set the auto_maint, auto_tbl_maint, and auto_reorg database
configuration parameters to ON. You can set the parameters to ON with
- 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
Replace <db_name> with the name of the database on which you want to enable automatic maintenance and reorganization.
- Connect to the database, <db_name>.
- 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:
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.
- Call the AUTOMAINT_SET_POLICY procedure.
- Call the AUTOMAINT_SET_POLICYFILE procedure.