auto_maint - Automatic maintenance configuration parameter

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.