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.
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 OFF.