Automatic table maintenance in DB2, Part 2: Automatic table and index reorganization in DB2 for Linux, UNIX, and Windows

How does auto reorg actually work?

The autonomic capabilities of IBM® DB2® for Linux®, UNIX®, and Windows® lighten the load of the DBA and enable you to maximize performance. This article demonstrates how to enable and configure automatic reorganization, monitor automatic reorganization progress, and detect errors. It provides an overview of the automatic reorganization processing to help you understand how and when this automatic feature determines that reorganization of a table or index is required, and what type of reorganization will be performed. Finally, a set of reorg best practices is presented. This article belongs to a two-part series on autonomic table maintenance in DB2.

Ivan Popivanov (ivannp@ca.ibm.com), Software Developer, IBM, Software Group

Ivan PopivanovIvan Popivanov is a Software Engineer in the DB2 Optimizer and RUNSTATS team, having previously worked in the Autonomic Computing team where was the lead developer of Auto-Runstats. Ivan has five years of experience with IBM. He has previously co-authored two conference papers and several patent applications. Ivan holds degrees in Computer Science from the University of Toronto and from Sofia University (Bulgaria).



Scott Walkty (swalkty@ca.ibm.com), Software Developer, IBM, Software Group

Scott WalktyScott Walkty is a Software Developer on the DB2 Workload Management Team. He previously worked for five years on the DB2 Tools Team ,where he was one of the developers of Automatic Table Reorganization. Scott holds a Masters degree in Computer Science from the University of Manitoba.



Angela Yang (ayyang@ca.ibm.com), Software Developer, IBM, Software Group

Angela YangAngela Yang is a Software Developer at the IBM Toronto Lab. For the past 3 years she has worked in several areas of DB2 development including administration tools and runstats. She is currently working on DB2 Query Optimizer development team.



Beck Tang (becktang@ca.ibm.com), Software Developer, IBM

Beck TangBeck Tang has six years of experience as a member of the IBM SAP Integration and Support Center at the IBM Toronto Lab. His current role includes certification testing of SAP NetWeaver with IBM DB2 UDB and assisting customers with problem analysis and troubleshooting. Mr. Tang is also a customer advocate providing custom-tailored support for large customer accounts running SAP and DB2 UDB. In addition, he is a DB2 Certified Solution Expert. He is a co-author of the red book, "SAP Solutions on IBM DB2 UDB V8.2.2 Handbook" and white paper, "SAP/DB2 and High Availability on Sun Cluster 3.X". Mr. Tang can be reached at becktang@ca.ibm.com.



12 July 2007

Also available in Russian

Introduction

A common maintenance task performed by database administrators is the reorganization of data in tables and indexes, called "reorg." Table reorganization is used to defragment a table, reclaim free space, and eliminate overflow rows in order to improve data access performance. Optionally it can be used to reorder data in the order of a particular index, to optimize queries that use that index.

Database administrators are faced with the challenge of determining when a table or index needs to be reorganized, determining the type of reorganization required, and then performing the reorganization and checking the results to ensure there were no errors.

This article provides an in-depth look into the automatic reorganization feature. After reading this article, you will be able to:

  • Set up and configure automatic reorganization. You'll learn the different configuration options and the tradeoffs between them.
  • Understand how the automatic reorganization feature works. In particular, you'll learn how the feature determines reorganization is required, and what types of reorganization will be performed. With this knowledge, you will be able to predict the behavior of automatic reorganization.
  • Monitor automatic reorganization progress and detect any errors that occur during the automation.

This article also presents a set of best practices that will help you use the feature to its full potential.

Note that this article focuses primarily on automatic reorganization in DB2 9. Differences in automatic reorganization between DB2 9 and DB2 v8.2 will be discussed in a later section.

Read on, or link directly to the section that interests you:


Enable and configure automatic reorganization

The key elements in setting up automatic reorganization are:

  1. Enabling the feature by updating the database configuration (that is, turning the feature on)
  2. Defining a window of time during which the automatic reorganization feature may perform table reorganizations (specifying when reorganizations may occur).

Enabling and disabling automatic reorganization

The automatic reorganization feature is enabled or disabled using the AUTO_REORG database configuration parameter. The AUTO_REORG database configuration parameter is part of a hierarchy of automatic maintenance database configuration parameters. These parameters can be viewed using the GET DB CONFIG CLP command. For example:

Listing 1. GET DB CONFIG output
CONNECT TO <db alias>
GET DB CONFIG
CONNECT RESET
...
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 statistics profiling    (AUTO_STATS_PROF) = OFF
       Automatic profile updates         (AUTO_PROF_UPD) = OFF
     Automatic reorganization               (AUTO_REORG) = OFF

This hierarchy, shown by the indentation of the configuration parameters in the listing above, allows a database administrator to enable or disable automatic table maintenance features selectively (on a feature-by-feature basis) 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 their actual value. Similarly, if AUTO_TBL_MAINT is set to OFF, the AUTO_RUNSTATS, AUTOSTATS_PROF, AUTO_PROF_UPD and AUTO_REORG parameters are considered to have an effective value of OFF.

Hence, to enable automatic reorganization, the AUTO_MAINT, AUTO_TBL_MAINT and AUTO_REORG parameters must all have a value of ON.

You can enable automatic reorganization by issuing the following command:

Listing 2. Enable automatic reorg by updating database configuration
CONNECT TO <db alias>
UPDATE DB CONFIG USING AUTO_MAINT ON AUTO_TBL_MAINT ON AUTO_REORG ON
CONNECT RESET

On a DB2 instance where the database partitioning feature is enabled, this command must be issued on the catalog partition. Setting the configuration parameter on any other partition will have no effect. (For example, if you connect to a non-catalog partition and issue the above command, automatic reorganization will not be enabled.)

The AUTO_REORG configuration parameter can be updated dynamically. After issuing the commands above, automatic reorganization is enabled. There is no need to re-activate the database.

You can disable automatic reorganization by issuing the following command:

Listing 3. Disabling automatic reorg by updating database configuration
CONNECT TO <db alias>
UPDATE DB CONFIG USING AUTO_REORG OFF
CONNECT RESET

Again, if the database partitioning feature is enabled, this command must be issued on the catalog partition.

To check if AUTO_REORG is enabled, use the GET DB CONFIG command as shown above on the catalog partition. It is important that you always inspect the values of each of the AUTO_MAINT, AUTO_TBL_MAINT and AUTO_REORG configuration parameters. Looking at the value of AUTO_REORG alone is not sufficient.

Configuring automatic reorganization

Automatic reorganization behavior is controlled through a maintenance policy. The maintenance policy specifies:

  • An offline maintenance window. This is a window of time during which automatic reorganization will perform offline table and index reorganizations. Note: this is the only part of the maintenance policy that does not have a default value. Since tables will be taken offline (no write access allowed to the tables and no read access during the REPLACE phase of reorganization) during this window, the requirements for this window are different for each user. An offline window must be specified in order to use the automatic reorganization feature. If no offline window is specified and automatic reorganization is enabled, DB2 will fail each attempt to automatically reorganize a table. These failures will be reported through the automatic reorganization health indicator. Refer to the Health snapshots section, example 4 for details.
  • A set of tables that will be considered for reorganization. These tables can be limited by size or name. By default, all user tables are considered for automatic reorganization, regardless of table size.
  • A set of options that modify the reorganization operation when it is applied to a table (for example, to keep or rebuild compression dictionaries when performing table reorganization)
  • An online maintenance window. In DB2 9, automatic reorganization of indexes can be performed online (read and write access to the indexes is allowed during reorganization). If automatic online index reorganization is configured, the online maintenance window is the window of time during which the online index reorganizations will be performed.

The maintenance policy can only be updated using the DB2 Control Center graphical interface. There is no command line or programmatic interface.

To set the initial maintenance policy or change an existing maintenance policy, perform the following steps.

Step 1: Launch the DB2 Control Center

From the DB2 command line, type the following command:

db2cc

Step 2: Open the Configure Automatic Maintenance Wizard

In the Control Center main window, expand the navigation tree on the left hand side and open the databases folder. Right click on the database for which you are configuring automatic maintenance, and select the Configure Automatic Maintenance menu option from the pop up menu as shown in Figure 1.

Figure 1. Launch the Configure Automatic Maintenance Wizard from the Control Center
Launch the Configure Automatic Maintenance Wizard from the Control Center

Step 3: Review the introduction panel

The first panel in the configure automatic maintenance wizard provides a brief overview of automatic maintenance. Click on the Next button at the bottom of the window to continue.

Figure 2. Introduction panel
Introduction panel

Step 4: Configure the operation type panel

The operation type panel allows you to choose whether you are using the wizard to configure automatic maintenance, or disable automatic maintenance. For configuration, leave the radio button set to Change automation settings and click on the Next button. Note that disabling automation settings will simply update the database configuration parameters discussed in the previous section, setting the values to OFF.

Figure 3. Operation type panel
Operation type panel

Step 5: Configure the maintenance window panel

Use the configure maintenance window panel to specify windows of time for automatic maintenance operations. A DB2 database can have at most two windows specified:

  • An online window -- For maintenance operations that leave the object of the maintenance accessible. In the case of a table, users will continue to be able to read from and write to the table.
  • An offline window -- During which time the object undergoing maintenance will be inaccessible to users. In the case of a table, users will not be able to read from or write to the table.

For automatic reorganization, all table reorganizations occur during the offline maintenance window. Index reorganizations may occur during either the online window or the offline window, depending on whether or not the online index reorganization option is specified (see step 7).

Note that maintenance windows are used to specify the start time of automatic maintenance operations. Any operations that run past the end of a window are allowed to proceed to completion. For example, if a maintenance window is 7:00 PM -> 5:00 AM every weekday, and a table reorganization is started at 4:30 AM, the reorganization will not be terminated if it is still executing at 5:00 AM. The reorganization will be allowed to finish.

By default, all DB2 databases have an online window defined that is 24x7 (that is, all hours of all days) and no offline window (since there is no acceptable default for all users). An offline window MUST be defined in order to use the automatic reorganization feature. If automatic reorganization is enabled and an offline window is not defined, DB2 will fail each attempt to automatically reorganize a table. These failures will be reported through the automatic reorganization health indicator. Refer to the Health snapshots section, example 4 for details. Also note that if you configure automatic reorganization to perform online index reorganizations, you should consider changing the online maintenance window so that it does not overlap with the offline maintenance window. This is explained further in the section on the automatic reorganization process.

To define an offline window, select the Change button next to the offline maintenance window description.

Figure 4. Configure maintenance window panel
Configure Maintenance Window Panel

This will launch the maintenance window specification dialog. Use this dialog to define a maintenance window that meets your requirements. When defining the offline window, keep in mind:

  • While a table is undergoing reorganization in the offline window, it will be inaccessible (no read or write access allowed). So the window should not overlap periods of usage for the tables.
  • If the window is too small (for example, suppose a window is defined for 1 hour, once a month), this will limit the benefits of automatic reorganization, since there will not be sufficient time to perform the reorganizations.
Figure 5. Maintenance window specification dialog
Maintenance Window Specification Dialog

When you have finished defining the maintenance window, close the specification dialog and click the Next button on the maintenance window panel.

Step 6: Update the notification list panel

The notification list panel is used to specify a set of e-mail addresses for users who should receive e-mail notification whenever any health indicators enter an alert state. The health monitor has a health indicator, db.tb_reorg_req, which enters an alert state whenever automatic reorganization fails to reorganize a table (this is discussed in detail in the section on monitoring). If you wish to receive e-mail notifications when automatic reorganization failure occurs (that is, receive e-mails when automatic reorganization fails to reorganize a table), you need to configure the list of e-mail addresses using this panel. Note that there is no way to request e-mail notifications for a particular health indicator (for example, just db.tb_reorg_req). Any users configured to receive e-mail notification will receive an e-mail whenever ANY health indicator enters an alert state.

This step is optional. You can select Next immediately to skip this panel.

In order to configure e-mail notification, you first need to have performed the following steps:

  1. Install the DB2 Administration Server. This topic is outside the scope of this paper. Refer to the DB2 documentation for details.
  2. Update the DB2 Administration Server SMTP_SERVER configuration parameter. This can be done using the UPDATE ADMIN CONFIG CLP command. For example:

    Listing 4. Updating Adminisration Server configuration
    UPDATE ADMIN CONFIG USING SMTP_SERVER myserver.domain.com

    The SMTP_SERVER configuration parameter must point to an unauthenticated SMTP server (authenticated SMTP is not supported by the DB2 Administration Server).

Assuming the DB2 Administration server has been installed and the SMTP_SERVER configuration parameter has been configured, you can add e-mail addresses on the notification list panel. Select the Manage Contacts button to create new contacts which are added to a master contact list stored by the DB2 Administration Server. Then, choose addresses from this contact list and select the right arrow button to add these contacts to the list of users that will receive health notification e-mails from the health monitor. When you are finished, click the Next button.

Figure 6. Notification list panel
Notification List Panel

Step 7: Update the maintenance activities panel

The maintenance activities panel allows you to enable/disable automatic reorganization, enable/disable the automatic reorganization health indicator and configure the behavior of automatic reorganization.

The automate column corresponds to the AUTO_REORG configuration parameter. If this column has a check mark, the automatic reorganization feature is enabled (that is, AUTO_MAINT, AUTO_TBL_MAINT and AUTO_REORG are all set to ON and the effective value of AUTO_REORG is ON). If this column is not checked, check it to enable automatic reorganization. By default, this column will not be checked (that is, automatic reorganization is not enabled by default).

The Notify column corresponds to the db.tb_reorg_req health indicator. This health indicator monitors the progress of automatic reorganization. If there is a check mark, the health indicator is enabled. If there is no check mark, the health indicator is disabled. If you are enabling automatic reorganization, it is strongly encouraged that you enable the health indicator (that is, leave the Notify column checked). The health indicator is the primary mechanism for monitoring behavior, detecting problems and performing problem determination. The section on monitoring describes the health indicator in more detail. Note, checking this column does not enable e-mail notifications. To receive e-mail notifications when the db.tb_reorg_req health indicator enters an alert state, you must follow the steps in Step 6 above.

Figure 7. Maintenance activities panel
Maintenance activities panel

The lower portion of the panel describes the settings for automatic reorganization. Click the Configure settings button to launch the Configure Settings dialog.

The Configure Settings dialog has two tabs. The first tab is used to define the table scope. Table scope identifies which tables will be considered for automatic reorganization. There are two elements that can be configured:

  1. Name criteria

    The name criteria allows you to filter tables based on different attributes like name, schema, and comment.

  2. Size criteria

    The size criteria is a per-partition table size that only applies to offline reorganization operations. Tables exceeding this per-partition size limit will not be considered for offline table or index reorganizations. Tables exceeding this limit will still be considered for online index reorganizations if online index reorganizations are enabled for automatic reorganization. Consider setting the size criteria if you have large tables. Reorganization will take a long time on these tables, and will require a large offline maintenance window (period during which tables can be made inaccessible and reorganizations performed).

Automatic reorganization estimates the per-partition table size using the formula (SYSSTAT.TABLES.CARD * SYSSTAT.TABLES.AVGROWSIZE ) / (Number of partitions in the partition group for the table space that contains the table) .

Figure 8. Configure settings dialog, table scope
Configure Settings Dialog, Table Scope

The second tab of the settings dialog allows you to specify options to modify the behavior of the reorganization operations that are performed by the automatic reorganization feature. There are three options that can be specified:

  1. The option to use system temporary table space:

    By default, when a table is being reorganized, a working copy of the table is stored in the table spaces that contain the table. If the table space does not have sufficient size to store the copy, and the table space is not defined as resizable (or is resizable, but the underlying file systems run out of space), then the reorganization operation will fail. When the "use system temporary table space" option is enabled, automatic reorganization will automatically choose a temporary table space of compatible page size to be used during the reorganization operation for the working copy of the table.

    Refer to the DB2 documentation for more information on the row compression feature. If you do not have row compression enabled, you can ignore this option.

  2. The compression dictionary option:

    During table reorganization, you have the option to keep the existing compression dictionary for a table or rebuild the compression dictionary. Rebuilding the dictionary can lead to better compression, but will also increase the time required to reorganize the table. By default, automatic reorganization will keep any existing compression dictionary.

    Refer to the DB2 documentation for more information on the row compression feature. If you do not have row compression enabled, you can ignore this option.

  3. Index reorganization mode option:

    This option indicates whether indexes will be reorganized in the online maintenance window or the offline maintenance window. When the online index reorganization mode is specified, the reorganization commands executed by automatic reorganization will include the ALLOW WRITE ACCESS option. Users will still be able to read and write to the index while it is being reorganized. It is recommended that the online mode be used for indexes. Online reorganizations will take more time, but they can use the online maintenance window which is typically much larger than the offline maintenance window.

Some types of tables do not support online index reorganization (for example, MDC tables). For these tables, automatic reorganization will perform the index reorganizations offline in the offline maintenance window, regardless of the index reorganization mode specified.

Figure 9. Configure settings dialog, reorganization options
Configure settings dialog, reorganization options

After configuring automatic reorganization behavior on the Maintenance Activities Panel, click the Next button to advance to the summary panel.

Step 8: Summary panel

The summary panel provides an overview of all the configuration changes that are being made. Review the changes and, if you are satisfied, click the Finish button. The changes are applied to the database immediately (that is, no need to restart the instance or reactivate the database). You have successfully enabled and configured automatic reorganization.

Figure 10. Summary Panel
Summary Panel

Advanced configuration

In the automatic reorganization Configure Settings dialog, it is possible to filter tables by specifying a where clause on a select statement applied to SYSCAT.TABLES.

Figure 11. Configure settings dialog, table scope using a WHERE clause
Configure settings dialog, table scope using a WHERE clause

This where clause permits a great deal of flexibility in specifying which tables should be considered. You are not limited to only reorganizing tables in particular schemas or conforming to some naming scheme.

One approach that can be useful is the following. Create a control table containing the names of all the tables that you want considered for automatic reorganization. For example:

Listing 5. Creating a control table
CREATE TABLE ATM.AUTO_REORG_CTL( TABSCHEMA VARCHAR(128), TABNAME VARCHAR(128))

Populate this table with the names of all tables you wish to be considered for reorganization. Then define the following WHERE clause in the automatic reorganization policy.

Listing 6. WHERE clause for reorg policy
(TABSCHEMA, TABNAME) IN (SELECT CTL.TABSCHEMA, CTL.TABNAME FROM ATM.AUTO_REORG_CTL AS CTL)

Now only tables listed in the control table will be considered for automatic reorganization. If you wish to omit a particular table for some period of time, simply remove it from your control table.


How automatic reorganization works

This section provides an overview of how automatic reorganization works. After reading this section, you will have a better understanding of how often automatic reorganization runs, and how automatic reorganization makes decisions about when to reorganize tables and what type of reorganization to apply.

When to reorganize?

Automatic reorganization bases its decisions about whether or not to reorganize tables by using REORGCHK (specifically, the REORGCHK stored procedures; REORGCHK_TB_STATS and REORGCHK_IX_STATS). REORGCHK evaluates a set of 8 formulas for a table and compares the results of those formulas against a set of predetermined threshold values. If the thresholds are exceeded, reorganization is recommended for the table or the indexes of the table (depending on which threshold is exceeded). The following is a brief description of what is measured by each formula. Refer to the DB2 documentation for the exact formulas and thresholds, which may change slightly between releases.

Listing 7. Formulas used by REORGCHK
F1: Checks percentage of overflow rows in a table
F2: Checks percentage of free space in a table
F3: Checks percentage of empty pages in a table
F4: Checks cluster ratio (degree to which rows in table are in the same order as the 
index)
F5: Checks percentage of free space on index leaf pages
F6: Checks if number of levels in index can be reduced
F7: Checks percentage of RIDs (Record IDs) in an index that are marked as deleted 
F8: Checks percentage of index leaf pages that have all of their RIDS (Record IDs) marked
 as deleted.

It is important to note that the REORGCHK formulas are based on table and index statistics. If the statistics are not up to date, the recommendations made by REORGCHK will not be accurate. For example, suppose a large percentage of the rows in the table are overflow rows. The F1 formula from REORGCHK indicates that reorganization is required. If you reorganize the table and do not update the table statistics by issuing a RUNSTATS command, the statistics will still indicate that the table contains a high percentage of overflow rows, and REORGCHK will continue to recommend that the table be reorganized.

For this reason it is always recommended that when using automatic reorganization, automatic statistics collection should be enabled. This will ensure that statistics are kept as up to date as possible, so that the correct decisions will be made by automatic reorganization.

If you are curious whether automatic reorganization will reorganize a given table or index, you can always invoke the REORGCHK_TB_STATS (evaluates formulas F1 -> F3) or REORGCHK_IX_STATS (evaluates formulas F4 -> F8) procedures manually and look at the results. When a threshold is exceeded, an asterisk (*) is returned in the REORG column in the position corresponding to the formula that was exceeded.

For example:

Listing 8. Invoking REORGCHK_TB_STATS
CONNECT TO <db alias>
CALL REORGCHK_TB_STATS('T', 'ATM.TEST')"      


  Result set 1
  --------------

  TABLE_SCHEMA TABLE_NAME ...   F1          F2          F3          REORG
  ------------ ---------- ...   ----------- ----------- ----------- ----- 
  ATM          TEST       ...   0          17           25          -**   

  1 record(s) selected.

  Return Status = 0

In this case, the REORG column has a value of -**, which means formulas F2 and F3 are exceeded. To check the F4 -> F8 formulas for the table, you would call the REORGCHK_IX_STATS stored procedure in a similar manner.

What type of reorganization will be used?

The type of reorganization applied to a table depends on which REORGCHK formula is exceeded. Automatic reorganization checks the formulas in sequence from F1 to F8. The first formula that is exceeded determines the type of reorganization that is applied. Table 1 summarizes which type of reorganization will be applied for each of the formulas.

Table 1. Automatic reorganization operation based on REORGCHK formulas
FormulaReorganization Applied
F1REORG TABLE <table name>
F2REORG TABLE <table name>
F3REORG TABLE <table name>
F4REORG TABLE <table name> INDEX <index name> INDEXSCAN where <index name> is the name of the clustering index for the table (if it exists) or the first unique or primary key index if there is no clustering index
F5REORG INDEXES ALL FOR TABLE <table name>
F6REORG INDEXES ALL FOR TABLE <table name>
F7REORG INDEXES ALL FOR TABLE <table name> CLEANUP ONLY ALL
F8REORG INDEXES ALL FOR TABLE <table name> CLEANUP ONLY ALL

Note that the reorganization applied for a given REORGCHK formula is further modified as shown in Table 2 based on the configuration options specified in the maintenance policy.

Table 2. Modifications to reorganization based on automatic reorganization configuration
OptionsFormulasModifier
Rebuild dictionaryF1, F2, F3, F4RESETDICTIONARY
Use system temporary table spaceF1, F2, F3, F4USE <table space name> where automatic reorganization will choose <table space name> by selecting a system temporary table space of the correct page size.
Reorganize indexes onlineF5, F6, F7, F8ALLOW WRITE ACCESS

Automatic reorganization process

The processing of automatic reorganization is broken down into two distinct steps; evaluation and execution. The evaluation step is responsible for determining which tables require reorganization. The execution step performs the reorganizations on the tables, in the configured maintenance window. Automatic reorganization processing only occurs while a database is active. When a database is inactive, automatic reorganization will not check tables and will not schedule or perform reorganizations.

The evaluation step occurs for the first time two hours after a database has been activated, and again every two hours after that. The two-hour interval is subject to change from one version of DB2 to the next. The evaluation occurs on the same interval as the reorganization required health indicator (db.tb_reorg_req, refer to the section on monitoring for more details). You can check the current evaluation interval by issuing the following statement (output is in seconds):

Listing 9. Checking the current evaluation interval
CONNECT TO <db alias>
SELECT REFRESH_INTERVAL FROM TABLE(HEALTH_GET_IND_DEFINITION('')) AS T 
WHERE NAME = 'db.tb_reorg_req'

REFRESH_INTERVAL    
--------------------
                7200

  1 record(s) selected.

The evaluation interval is not user configurable. The interval was selected to minimize the impact of evaluation on system performance.

Note, it is very important that the database remains activated in order for automatic reorganization to work properly. When the database is deactivated and reactivated, the evaluation interval is reset and any scheduled reorganizations are removed from memory.

During each evaluation of automatic reorganization, the following takes place (Figure 12):

Figure 12. Automatic reorganization evaluation processing
Automatic Reorganization Evaluation Processing

Each table identified in the filter criteria of the automatic maintenance policy is examined. First the table is checked to see if it has any statistics. The last statistics update timestamp is compared against the last time automatic reorganization looked at the table. Since automatic reorganization decisions are based on the REORGCHK formulas, and those formulas are evaluated using table statistics, if the statistics for a table have not changed since the last time the table was examined, the results of the REORGCHK formulas will not have changed, so there is no point considering the table. You can check the last time statistics were collected for a table by examining the value of the STATS_TIME column in the row corresponding to the table in SYSCAT.TABLES.

If the statistics have been updated for a table or if statistics are present but the table has never been evaluated for automatic reorganization, the reorgchk formulas are applied to the table by invoking the REORGCHK_TB_STATS and REORGCHK_IX_STATS stored procedures.

The REORGCHK_TB_STATS procedure is invoked first to check if table reorganization is required (formula F1, F2 or F3 exceeded). If reorganization is required on a table, the table size filter (if one was configured) is applied.

Automatic reorganization estimates the per-partition size of the table using the formula (SYSSTAT.TABLES.CARD * SYSSTAT.TABLES.AVGROWSIZE ) / (Number of partitions in the partition group for the table space that contains the table) If the estimated per-partition size of the table is smaller than the size criteria, offline table reorganization is scheduled (all table reorganizations are done in the offline maintenance window). If the per-partition size of the table exceeds the size criteria a table reorganization cannot be done, so automatic reorganization continues to the next step.

Next automatic reorganization checks the results of the F4 formula using the REORGCHK_IX_STATS procedure. This formula is only evaluated if the table has a cluster or primary key index. If a cluster index exists, the formula is checked for the cluster index. Otherwise it is checked for the first primary key index on the table. If the threshold of the F4 formula is exceeded and the estimated size of the table is smaller than the size criteria, a table reorganization is scheduled.

If no table reorganizations were scheduled, the F5 -> F8 formulas (output from the previous REORGCHK_IX_STATS procedure call) are examined. If any of the thresholds for these formulas are exceeded, an index reorganization is scheduled. Index reorganizations may be scheduled to run online (in the online maintenance window) or offline (in the offline maintenance widow), depending on the index reorganization mode that was configured and the type of table.

In all of the cases described previously, once reorganization has been scheduled for a table, automatic reorganization will stop processing the table and start considering the next table identified by the automatic reorganization policy. This means that if a table requires both table and index reorganization, only the table reorganization will be scheduled, because the table reorganization formulas are examined first. Once the table has been reorganized and statistics have been updated, the table will again be considered during the evaluation processing. At this time, since the table no longer requires reorganization, the indexes of the table will be checked and index reorganization will be scheduled if required (that is, if a table requires both table and index reorganization, automatic reorganization will only reorganize the indexes once the table has been reorganized. It will not attempt to reorganize the table and the indexes in parallel).

The second part of the automatic reorganization process is the execution step, where the reorganization is performed. Execution is performed by a scheduler and occurs serially (that is, never more than one automatic maintenance operation will be performed at a time). All scheduled reorganizations (and other automatic maintenance operations, like automatic runstats) are maintained in a queue. When the corresponding maintenance window begins, reorganizations are performed one after another until the end of the window (the type of maintenance window depends on the maintenance policy and the reorganization type. Index reorganizations can be configured to run online in the online window. All other reorganizations will wait for the offline window). Table reorganizations are performed offline, which means that the table will be inaccessible (no write access allowed, and no read access during the REPLACE phase of reorganization) to users during the time that it is being reorganized. If the table is in use when the reorganization operation begins, the user will not be forced off the system. Rather the reorganization operation will wait until it can get exclusive access to the table. Since the reorganizations are performed serially, this will block other automatic reorganizations from taking place, so it is suggested that you do not access tables that are considered for automatic reorganization during the offline maintenance window.

It is important to note that the maintenance window serves as a starting time for reorganization operations. As such, the last reorganization operation started at the end of the maintenance window may run past the end of the maintenance window. For example, in figure 13 below, the reorganization of table 3 exceeds the maintenance window.

Figure 13. Table reorganization in an offline maintenance window
Table reorganization in an offline maintenance window

Since table reorganizations are performed offline, it is important to consider carefully both the maintenance window and the set of tables that will be reorganized automatically. A large offline window is generally not desired, so if you have large tables which could take a long time to reorganize, consider using the maintenance policy size criteria to omit these tables and perform the reorganization manually. Also consider defining an offline maintenance window which is slightly smaller than the actual time during which tables may be made inaccessible so that if a table reorganization extends past the end of the window, users will not immediately be impacted (that is, there is still a buffer of time during which the reorganization may complete without affecting users).

Another important consideration is the definition of the online maintenance window. All automatic reorganization operations (table or index) are considered to have the same priority. If the definition of the online maintenance window overlaps with the offline maintenance window, online index reorganizations may run during the same time period as offline table and index reorganizations. Since maintenance operations are executed serially this can result in online operations blocking offline operations. In order to make the most effective use of an offline maintenance window, only offline operations should be run during this time period. For this reason, it is recommended that at least a portion of the offline maintenance window should not overlap in time with the definition of the online maintenance window.

If automatic statistics collection is enabled, the automatic statistics collection process is notified to consider updating statistics on the newly reorganized table in its next statistics collection interval.

Automatic reorganization and manual reorganization

Manual table and index reorganization will not perform statistics collection, and will not notify automatic statistics collection that a table requires statistics to be updated. If you perform manual table reorganization (that is, manually issue the REORG command for a table or index), you should collect statistics (issue the RUNSTATS command) once the reorganization has completed. This will ensure that statistics are up to date and will prevent automatic reorganization from attempting to reorganize the table.

Automatic reorganization without an offline maintenance window

In some user environments, it may not be possible to define an offline maintenance window (for example, if tables must always remain accessible, 24 hours a day, 7 days a week). In such environments, it is not possible for automatic reorganization to reorganize tables because the automatic reorganization feature always performs table reorganizations offline, in an offline maintenance window.

It is possible to still use automatic reorganization in this scenario to reorganize indexes by doing the following (refer to the section on enabling and configuring automatic reorganization for details):

  1. Enable online automatic index reorganization.
  2. Set the automatic reorganization size criteria (criteria used to determine if a table should be considered for offline reorganization) to a very small value (smaller than the size of any of your tables).

Part (b) is important because automatic reorganization always checks whether table reorganization is required before checking if index reorganization is required. If table reorganization is required and the per-partition table size is smaller than the size criteria, offline table reorganization is scheduled and processing moves on to the next table in the maintenance policy. So an offline maintenance window would be required. However, if the per-partition table size is larger than the size criteria, table reorganization is not considered, and processing continues with the index reorganization checks. The size criteria is a mechanism for limiting the size of objects that are considered for offline operations. By setting a low value, you can prevent any offline operations from being scheduled. And by enabling online index reorganization, indexes will still be reorganized automatically. So using this configuration, an offline maintenance window is not required (because offline reorganizations will never be scheduled).

Keep in mind that with such a setup, table reorganizations are NOT taking place automatically, only index reorganizations (and only for those types of tables that support online index reorganization). To fully make use of the automatic reorganization feature, an offline maintenance window is required as described earlier.


Monitoring automatic reorganization

As an automated process, automatic reorganization is performing work that would typically be performed manually by a database administrator. The ability to see what is going on with automatic reorganization and to detect any problems is key to building trust in the use of the automation. This section will explain to you how to check the progress of automatic reorganization and how to detect any problems that occur.

Health snapshots and the reorganization required health indicator

The primary interface for monitoring automatic reorganization is using the health monitor and the db.tb_reorg_req health indicator. Health indicators measure the health of particular aspects of DB2. The db.tb_reorg_req health indicator enters an attention (non-normal) state whenever a table requires manual reorganization (that is, in the case where automatic reorganization is enabled, this means that automatic reorganization of a table has failed). The health indicator can also be useful when automatic reorganization is not enabled as it still runs the evaluation process of automatic reorganization to detect when tables need to be reorganized.

The current value of a health indicator can be obtained using a health snapshot. This paper will provide examples of health snapshots using the command line. There are several other ways to access health snapshot information which will be described briefly at the end of this section.

To take a health snapshot for a database, issue the following command from the DB2 command line:

Listing 10. Health snapshot
GET HEALTH SNAPSHOT FOR DATABASE ON <db alias>

Note that database health snapshot information will only be returned if the database is active. If the database is inactive, you will see warning SQL1611W returned.

Health snapshot output looks something like the following (note that only the db.tb_reorg_req health indicator is displayed here for clarity, other health indicators have been omitted).

Listing 11. Snapshot example
get health snapshot for db on reorgdb

             Database Health Snapshot 

Snapshot timestamp                         = 03/28/2007 10:26:53.217403

Database name                              = REORGDB
Database path                              = /home/swalkty/swalkty/NODE0000/SQL00002/
Input database alias                       = REORGDB
Operating system running at database server= AIX 64BIT
Location of the database                   = Local
Database highest severity alert state      = Attention

Health Indicators:

    Indicator Name                             = db.tb_reorg_req
       Value                                   = 1
       Evaluation timestamp                    = 03/28/2007 10:26:51.755071
       Alert state                             = Attention

          Collection:

             Name                              = "ATM"."SMALLTABLE"
             Detail                            = REORG TABLE USE TEMPTBSP; SQL22022
             State                             = Automation failed
             Evaluation timestamp              = 03/28/2007 10:26:52.000000

The snapshot header describes when the snapshot was taken, and for which database. The highest severity alert state is an aggregate across all health indicators being evaluated for the database, so it is not directly applicable for automatic reorganization

Listing 12. Snapshot header
Snapshot timestamp                         = 03/28/2007 10:26:53.217403

Database name                              = REORGDB
Database path                              = /home/swalkty/swalkty/NODE0000/SQL00002/
Input database alias                       = REORGDB
Operating system running at database server= AIX 64BIT
Location of the database                   = Local
Database highest severity alert state      = Attention

The health indicators section displays information for all health indicators being evaluated on the database. For simplicity, only the db.tb_reorg_req health indicator is shown here. The top level db.tb_reorg_req health indicator information describes the health of automatic reorganization as a whole. The evaluation timestamp indicates the last time an automatic reorganization evaluation took place. Recall that evaluation is the process when automatic reorganization looks for tables that require reorganization, and occurs roughly every 2 hours (as long as the database remains active). The alert state for db.tb_reorg_req will take on one of two values: Normal or Attention. If the value is Normal, automatic reorganization is functioning correctly, and is reorganizing tables properly. No tables need to be reorganized manually. If the value is Attention, at least one table requires manual reorganization (that is, automatic reorganization failed to reorganize the table for some reason).

Listing 13. Alert state=Attention
Health Indicators:

	Indicator Name                             = db.tb_reorg_req
	   Value                                   = 1
	   Evaluation timestamp                    = 03/28/2007 10:26:51.755071
	   Alert state                             = Attention

Following the high level health indicator information, there is one entry listed for each table that requires manual reorganization (that is, details are provided on tables for which reorganization failed). In the example shown here, automatic reorganization failed to reorganize the table ATM.SMALLTABLE.

Listing 14. Automation failed example
Name                              = "ATM"."SMALLTABLE"
Detail                            = REORG TABLE USE TEMPTBSP; SQL22022
State                             = Automation failed
Evaluation timestamp              = 03/28/2007 10:26:52.000000

The detail field shows you the reorganization options that automatic reorganization attempted to use and the sqlcode for the failure. In this case, automatic reorganization attempted a table reorganization with the "use system temporary table space" option (this option would only be present if enabled through the configuration). The reason for the failure is sqlcode 22022, maintenance window not defined or not long enough. In this case, the user probably forgot to define an offline maintenance window (recall, table reorganizations are run offline in an offline maintenance window).

The state shows the current state for the table. For a regular health snapshot, this state will be automation failed or attention. Automation failed means that an error was encountered trying to reorganize the table. Attention means that the table requires reorganization, and automatic reorganization did not attempt to reorganize the table (for example, if the table exceeds the size criteria or automatic reorganization is not enabled).

The evaluation timestamp shows the last time this table was considered for reorganization (that is, had the REORGCHK formulas applied to it). Note that this timestamp will be different than the evaluation timestamp. Remember that evaluation runs approximately every two hours. However, once a table has been evaluated (REORGCHK formulas applied) the first time, it will only be evaluated again once statistics have been updated.

By default the health snapshot information for the db.tb_reorg_req health indicator only shows information for tables that require manual reorganization. Tables which have been checked and found to not require reorganization (or require reorganization, but the reorganization is being automated) are not displayed. Sometimes it is useful to gather information about these tables too. You can modify the health snapshot command to request that information be returned for all tables by adding the WITH FULL COLLECTION clause. For example:

Listing 15. Requesting a snapshot for all tables
GET HEALTH SNAPSHOT FOR DATABASE ON <db alias> WITH FULL COLLECTION

The output for the db.tb_reorg_req health indicator now displays one entry for each table that is considered as part of the automatic reorganization scope. Similar to the entries for tables which require reorganization, you can tell from this output the last time that a table was evaluated for reorganization (that is, had the REORGCHK formulas applied to it). In this case, the state field will take on additional values: not yet evaluated, normal and automated. The not yet evaluated state means that automatic reorganization has not yet evaluated the REORGCHK formulas for the table (typically because there are no statistics available for the table). The normal state means that the table does not require reorganization. The automated state means that the table has been scheduled for reorganization which will take place in an upcoming maintenance window.

For illustrative purposes, it's simplest to consider a number of examples of the most common things you will see when taking a health snapshot.

Example 1: Database not active, no health snapshot information is returned

Listing 16. Example 1
GET HEALTH SNAPSHOT FOR DB ON SAMPLE

SQL1611W  No data was returned by Database System Monitor.

Example 2: Database is active, automatic reorganization has not yet evaluated (that is, database has not yet been active for at least 2 hours).

Listing 17. Example 2
GET HEALTH SNAPSHOT FOR DB ON SAMPLE

             Database Health Snapshot

Snapshot timestamp                         = 03/28/2007 13:07:04.794350

Database name                              = SAMPLE
Database path                              = /home/swalkty/swalkty/NODE0000/SQL00001/
Input database alias                       = SAMPLE
Operating system running at database server= AIX 64BIT
Location of the database                   = Local
Database highest severity alert state      = Not yet evaluated

Health Indicators:

   Not yet evaluated

Example 3: Health snapshot, automatic reorganization working properly, no manual reorganization is required.

Listing 18. Example 3
			GET HEALTH SNAPSHOT FOR DB ON SAMPLE

             Database Health Snapshot

Snapshot timestamp                         = 03/28/2007 10:25:08.460731

Database name                              = SAMPLE
Database path                              = /home/swalkty/swalkty/NODE0000/SQL00002/
Input database alias                       = SAMPLE
Operating system running at database server= AIX 64BIT
Location of the database                   = Local
Database highest severity alert state      = Normal

Health Indicators:

    Indicator Name                             = db.tb_reorg_req
       Value                                   = 0
       Evaluation timestamp                    = 03/28/2007 10:25:07.529251
       Alert state                             = Normal

Example 4 : Health snapshot, automatic reorganization failed, no offline maintenance window was defined. As discussed in the section on enabling and configuring automatic reorganization, an offline maintenance window must be defined for the automatic reorganization feature to function properly. If no offline window is defined, automatic reorganization will be unable to perform any offline reorganizations (in particular, table reorganizations which automatic reorganization always performs offline), and errors will be reported through the health indicator as below. The detail field contains sqlcode -22022, which indicates that either an offline maintenance window was not specified, or is not large enough.

Listing 19. Example 4
GET HEALTH SNAPSHOT FOR DB ON SAMPLE

             Database Health Snapshot

Snapshot timestamp                         = 03/28/2007 10:26:53.217403

Database name                              = SAMPLE
Database path                              = /home/swalkty/swalkty/NODE0000/SQL00002/
Input database alias                       = SAMPLE
Operating system running at database server= AIX 64BIT
Location of the database                   = Local
Database highest severity alert state      = Attention

Health Indicators:

    Indicator Name                             = db.tb_reorg_req
       Value                                   = 1
       Evaluation timestamp                    = 03/28/2007 10:26:51.755071
       Alert state                             = Attention

          Collection:

             Name                              = "ATM"."SMALLTABLE"
             Detail                            = REORG TABLE USE TEMPTBSP; SQL22022
             State                             = Automation failed
             Evaluation timestamp              = 03/28/2007 10:26:52.000000

Example 5: Health snapshot, automatic reorganization not attempted for a table which exceeded the size criteria.

Listing 20. Example 5
GET HEALTH SNAPSHOT FOR DB ON SAMPLE

             Database Health Snapshot

Snapshot timestamp                         = 03/28/2007 10:36:06.261868

Database name                              = SAMPLE
Database path                              = /home/swalkty/swalkty/NODE0000/SQL00002/
Input database alias                       = SAMPLE
Operating system running at database server= AIX 64BIT
Location of the database                   = Local
Database highest severity alert state      = Attention

Health Indicators:

    Indicator Name                             = db.tb_reorg_req
       Value                                   = 1
       Evaluation timestamp                    = 03/28/2007 10:36:04.844324
       Alert state                             = Attention

          Collection:

             Name                              = "ATM"."LARGETABLE"
             Detail                            = REORG TABLE; TABLE EXCEEDS OFFLINE 
REORG SIZE CRITERIA
             State                             = Attention
             Evaluation timestamp              = 03/28/2007 10:35:40.000000

Example 6: Health snapshot with full collection option, no statistics have ever been collected for table employee, so the table is not yet evaluated (no statistics means reorgchk formulas cannot be evaluated). In this case, the table has no evaluation timestamp, since automatic reorganization has not yet evaluated the table (the health indicator does have an evaluation timestamp, because automatic reorganization evaluation processing has occurred for all tables in scope. But for this particular table, the REORGCHK formulas could not yet be evaluated).

Listing 21. Example 6
GET HEALTH SNAPSHOT FOR DB ON SAMPLE WITH FULL COLLECTION

             Database Health Snapshot

Snapshot timestamp                         = 03/28/2007 12:52:48.477502

Database name                              = SAMPLE
Database path                              = /home/swalkty/swalkty/NODE0000/SQL00001/
Input database alias                       = SAMPLE
Operating system running at database server= AIX 64BIT
Location of the database                   = Local
Database highest severity alert state      = Alarm

Health Indicators:

    Indicator Name                             = db.tb_reorg_req
       Value                                   = 0
       Evaluation timestamp                    = 03/28/2007 12:52:46.460665
       Alert state                             = Normal

          Collection:

             Name                              = "DBUSER"."EMPLOYEE"
             State                             = Not yet evaluated

Example 7: Health snapshot with full collection option, no tables require manual reorganization, table DBUSER.EMPLOYEE is in the automated state which means that reorganization is required and the table is scheduled for reorganization (which will take place in the offline maintenance window), table DBUSER.CL_SCHED is normal (no reorganization required). The last time automatic reorganization checked table DBUSER.CL_SCHED (that is, applied reorgchk formulas) was Mar 28, 2007 at 11:57:16. For both these tables, you do not need to take any action.

Listing 22. Example 7
GET HEALTH SNAPSHOT FOR DB ON SAMPLE WITH FULL COLLECTION

             Database Health Snapshot

Snapshot timestamp                         = 03/28/2007 12:52:48.477502

Database name                              = SAMPLE
Database path                              = /home/swalkty/swalkty/NODE0000/SQL00001/
Input database alias                       = SAMPLE
Operating system running at database server= AIX 64BIT
Location of the database                   = Local
Database highest severity alert state      = Alarm

Health Indicators:

    Indicator Name                             = db.tb_reorg_req
       Value                                   = 0
       Evaluation timestamp                    = 03/28/2007 12:52:46.460665
       Alert state                             = Normal

          Collection:

             Name                              = "DBUSER"."EMPLOYEE"
             State                             = Automated
             Evaluation timestamp              = 03/28/2007 11:57:16.000000


             Name                              = "DBUSER"."CL_SCHED"
             State                             = Normal
             Evaluation timestamp              = 03/28/2007 11:57:16.000000

As mentioned earlier, there are a number of other interfaces to snapshot information. These include the db2GetSnapshot C API, the Health Center graphical interface and the following set of SQL interfaces:

  • HEALTH_DB_HI - Information on health indicators.
  • HEALTH_DB_HIC - Information on individual tables

Note that there is no SQL interface equivalent to the WITH FULL COLLECTION option.

To summarize, using the health snapshot, you can tell at a glance:

  • The last time automatic reorganization evaluation took place.
  • The set of tables that require manual reorganization (and, if applicable, the reason automatic reorganization failed).
  • The set of tables which do not require reorganization (if the FULL COLLECTION option is specified, look for tables in the NORMAL state).
  • The set of tables which are currently scheduled for reorganization (if the FULL COLLECTION option is specified, look for tables in the AUTOMATED state)

By default, the health monitor and the db.tb_reorg_req health indicators are both enabled. You can check if the health monitor is enabled using the GET DBM CONFIG command.

Listing 23. Checking to see if the health monitor is enabled
GET DBM CONFIG 

          Database Manager Configuration

     Node type = Database Server with local clients

 Database manager configuration release level            = 0x0c00
...

   Statement                              (DFT_MON_STMT) = OFF
   Table                                 (DFT_MON_TABLE) = OFF
   Timestamp                         (DFT_MON_TIMESTAMP) = ON
   Unit of work                            (DFT_MON_UOW) = OFF
Monitor health of instance and databases   (HEALTH_MON) = ON
 SYSADM group name                        (SYSADM_GROUP) =

If the health monitor is disabled, you can dynamically enable it using the following command:

Listing 24. Enabling the health monitor
ATTACH TO <nodename>
UPDATE DBM CONFIG USING HEALTH_MON ON IMMEDIATE

You can check if the db.tb_reorg_req health indicator is enabled using the GET ALERT CONFIG command.

Listing 25. Checking to see if the db.tb_reorg_req health indicator is enabled
GET ALERT CONFIG FOR DATABASE ON <db alias>

            Alert Configuration

  Indicator Name                     = db.db_op_status
      Default                        = Yes     
      Type                           = State-based
      Sensitivity                    = 0
      Formula                        = db.db_status;
      Actions                        = Disabled
      Threshold or State checking    = Enabled
...

  Indicator Name                     = db.tb_reorg_req
      Default                        = Yes     
      Type                           = Collection state-based
      Sensitivity                    = 0
      Actions                        = Disabled
     Threshold or State checking  = Enabled

If the db.tb_reorg_req health indicator is disabled, you can enable it using the following command:

Listing 26. Enabling the db.tb_reorg_req health indicator
UPDATE ALERT CONFIG FOR DATABASE ON <db alias> USING DB.TB_REORG_REQ 
	SET THRESHOLDSCHECKED YES

Diagnostic logging

Automatic reorganization writes log points to the db2diag.log as tables are processed during the execution step (that is, when reorganization of a table takes place, during the maintenance window). An entry will be written when the reorganization of a table has started and when the reorganization has completed. These log entries will always be written, regardless of the value of the DIAGLEVEL database manager configuration parameter. In the case of an error, the "stop" log point will include the sqlcode for the error that was encountered.

Listing 27. Example automatic reorganization log points
2007-03-28-10.26.57.436904-240 I8286391A371       LEVEL: Event
PID     : 1155132              TID  : 1293        PROC : db2fmp
INSTANCE: swalkty              NODE : 000
EDUID   : 1293                 EDUNAME: db2acd
FUNCTION: DB2 UDB, Automatic Table Maintenance, db2AutoReorgExec, probe:10
START   : Automatic reorg has started on table REORGDB ."ATM     "."SMALLTABLE"


2007-03-28-10.26.58.174382-240 I8295995A424       LEVEL: Event
PID     : 1155132              TID  : 1293        PROC : db2fmp
INSTANCE: swalkty              NODE : 000
APPID   : *LOCAL.swalkty.070328142710
EDUID   : 1293                 EDUNAME: db2acd
FUNCTION: DB2 UDB, Automatic Table Maintenance, db2AutoReorgExec, probe:10
STOP    : Automatic reorg has completed successfully on table REORGDB ."ATM     "."SMALLTABLE"

When the DIAGLEVEL database manager configuration parameter is set to level 4 (all log points written) automatic reorganization will also write log entries at the start and end of each evaluation interval (that is, roughly every two hours, recall from the section on how automatic reorganization works, evaluation is the part of automatic reorganization that runs on a regular interval, and checks if tables require reorganization). Typically these log points are not necessary, since they will occur quite frequently and consume a lot of space in the diagnostic log. However, if you suspect a problem with automatic reorganization evaluation (for example, database has been active for greater than 2 hours, and the db.tb_reorg_req health indicator is enabled, but the health indicator does not show up in a health snapshot), you may consider temporarily setting DIAGLEVEL to 4 and looking in the log for these log points.

Evaluation log points look like the following:

Listing 28. Evalutaion log points
2007-03-28-10.25.01.920756-240 I8320776A361       LEVEL: Event
PID     : 1155132              TID  : 1038        PROC : db2fmp
INSTANCE: swalkty              NODE : 000
EDUID   : 1038                 EDUNAME: db2acd
FUNCTION: DB2 UDB, Automatic Table Maintenance, db2HmonEvalReorg, probe:10
START   : Automatic reorg evaluation has started on database REORGDB

2007-03-28-10.26.57.462995-240 I8287958A375       LEVEL: Event
PID     : 1155132              TID  : 1036        PROC : db2fmp
INSTANCE: swalkty              NODE : 000
EDUID   : 1036                 EDUNAME: db2acd 
FUNCTION: DB2 UDB, Automatic Table Maintenance, db2HmonEvalReorg, probe:20
STOP    : Automatic reorg evaluation has finished successfully on database REORGDB

Problem determination for automatic reorganization

This section discusses some common problems you may encounter when you first get started using automatic reorganization.

Diagnosing problems with automatic reorganization

The most frequently asked question when people start using automatic reorganization is "Why is automatic reorganization not reorganizing table X". By understanding the previous sections (configuration, behavior, monitoring), it is fairly easy to answer this question.

The first thing to do is verify your configuration settings. There are three key things to check here:

  1. Run the GET DB CONFIG command. Verify that automatic reorganization is enabled. In particular, the configuration parameters AUTO_MAINT, AUTO_TBL_MAINT and AUTO_REORG should all have values of ON
  2. Launch the Configure automatic maintenance wizard from the DB2 Control Center. Verify that you have defined an offline maintenance window.
  3. Check the name and size criteria in the Configure automatic maintenance wizard. Ensure that the tables you want considered for reorganization will be matched by these criteria.

Once you have verified the configuration, you can begin to investigate the behavior. Automatic reorganization will evaluate for the first time 2 hours after database activation, and roughly every 2 hours after that. Run the following command to check when your database was activated:

Listing 29. Checking to see when your database was activated
GET SNAPSHOT FOR DB ON <db alias>

              Database Snapshot

Database name                              = SAMPLE
Database path                              = /home/swalkty/swalkty/NODE0000/SQL00001/
Input database alias                       = SAMPLE
Database status                            = Active
Catalog database partition number          = 0
Catalog network node name                  =
Operating system running at database server= AIX 64BIT
Location of the database                   = Local
First database connect timestamp       = 03/28/2007 16:41:59.082095
Last reset timestamp                       =
Last backup timestamp                      =

If the database has not been active for at least 2 hours, it is expected that automatic reorganization has not performed an evaluation.

If the database has been active for at least 2 hours, you should be able to use health snapshots to get a better understanding of what is going on. Issue the following command:

Listing 30. Snapshot for all tables
GET HEALTH SNAPSHOT FOR DB ON <db alias> WITH FULL COLLECTION

And search for the entry for the table that you are concerned about. You will see an entry like the following:

Listing 31. Look for table state
             Name                              = "DBUSER"."EMPLOYEE"
             State                             = Automated
             Evaluation timestamp              = 03/28/2007 11:57:16.000000

If the state is listed as "not yet evaluated", the table statistics are likely out of date. Run the following statement:

Listing 32. Checking for last statistics time
SELECT STATS_TIME FROM SYSCAT.TABLES WHERE TABNAME = 'table' AND TABSCHEMA = 'schema'

Automatic reorganization will only consider tables that have statistics. If STATS_TIME is NULL, the table will not be evaluated. In that case, issue a RUNSTATS command on the table and then wait for the next evaluation interval.

If the state in the snapshot output is listed as automated, reorganization has been scheduled for the table. The table will remain in automated state until the reorganization is performed. The earliest that will happen is the next offline maintenance window, but this is not guaranteed. If there are a number of tables requiring reorganization, it may take several occurrences of the maintenance window to complete them.

If the state is listed as normal, automatic reorganization does not think that reorganization is required on the table. You can verify this yourself by manually invoking the REORGCHK_TB_STATS and REORGCHK_IX_STATS stored procedures and looking at the output flags in the REORG column.

If REORGCHK indicates that the table needs reorganization, and the table is still reported as normal, look at the STATS_TIME for the table again. Statistics may have been recently updated, in which case you just need to wait for the next evaluation interval. For example, in a health snapshot, if you have the following:

Listing 33. Checking for timestamps
GET HEALTH SNAPSHOT FOR DB ON SAMPLE WITH FULL COLLECTION

             Database Health Snapshot

Snapshot timestamp                         = 03/28/2007 12:52:48.477502

Database name                              = SAMPLE
Database path                              = /home/swalkty/swalkty/NODE0000/SQL00001/
Input database alias                       = SAMPLE
Operating system running at database server= AIX 64BIT
Location of the database                   = Local
Database highest severity alert state      = Alarm

Health Indicators:

    Indicator Name                             = db.tb_reorg_req
       Value                                   = 0
    Evaluation timestamp                  = 03/28/2007 12:52:46.460665
       Alert state                             = Normal

          Collection:

             Name                              = "DBUSER"."CL_SCHED"
             State                             = Normal
        Evaluation timestamp              = 03/28/2007 11:57:16.000000

The key timestamps are in bold here. The first timestamp represents the last time that automatic reorganization evaluation took place. The second timestamp is the last time that evaluation occurred for a particular table. Once automatic reorganization has looked at a table, the table will not be looked at again until statistics are updated (which means that STATS_TIME is more recent than the last evaluation timestamp for the table).

In this example:

  • The next evaluation interval for automatic reorganization will be at 03/28/2007 12:52:46 + 2 hours (that is, 03/28/2007 14:52:46).
  • The last time the DBUSER.CL_SCHED table was examined was at 03/28/2007 11:57:16.

If the STATS_TIME for the table is earlier than 03/28/2007 11:57:16 (the last time the table was evaluated), the table will NOT be re-evaluated during the next evaluation interval, at time 03/28/2007 14:52:46.

However, if the STATS_TIME is more recent than 03/28/2007 11:57:16 (for example, suppose statistics for the table were updated at time 03/28/2007 12:58:00), then the table will be re-evaluated during the next evaluation interval, at time 03/28/2007 14:52:46 (that is, during this interval, the REORGCHK formulas will be re-applied).

Using this information, you can determine when a table was last evaluated, and predict when it should be looked again.

Misleading log points

If you are running DB2 v8.2 or have DIAGLEVEL set to 4, you will see evaluation log points for automatic reorganization even if the AUTO_REORG configuration parameter is disabled. In this case, automatic reorganization is NOT running. The evaluation portion of automatic reorganization is still performed in order to identify which tables require manual reorganization to report through the db.tb_reorg_req health indicator, but reorganizations will NOT be automated in this case.

Dealing with unexpected errors

Automatic reorganization may encounter errors from time to time due to user operations (for example, force all applications off the database). Not all errors in diagnostic logs are critical. If auto-reorganization fails for a table, it will try to reorganize the table again (that is, when the table is in an "automate failed" state, it will be checked again at the next evaluation interval and if reorganization is still required, a new reorganization will be scheduled). If a table remains in automate failed state for a number of intervals, this could indicate some underlying problem. However if a table is in automate-failed state for a single evaluation interval, consider the reason for the failure and wait to see if the problem corrects itself.

Note that automatic reorganization uses a weak connection to the database. Since automatic reorganization runs in the background, it is only able to connect to an active database (either explicitly activated, or implicitly activated by first connection). When a database is deactivated, any connections automatic reorganization has open to the database will be forced off. So it is not unusual to see log points written by automatic reorganization where an sqlcode of -952 (operation interrupted) is logged. Reorganizations that are forced will not leave the table in an inconsistent state.


Differences between DB2 V8.2 and DB2 9

Note: when migrating from DB2 v8.2 to DB2 9, consider updating your automatic reorganization maintenance policy to take advantage of these new configuration options.

The following configuration settings are new for DB2 9:

  • Size criteria: In DB2 v8.2, the only mechanism to control which tables are considered by automatic reorganization is the name criteria (that is, can only filter tables on name, schema, and comment. Tables cannot be omitted based on size).

  • Temporary table space option: In DB2 v8.2, whenever a table is reorganized, the working copy of the table is stored in the table spaces that contain the table. You must ensure that the table spaces have sufficient space to store the copy (or are defined as resizable) otherwise the reorganization operation will fail. There is no option in DB2 v8.2 to use a system temporary table space to store the working copy during reorganization.

  • Compression dictionary option. The row compression feature was introduced in DB2 9 and the corresponding automatic reorganization option, to keep/rebuild compression dictionaries as part of the reorganizations it performs, was added at the same time.

  • Index reorganization mode. In DB2 v8.2, all index reorganizations are performed offline (no write access allowed) in the offline maintenance window. In DB2 9, you can specify that index reorganizations should be performed online (allow both read and write access to index during reorganization) in the online maintenance window.

The second difference between automatic reorganization in DB2 v8.2 and DB2 9 is diagnostic logging. In DB2 9, evaluation log points are only written to the DB2 diagnostic log (db2diag.log) when the DIAGLEVEL is set to 4 (logging all diagnostic information). In DB2 v8.2, evaluation log points are written every evaluation interval, regardless of logging level (refer to the section on monitoring for an example of evaluation log points) . You should be aware that if using DB2 v8.2, this means that roughly every 2 hours (every time automatic reorganization checks if there are tables that need to be reorganized), 2 log points will be written. Over time, this can fill up the db2diag.log, so you may need to clean up the db2diag.log more frequently if enabling automatic reorganization prior to DB2 9.


Best practices for automatic reorganization

  1. Activate your database and keep the database active. Explicit activation via the ACTIVATE DATABASE command is recommended. Automatic reorganization evaluation occurs at regular intervals from db activation time and only occurs when a database is active. If the database comes up and down irregularly (which can occur with implicit activation from CONNECT statements), automatic reorganization will not occur as frequently. It is especially important that the database be kept active during the time period identified by the offline maintenance window so that automatic reorganization process is able to connect to the database and perform reorganizations. If the database is inactive during the offline window, reorganizations will not be performed.

  2. Keep table statistics are up to date. It is recommended that automatic statistics collection be used in combination with automatic reorganization to realize the full benefits of automatic reorganization. Automatic reorganization relies on statistics to determine whether or not a table needs to be reorganized. If statistics are not kept up to date, tables will not be reorganized.

  3. Ensure the health monitor is enabled and the db.tb_reorg_req health indicator is enabled. These are both enabled by default. The db.tb_reorg_req health indicator is the primary source of information about the progress of automatic reorganization. If it is not enabled, you will not be able to monitor automatic reorganization or detect if there are any problems. It is recommended that you check this health indicator fairly regularly to monitor that there are no problems with automatic reorganization. Consider using the health monitor's e-mail notification facility to receive e-mails whenever the health indicator enters an attention state.

  4. If you have large tables, consider using the size criteria of the maintenance policy to omit these tables from offline reorganization. Offline reorganization will make the table inaccessible while reorganization occurs. On large tables, reorganization may take a long time (and, as indicated in the section on monitoring, may exceed the maintenance window). Offline reorganizations for large tables should be planned with care and performed manually in order to minimize impact to users.

  5. When defining the offline maintenance window, keep in mind that the window is effectively used to determine start times for maintenance operations and that operations started during the window may run past the end of the window. Consider defining an offline maintenance window which is smaller than the actual amount of time that tables may remain offline in order to minimize the impact of reorganizations that run past the end of the window.

  6. If you perform manual reorganization on a table (issue the REORG command) while the automatic reorganization feature is enabled, it is important to update statistics for the table once the reorganization completes. Automatic reorganization decides whether or not a table requires reorganization by looking at table statistics. If the statistics are not updated following a manual reorganization, automatic reorganization will not be aware that table reorganization has taken place and may attempt to reorganize the table.

  7. If you configure automatic reorganization to perform online index reorganization, you should modify the default online maintenance window so at least a part of the window does not overlap with the offline maintenance window. This will ensure there is a period of time that will be used exclusively for offline maintenance operations.


Conclusion

Automatic reorganization is an effective means of managing the reorganizations on your tables and indexes. This paper has demonstrated how to enable and configure automatic reorganization, monitor automatic reorganization progress and detect errors. It has also provided an overview of the automatic reorganization processing to help you understand how and when automatic reorganization determines that reorganization of a table/index is required, and what type of reorganization will be performed. Finally, a set of best practices were presented.


Acknowledgment

We would like to acknowledge and thank Jessica Escott for reviewing this article.

Resources

Learn

Get products and technologies

  • Download a free trial version of DB2 Enterprise 9.
  • 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 Edtion and provides a solid base to build and deploy applications.
  • Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2, Lotus®, Rational®, Tivoli®, and WebSphere®.

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=239993
ArticleTitle=Automatic table maintenance in DB2, Part 2: Automatic table and index reorganization in DB2 for Linux, UNIX, and Windows
publish-date=07122007