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, auto_sampling, auto_cg_stats, auto_ai_maint, auto_ai_optimizer, and auto_model_discover).

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

Assuming that auto_maint and auto_runstats is 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) = ON
       Automatic column group statistics (AUTO_CG_STATS) = OFF
     Automatic reorganization               (AUTO_REORG) = OFF
   Automatic AI maintenance              (AUTO_AI_MAINT) = ON
     AI Optimizer                    (AUTO_AI_OPTIMIZER) = ON
       Automatic Model Discovery   (AUTO_MODEL_DISCOVER) = 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.

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_cg_stats

This parameter controls automatic collection of column group statistics on base tables. Statistical views are excluded. To enable the automatic collection of column group statistics, set auto_maint, auto_tbl_maint, auto_runstats and auto_cg_stats to ON. By default, this parameter is set to OFF.

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.

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.

auto_ai_maint

This parameter controls automatic AI maintenance capabilities within Db2 and is the parent of all the other automatic AI maintenance database configuration parameters (auto_ai_optimizer and auto_model_discovery).

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. Automatic AI maintenance can be enabled or disabled globally by enabling/disabling this parameter.

By default, this parameter is set to ON.

You can enable or disable individual automatic AI maintenance features independently by setting the following parameters:

auto_ai_optimizer

This automated AI maintenance parameter enables or disables automatic AI Query Optimizer operations for a database and is the parent of all the other automatic AI Query Optimizer database configuration parameters (auto_model_discovery). An AI Optimizer policy (a defined set of rules or guidelines) can be used to specify the automated behavior. The AI Query Optimizer discovers and trains models used by the optimizer to determine the most efficient query execution plan.

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. AI Query Optimizer operations can be enabled or disabled globally by enabling/disabling this parameter.

To be enabled, this parameter must be set to ON, and its parent parameters must also be enabled. The models are managed as part of the automatic statistics collection operation, so auto_tbl_maint, auto_ai_maint and auto_runstats must be set to ON for auto_ai_optimizer to be effective. A warning (SQLSTATE 01694) will be issued if either auto_tbl_maint, auto_ai_maint or auto_runstats is OFF.

By default, this parameter is set to ON for new databases and OFF for existing databases.

auto_model_discovery

This automated AI Query Optimizer parameter enables or disables the automatic discovery of models managed and used by the AI Query Optimizer. To be enabled, this parameter must be set to ON, and its parent parameters must also be enabled.

By default, the parameter is set to ON.