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 all table maintenance parameters (auto_runstats, auto_stats_prof, auto_prof_upd, 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_stats_prof

When enabled, this automated table maintenance parameter turns on statistical profile generation, designed to improve applications whose workloads include complex queries, many predicates, joins, and grouping operations over several tables. 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. This parameter cannot be enabled if the section_actuals database configuration parameter is enabled (SQLCODE -5153).

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
             Automatic statement statistics  (AUTO_STMT_STATS) = ON
          Automatic statistics profiling     (AUTO_STATS_PROF) = OFF
            Automatic profile updates          (AUTO_PROF_UPD) = 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_prof_upd

When enabled, this automated table maintenance parameter (a child of auto_stats_prof) specifies that the runstats profile is to be updated with recommendations. When this parameter is disabled, recommendations are stored in the opt_feedback_ranking table, which you can inspect when manually updating the runstats profile. 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_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.