The database manager provides automatic maintenance capabilities
for performing database backups, keeping statistics current, and reorganizing
tables and indexes as necessary. Performing maintenance activities
on your databases is essential in ensuring that they are optimized
for performance and recoverability.
Maintenance of your database includes some or all of the following
activities:
- Backups. When you back up a database, the database manager
takes a copy of the data in the database and stores it on a different
medium in case of failure or damage to the original. Automatic database
backups help to ensure that your database is backed up properly and
regularly so that you don't have to worry about when to back up or
know the syntax of the BACKUP command.
- Data defragmentation (table or index reorganization). This
maintenance activity can increase the efficiency with which the database
manager accesses your tables. Automatic reorganization manages an
offline table and index reorganization so that you don't need to worry
about when and how to reorganize your data.
- Data access optimization (statistics collection). The database
manager updates the system catalog statistics on the data in a table,
the data in indexes, or the data in both a table and its indexes.
The optimizer uses these statistics to determine which path to use
to access the data. Automatic statistics collection attempts to improve
the performance of the database by maintaining up-to-date table statistics.
The goal is to allow the optimizer to choose an access plan based
on accurate statistics.
- Statistics profiling. Automatic statistics profiling advises
when and how to collect table statistics by detecting outdated, missing,
or incorrect statistics, and by generating statistical profiles based
on query feedback.
It can be time-consuming to determine
whether and when to run maintenance activities, but automatic maintenance
removes the burden from you. You can manage the enablement of the
automatic maintenance features simply and flexibly by using the automatic
maintenance database configuration parameters. By setting the automatic
maintenance database configuration parameters, you can specify your
maintenance objectives The database manager uses these objectives
to determine whether the maintenance activities need to be done and
runs only the required ones during the next available maintenance
window (a time period that you define).
In IBM® Data Studio Version
3.1 or later, you can use the task assistant for configuring
automatic maintenance. Task assistants can guide you through the
process of setting options, reviewing the automatically generated
commands to perform the task, and running these commands. For more
details, see Administering databases with task assistants.
DB2 Version 10.1 for Linux, UNIX, and Windows