Configure automatic maintenance in DB2 for Linux, UNIX, and Windows using IBM Data Studio

A step-by-step guide for database admins

The automatic maintenance capabilities of IBM® DB2® for Linux®, UNIX®, and Windows® relieves DBAs so they can focus on other vital tasks, such as maximizing performance. This article demonstrates how to configure automatic maintenance capabilities in DB2 for performing database backups, keeping statistics current, and reorganizing tables and indices as necessary, using IBM Data Studio.

Share:

Arvind Gupta (arvindgu@in.ibm.com), Senior Staff Software Engineer, IBM

Arvind GuptaArvind Gupta works on the IBM Data Server Client Drivers team with the IBM Software Lab, India. He has been with IBM for more than three years, with overall experience of more than 11 years. He has worked on the Data Studio product since 2008. He has extensive experience in Java J2EE application development.



29 March 2012

Also available in Chinese

Introduction

Database administrators perform common maintenance tasks that include backups, reorganization of data in tables and indices, and statistics collection. The DB2 database manager provides automatic maintenance capabilities for performing backups, keeping statistics current, and reorganizing tables and indices as necessary. Performing these tasks regularly on your databases is of extreme importance to ensure that they are optimized for performance and recoverability.

It can be time-consuming and difficult to determine whether and when to run maintenance activities. The autonomic capabilities of DB2 remove the burden from you. You can manage automatic maintenance features easily and flexibly by using the IBM Data Studio Configure Automatic Maintenance wizard without worrying about DB2 command syntax. In the wizard, you specify your maintenance objectives, and the DB2 database manager uses these objectives to determine whether the maintenance activities need to be performed, then runs only the required activities during the next available maintenance window (a time period you define).

This articles explains how each of these tasks can be configured in IBM Data Studio. After reading this article, you will be able to:

  • Understand how to use the IBM Data Studio Configure Automatic Maintenance Wizard
  • Configure maintenance windows
  • Configure automatic backup
  • Configure automatic reorganization
  • Configure automatic statistics collection

Configuring automatic maintenance tasks using the IBM Data Studio

  1. Launch IBM Data Studio.
  2. Open the Configure Automatic Maintenance Wizard.
    • In Administration Explorer, expand the navigation tree on the left and select the databases for which you want to configure automatic maintenance. Connect to it if it is not already connected by right-clicking and selecting Connect.
    • After the database is connected, right-click on it and select the Set Up and Configure > Configure Automatic Maintenance menu option in the pop-up menu, as shown in Figure 1.
      Figure 1. Launching Configure Automatic Maintenance Wizard
      Image shows to select database name, set up, and configure automatic maintenance
  3. The first tab in the wizard, Options, lists database configuration parameters, along with a checkbox. The configuration parameters are shown in hierarchy by the indentation shown in Figure 2. These database configuration parameters allow a DBA to enable or disable automatic table maintenance features selectively or as a group. For example, if AUTO_MAINT is set to OFF, all other automatic maintenance parameters are considered to have an effective value of OFF, regardless of their actual value. Similarly, if AUTO_TBL_MAINT is set to OFF, the AUTO_REORG, AUTOSTATS_PROF, AUTO_PROF_UPD, AUTO_RUNSTATS and AUTO_STMT_STATS parameters are considered to have an effective value of OFF. If AUTO_TBL_MAINT is set to ON, the AUTO_REORG, AUTOSTATS_PROF, AUTO_PROF_UPD, AUTO_RUNSTATS and AUTO_STMT_STATS parameters will have an effective value of their actual value. Select a parameter's checkbox to set value of ON or uncheck to set value of OFF.
    Figure 2. Options tab in Configure Automatic Maintenance Wizard
    Image shows automatic maintenance options AUTO_MAINT, AUTO_TBL_MAINT, AUTO_RUNSTATS, AND AUTO_STMT_STATS are checked
  4. After changing the automatic maintenance options values as per your requirements, click on Preview Command or expand the Command section to view generated commands.
    Figure 3. Preview command
    Image shows connect to sample, update database configuration, and connect reset commands
  5. Click Run to execute the generated commands on DB2 server. The changes are applied to the database.

You have successfully enabled configuration parameters.


Maintenance windows

Maintenance windows are periods of time you define to allow the DB2 database manager to perform automatic maintenance activities. A database can have two windows specified:

  • Online maintenance window — Time period for maintenance operations that leaves the object of the maintenance accessible to users.
  • Offline maintenance window — Time period for maintenance operations during which object undergoing maintenance will not be accessible to users.

Automatic maintenance activities consume resources on your system and might affect the performance of your database when they run. Some activities also restrict access to tables, indices, and databases. Therefore, you must provide appropriate windows when the database manager can run maintenance activities.

Offline maintenance activities

Offline maintenance activities — offline database backups and table and index reorganization — can be performed only in the offline maintenance window. The level of access to database objects depends on which maintenance activity is running.

All connected applications are forced off during offline backup, and no application is allowed to connect while offline backup is being performed. While offline table or index reorganization (data defragmentation) is being performed, applications are allowed to access but not allowed to update the data in tables.

Offline maintenance activities will continue to run, even if they go beyond the window specified. The internal scheduling mechanism keeps learning on how to best estimate job completion times. The scheduler will not start the job the next time if the offline maintenance is too small for a particular database backup or reorganization activity and relies on the health monitor to provide notification of the need to increase the offline maintenance window.

Online maintenance activities

Online index reorganization can be performed only in the online maintenance window. While online maintenance activities run, all connected applications remain connected, and new connections can be established. The online database backups and automatic statistics collection and profiling are throttled by the adaptive utility-throttling mechanism to minimize the impact on the system.

Online maintenance activities will continue to run even if they go beyond the window specified.

It is recommended not to overlap online and offline windows if both are defined. Generally, the offline window will be much smaller than the online window. If the windows overlap, online tasks may be performed during the offline window (in the part that overlaps), and this takes time away from offline tasks, which are already running in a smaller window.


Configure maintenance window

To set the initial online or offline maintenance window, or change an existing online or offline maintenance window:

  1. Click on Online Maintenance Window or Offline Maintenance Window tab to specify the period of time for automatic maintenance operations.
    Figure 4. Online maintenance window tab
    Image shows online maintenance window tab
    Figure 5. Offline maintenance window tab
    Image shows offline maintenance window tab
  2. Select the Create or update the maintenance window checkbox.
  3. Select During the specified time or Not during the specified time in the Specify when the automatic maintenance can run drop-down.
  4. Specify the start time and duration in number of hours.
  5. Specify the frequency of the windows by selecting the Days of the week checkbox.
  6. After defining a maintenance window as per your requirements, click Preview Command or expand the Command section to view generated commands. Please refer to Figure 3.
  7. Click Run to execute the commands. The changes are applied to the database.

You have successfully enabled and configured the maintenance window.


Automatic backup

A database may become unusable due to various types of hardware or software failures. Automatic database backup makes database backup management tasks easier for the DBA and ensures that your database is backed up properly and regularly, and that a recent full backup of the database is performed as needed.

It is recommended that you protect your data by planning and implementing a disaster recovery strategy for your system. You may incorporate the automatic database backup feature as part of your backup and recovery strategy.

Configuring automatic backup

To configure automatic backup:

  1. The automatic backup maintenance can be enabled or disabled by using the AUTO_DB_BACKUP and AUTO_MAINT database configuration parameters. To enable the automatic backup feature, under the Options tab, select the checkbox corresponding to the AUTO_DB_BACKUP and AUTO_MAINT parameters. Refer to Figure 2.
  2. Define an online maintenance window for online backup and an offline maintenance window for offline database backup. This is explained further in the "Configure maintenance window" section.
  3. Define a backup policy. The automatic backup policy is used to control the behavior of automatic backup. To set the initial automatic backup maintenance policy or change an existing automatic backup maintenance policy, click Backup Policy and select the Create or update the backup policy checkbox. Please refer to Figure 6.
    Figure 6. Backup policy options
    Image shows policy options including backup criteria, backup location, and backup mode

    The following options can be configured in backup policy:

    1. Backup criteria specifies the criteria when automatic backup should consider scheduling a backup. Automatic backup is performed when any of the following criteria are true:
      • You have never completed a full database backup.
      • Time elapsed since the last full backup is more than a specified number of hours (for example, take backup if it has been more than one week since the last backup).
      • The transaction log space consumed since last backup (applicable only in archive logging mode) is more than the specified value (for example, take backup if more than 25 MB of log space was used).

      Backup criteria provides you options to specify how frequently backup is to be taken by automatic backup:

      • Optimize for database recoverability — Use this to take backup more often, such as once a day.
      • Balance database recoverability with performance — Use this to take backup every week.
      • Optimize for database performance — Use this to take backup less frequently, such as once a month.
      • Customize the criteria — Use this option to get more flexibility in frequency and transaction log space consumed since last backup.
    2. Backup location specifies the location where the backup will be placed. You can also change the location of your backup image. Automatic database backup supports disk, tape, Tivoli® Storage Manager (TSM), and vendor DLL media types. If backup media type is selected as disk, the automatic backup feature will regularly delete old backup images from the directory specified in the Configure Automatic Maintenance Wizard. Only the most recent backup image is guaranteed to be available at any given time. It is recommended that this directory be kept exclusively for the automatic backup feature and not be used to store other backup images.
    3. Backup mode specifies whether automatic backup should take backup in online or offline mode. If the database is enabled for roll-forward recovery (archive logging), automatic database backup can be enabled for online or offline backup. Otherwise, only offline backup is available.
  4. After defining a backup policy as per your requirements, click Preview Command or expand the Command section to view generated commands. Please refer to Figure 3.
  5. Click Run to execute the commands. The changes are applied to the database.

You have successfully enabled and configured automatic backup.


Automatic reorganization

Database administrators perform a frequent task called reorg to reorganize data in tables and indices. When many changes happens in a table, data may not be efficiently stored in sequential pages on the disk. Hence, the database manager has to perform additional read operations to access data.

Table reorganization is used to defragment a table, reclaim free space, and eliminate overflow rows in order to improve data access performance. Reorg is also used to reorder data in the order of a particular index, to optimize queries that use that index.

With table or index reorganization maintenance, you can increase the efficiency with which the database manager accesses your tables. When you use automatic reorganization, you don't need to worry about when and how to reorganize your data as it manages an offline table and index reorganization. If you are not sure when and how to reorganize your tables and indices, you can incorporate automatic reorganization as part of your overall database maintenance plan.

Configure automatic reorganization

To configure automatic reorganization:

  1. The automatic reorganization feature can be enabled or disabled by using the AUTO_REORG, AUTO_TBL_MAINT and AUTO_MAINT database configuration parameters. Enable automatic reorganization by selecting the checkbox corresponding to the AUTO_MAINT, AUTO_TBL_MAINT and AUTO_REORG parameters under the Options tab. Please refer to Figure 2.
  2. Define an offline maintenance window to specify the time during which automatic reorganization will perform offline table and index reorganization. This is explained further in the "Configure maintenance window." When you define an offline window, please note:
    • To run automatic reorganization successfully, an offline window must be specified. If automatic reorganization is enabled without the offline window, the reorganization operation will fail every time when it runs. By default, all DB2 databases have an online window defined: 24x7 (all hours of all days). There is no default offline window (as there is no acceptable default for all users).
    • A table will not be accessible, which means no read or write access is allowed while it is undergoing reorganization in the offline window. So the offline window should be defined in such a way that it will not overlap periods of usage for the tables.
    • The offline window should give sufficient time to perform the reorganization. If the window is too small (for example, once a month for one hour), it will restrict the benefits of automatic reorganization, since it will not provide adequate time to perform the reorganization.
  3. Define a reorganization policy. The reorganization policy is used to control the behavior of automatic table reorganization. To set the initial automatic reorganization maintenance policy or change an existing automatic reorganization maintenance policy, click the Reorg Policy tab and select the Create or update the reorganization policy checkbox. Please refer to Figure 7.
    Figure 7. Reorganization policy
    Image shows that reorg policy lets you choose the table scope, simple condition, size criteria, and reorg options

    The following options can be configured in reorganization policy:

    1. Table scope Identifies which tables are considered for automatic reorganization. Ways to define table scope:
      • All tables — This is default option to consider all the tables for automatic reorganization excluding system tables. Select the Include system table checkbox to include system tables for automatic reorganization collection.
      • Selected tables — This allows you to filter and select specific tables you want considered for automatic reorganization. You can choose one of the following filter options:
        • Use simple criteria filter. You can select simple criteria to filter tables based on attributes like name, schema, table type, table space, and comment. Specification is done by completing a WHERE clause applied to SELECT tables from SYSCAT.TABLES. For example, TABNAME LIKE '%EMP%'.
        • Use customize filter. You can specify a custom filter that provides more flexibility and control to filter tables by defining your own WHERE clause on a select statement applied to SELECT tables FROM SYSCAT.TABLES.
    2. Size criteria is an optional element used to exclude tables on the basis of per-partition table size, and it is applicable only to offline reorganization operations. The tables that exceed this size limit will not be considered for offline table or index reorganization. Tables will still be considered for online index reorganization, if online index reorganization is enabled for automatic reorganization. This criteria is useful for excluding large tables for which reorganization would be very time-consuming, expensive, and would require a larger offline maintenance window. Per-partition table size is estimated using the formula (SYSSTAT.TABLES.CARD * SYSSTAT.TABLES.AVGROWSIZE ) / (Number of partitions in the partition group for the table space that contains the table) by the automatic reorganization.
    3. Reorganization options allow you to modify the behavior of reorganization operations by specifying the following options:
      1. Use a system temporary table space with a compatible page size. By default, database manager stores a working copy of the table in the table spaces that contains the table, when a table is being reorganized. The reorganization operation will fail, if the table spaces does not have enough space to store the copy of table and the table space is not defined as resizable, or if underlying file systems run out of space. If the Use a system temporary table space with a compatible page size option is enabled, automatic reorganization will automatically choose a temporary table space of compatible page size during the reorganization operation for the working copy of the table.
      2. Index reorganization mode is to specify whether indices are to be reorganized in the online maintenance window or in the offline maintenance window. In online index reorganization mode, users will still be able to read and write to the index while it is being reorganized because the reorganization commands executed by automatic reorganization will include the ALLOW WRITE ACCESS option, so online index reorganization mode is recommended for indices. Online reorganization will takes more time, but it can be managed by specifying larger online maintenance window. For tables that do not support online index reorganization (MDC tables, for example), automatic reorganization will perform the index reorganization in an offline maintenance window even if the online index reorganization mode is specified.
      3. Compression data dictionary allows you to opt to rebuild compression dictionary or keep the existing compression dictionary for a table. Better compression can be achieved by rebuilding the dictionary, but will increase the time required to reorganize the table. By default, automatic reorganization keeps any existing compression dictionary. You can ignore this if you do not have row compression enabled. Refer to the DB2 documentation for more information about the row compression feature.
  4. After defining a reorganization policy as per your requirements, click Preview Command or expand the Command section to view generated commands. Please refer to Figure 3.
  5. Click Run to execute. The changes are applied to the database.

You have successfully enabled and configured automatic reorganization.


Automatic statistics collection

Catalog statistics are used by the DB2 optimizer to determine the most efficient access plan for any given query. If statistics are out of date or incomplete for a table or an index, the optimizer may select a plan that is not optimal, resulting in poorer query execution performance. Sometimes it is complex to decide which statistics to collect for a given workload. Also keeping these statistics up to date is time-consuming.

When automatic statistics collection is configured, DB2 determines whether database statistics require an update. DB2 will automatically run the RUNSTATS utility in the background to ensure that the most current database statistics are available.

If you are uncertain about how frequently to collect statistics for the tables in your database, you can include the automatic statistics collection feature as part of your overall database maintenance plan.

Configuring automatic statistics collection

To configure the automatic statistics collection:

  1. Enable automatic statistics collection, set the AUTO_MAINT and AUTO_TBL_MAINT database configuration parameters to ON. Under the Options tab, select the checkbox corresponding to the AUTO_MAINT, AUTO_TBL_MAINT parameters. Refer to Figure 2. You can configure the following options:
    • Background statistics collection — To enable background statistics collection, set AUTO_RUNSTATS database configuration parameter to ON. Under the Options tab, select the checkbox corresponding to the AUTO_RUNSTATS parameter.
    • Real-time statistics collection — To enable real-time statistics collection, set AUTO_STMT_STATS and AUTO_RUNSTATS database configuration parameters to ON. Under the Options tab, select the checkbox corresponding to the AUTO_STMT_STATS and AUTO_RUNSTATS parameters.
    • Automatic statistics profile generation — To enable automatic statistics profile generation, set AUTO_STATS_PROF and AUTO_PROF_UPD database configuration parameters to ON. Statistics are collected automatically using the generated profiles when the AUTO_RUNSTATS database configuration parameter is also set to ON.

      Under the Options tab, select the checkbox corresponding to the AUTO_STATS_PROF and AUTO_PROF_UPD parameters.

      Note: The AUTO_STATS_PROF cannot be enabled if SECTION_ACTUALS database configuration parameter is enabled.

    Refer to the DB2 documentation to get detailed information about automatic statistics collection (see Resources).

  2. Define an online maintenance window to specify the time during which automatic statistics collection will start. Automatic statistics collection will occur only during the online maintenance window, so it is not necessary to define an offline maintenance window for automatic statistics collection. By default, all DB2 databases have an online window defined: 24x7. This is generally acceptable for automatic statistics collection. The tables are accessible to users while runstats takes place, and statistic collection is always throttled by the adaptive utility throttling mechanism to control the impact on overall system performance. To modify the online window, select the Create or update the maintenance window checkbox under the Online maintenance window tab. Specify a maintenance window that meets your requirements. This is explained further in "Configure maintenance window."
  3. Define a runstats policy, which is used to control the behavior of automatic statistics collection. To set the initial automatic runstats maintenance policy or change an existing automatic runstats maintenance policy, click the Runstats Policy tab and select the Create or update the runstats policy checkbox. See Figure 8.
    Figure 8. Runstats policy options
    Image shows the runstats policy option, which lets you choose tables and simple conditions

    In the runstats policy option, specify the table scope that identifies which tables will be considered for automatic statistics collection:

    1. All tables is the default option to consider all the tables for automatic statistics collection. You can exclude system tables to prevent automatic statistics collection for them, by not selecting Include system tables check box.
    2. Selected tables allows you to filter and select tables you want to be considered for automatic statistics collection. You can choose one of the following filter options:
      • Use simple criteria filter to filter the tables based on different attributes like name, schema, table type, table space, and comment. Specification is done by completing a WHERE clause applied to SELECT tables from SYSCAT.TABLES (TABNAME LIKE '%EMP%', for example).
      • Use customize filter that provides more flexibility and control to filter tables by defining your own WHERE clause on a select statement applied to SELECT tables FROM SYSCAT.TABLES.
  4. After defining a runstats policy as per your requirements, click on Preview Command or expand the Command section to view generated commands. Refer to Figure 3.
  5. Click Run to execute the commands on the DB2 server. The changes are applied to the database.

You have successfully enabled and configured automatic statistics collection.


Conclusion

This article has demonstrated how to enable or modify automatic maintenance configuration parameters and automatic database backup, automatic reorganization, and automatic statistics collection features using IBM Data Studio without worrying about command syntax. It has also provided an overview of the DB2 automatic maintenance features to help you understand how and when to enable these features.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=806634
ArticleTitle=Configure automatic maintenance in DB2 for Linux, UNIX, and Windows using IBM Data Studio
publish-date=03292012