Configure automatic maintenance in DB2 for Linux, UNIX, and Windows with SQL interface

Common maintenance tasks performed by database administrators include
reorganization, statistics collection, and backup. DB2 database manager provides
facilities to perform these maintenance tasks automatically. Prior to the release
of DB2 9.5, automatic maintenance tasks were only configurable manually using the
Automatic Maintenance Wizard in the DB2 Control Center. It was not possible to
programmatically deploy an automatic maintenance configuration to a database, and
it was non-trivial to retrieve an existing configuration and replicate it across
multiple databases (that is, each database needed to be configured manually using
the DB2 Control Center).

In DB2 9.5, the configuration for each automatic maintenance task can be
expressed as a very simple XML document, referred to as an automatic maintenance
policy. The automatic maintenance policies contain the same configuration
information that can be set using the Control Center. SQL stored procedures are
provided to retrieve and update these policies. Using the stored procedures, you
can perform the following tasks:

  • Easily retrieve and view automatic maintenance configuration information from
    the command line.
  • Deploy automatic maintenance polices to any database using a script or
    programming language. Using a script, you can replicate the automatic
    maintenance configuration across multiple databases, a task that would have
    previously required manual updates for each database using the Control

This article provides in-depth information about the new stored procedures and
the automatic maintenance policies. Following the instructions outlined in this
article, you will be able to configure automatic backup, automatic reorganization,
and automatic statistics collection effectively and easily.