auto_maint - Automatic maintenance configuration parameter
This parameter is the parent of all the other automatic maintenance database configuration parameters (auto_db_backup, auto_tbl_maint, auto_runstats, auto_stmt_stats, auto_stats_views, auto_reorg, and auto_sampling).
- Configuration type
- Database
- Applies to
-
- Database server with local and remote clients
- Client
- Database server with local clients
- Partitioned database server with local and remote clients
- Parameter type
- Configurable Online
- Propagation class
- Immediate
- Default [range]
- ON [ON; OFF ]
When this parameter is disabled, all of its child parameters are also disabled, but their settings, as recorded in the database configuration file, do not change. When this parent parameter is enabled, recorded values for its child parameters take effect. In this way, automatic maintenance can be enabled or disabled globally.
By default, this parameter is set to ON.
Automatic table maintenance requires that the instance owner user-id has been granted Database administration authority (DBADM).
You can enable or disable individual automatic maintenance features independently by setting the following parameters:
- auto_db_backup
- This automated maintenance parameter enables or disables automatic backup operations for a database. A backup policy (a defined set of rules or guidelines) can be used to specify the automated behavior. The objective of the backup policy is to ensure that the database is being backed up regularly. The backup policy for a database is created automatically when the Db2® Health Monitor first runs. By default, this parameter is set to OFF. To be enabled, this parameter must be set to ON, and its parent parameter must also be enabled.
- auto_tbl_maint
- This
parameter is the parent of table maintenance parameters (auto_runstats and auto_reorg).
When this parameter is disabled, all of its child parameters are also
disabled, but their settings, as recorded in the database configuration
file, do not change. When this parent parameter is enabled, recorded
values for its child parameters take effect. In this way, table maintenance
can be enabled or disabled globally.
By default, this parameter is set to ON.
- auto_runstats
- This automated table maintenance parameter enables or disables
automatic table RUNSTATS operations for a database.
A RUNSTATS policy (a defined set of rules or guidelines)
can be used to specify the automated behavior. Statistics collected
by the RUNSTATS utility are used by the optimizer
to determine the most efficient plan for accessing the physical data.
To be enabled, this parameter must be set to ON,
and its parent parameters must also be enabled.
By default, this parameter is set to ON.
- auto_stmt_stats
This parameter enables and disables the collection of real-time statistics. It is a child of the auto_runstats configuration parameter. This feature is enabled only if the parent, auto_runstats configuration parameter, is also enabled. For example, to enable auto_stmt_stats, set auto_maint, auto_tbl_maint, and auto_runstats to ON. To preserve the child value, the auto_runstats configuration parameter can be ON while the auto_maint configuration parameter is OFF. The corresponding Auto Runstats feature will still be OFF.
Assuming that both Auto Runstats and Auto Reorg are enabled, the settings are as follows:Automatic maintenance (AUTO_MAINT) = ON Automatic database backup (AUTO_DB_BACKUP) = OFF Automatic table maintenance (AUTO_TBL_MAINT) = ON Automatic runstats (AUTO_RUNSTATS) = ON Real-time statistics (AUTO_STMT_STATS) = ON Statistical views (AUTO_STATS_VIEWS) = OFF Automatic sampling (AUTO_SAMPLING) = OFF Automatic reorganization (AUTO_REORG) = ON
You can disable both Auto Runstats and Auto Reorg features temporarily by setting auto_tbl_maint to OFF. Both features can be enabled later by setting auto_tbl_maint back to ON. You do not need to issue db2stop or db2start commands to have the changes take effect.
By default, this parameter is set to ON.
- auto_stats_views
- This parameter enables and disables automatic statistic collection
on statistical views. The statistics on statistical views are automatically
maintained when this parameter is set to ON.
By default, this parameter is set to OFF.
- auto_reorg
- This automated table maintenance parameter enables or disables
automatic table and index reorganization for a database. A reorganization
policy (a defined set of rules or guidelines) can be used to specify
the automated behavior. To be enabled, this parameter must be set
to ON, and its parent parameters must also be enabled.
By default, this parameter is set to OFF.
- auto_sampling
- This parameter controls whether automatic statistics collection uses sampling when collecting
statistics for a large table. To enable the automatic sampling, set auto_maint,
auto_tbl_maint, auto_runstats and
auto_sampling to ON.
If the automatic statistics
collection is enabled, page-level sampling is used and the sampling rate is determined
automatically. Both data and index pages are sampled for base tables as opposed to statistical
views, which do not have indexes.
By default, this parameter is set to ON.