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

Use the command line interface for automatic maintenance policies

Configure DB2 automatic maintenance operations from either the command line or programmatically, using new stored procedures introduced in IBM® DB2® 9.5 for Linux®, UNIX®, and Windows®. This article discusses different configuration options and provides examples.

Kayalvizhi Ganesan (kaganesa@in.ibm.com), Software Developer, IBM

Kayalvizhi Ganesan photoKayalvizhi Ganesan is a Software Developer on DB2 Tools Team at India Software Lab. She is presently working on Health Monitor Tool. Kayal holds a Bachelor of Engineering degree in Computer Science from Regional Engineering Collage, Trichy, India.



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

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.



10 January 2007

Also available in Chinese

Introduction

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

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

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

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


Automatic maintenance control overview

DB2 9.5 supports three types of automatic maintenance: automatic backup, automatic table statistics collection, and automatic table reorganization. The behavior of automatic maintenance operations are controlled through three mechanisms:

  1. Database configuration parameters
  2. Behavior
  3. Maintenance windows

Database configuration parameters

Database configuration parameters are used to enable or disable a particular feature. The configuration parameters are expressed using the following hierarchy:

Listing 1. Configuration parameters hierarchy
 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

For example, if you set AUTO_MAINT to a value of OFF, all other automatic maintenance parameters are considered to have an effective value of OFF, regardless of whether or not they are set to ON.

To enable a given automatic maintenance feature, the corresponding configuration parameter and all higher level switches must be set to ON. For example, to enable automatic table reorganization, the AUTO_MAINT, AUTO_TBL_MAINT, and AUTO_REORG configuration parameters must all have a value of ON.

Behavior

An automatic maintenance policy controls the behavior of a given automatic maintenance operation. Behavior includes things such as which objects will be considered for automatic maintenance, or what types of options to use when applying a given maintenance operation.

Maintenance windows

Maintenance windows specify "when" a given automatic operation is allowed to be performed. A database may have at most two maintenance windows: an online window and an offline window. The online window is used for operations during which the target of the maintenance remains accessible to users, while the offline window is used for operations during which the target of the maintenance may be made inaccessible (that is taken offline).

The following table summarizes the control mechanisms relevant to each automatic maintenance operation:

Table 1. Control
Automatic maintenance typeDatabase configuration parameterMaintenance windowAutomatic maintenance policy
Automatic table runstatsAUTO_RUNSTATS (enabled by default on new databases)Online windowPolicy controls which tables are considered for automatic statistics collection.
Automatic table reorganizationAUTO_REORG (disabled by default on new databases)Offline window for table reorganization; online window or offline window for index reorganizationPolicy controls which tables are considered for automatic reorganization and controls the reorganization options (for example,index reorganizations online).
Automatic backupAUTO_DB_BACKUP (disabled by default on new databases)Online window for online automatic backups, and offline window for offline automatic backupsPolicy controls which backuptype (online or offline), location of backup, and backup criteria (that is "when" should automatic backup consider taking a backup).

This article focuses on the specification of the maintenance policy (which controls the behavior) and the maintenance window (which defines when the operation may take place). The configuration parameters can be updated or retrieved using the existing GET DATABASE CONFIGURATION and UPDATE DATABASE CONFIGURATION commands (or through SQL using the ADMIN_CMD stored procedure). Refer to the DB2 documentation for further details on the configuration parameters used to enabled or disable automatic maintenance.


Stored procedures for retrieving and updating automatic maintenance policies

DB2 9.5 provides the following stored procedures for updating and retrieving automatic maintenance policies and the maintenance windows:

  • AUTOMAINT_SET_POLICY
  • AUTOMAINT_SET_POLICYFILE
  • AUTOMAINT_GET_POLICY
  • AUTOMAINT_GET_POLICYFILE

Both the policies and maintenance windows are specified as simple XML documents. The "FILE" version of the procedures either accept an XML file as input or return a file as output. The other versions of the procedures accept the XML input as a BLOB and return the output document as a BLOB.

For example, when the database administrator wants to configure the automatic backup policy for several databases, he can define the configuration in an XML file, then connect to each database and call AUTOMAINT_SET_POLICYFILE to deploy the policy on each databases.

Any user who has EXECUTE privilege on these procedures can read or update automatic maintenance policies. By default, only the database administrator has EXECUTE privilege on these procedures.


Updating automatic maintenance policies

Automatic maintenance policies are updated using the AUTOMAINT_SET_POLICY and AUTOMAINT_SET_POLICYFILE procedures. Both procedures take as input the type of policy being updated, and an XML document (passed either as a file or a BLOB). The procedures verify that the input XML document is well formed and update the corresponding policy elements in the database.

Note: Policy values are stored internally in a table. In order for the policy changes to take effect, you must issue a COMMIT statement after invoking these procedures. The procedures will not perform a COMMIT automatically. If you issue a ROLLBACK after invoking these procedures, policy settings will not be updated.

AUTOMAINT_SET_POLICYFILE

The AUTOMAINT_SET_POLICYFILE procedure takes two parameters:

>>-AUTOMAINT_SET_POLICYFILE(--policy_type--,--policy_file_name--)------------><

policy_type: Type of automatic maintenance policy to be updated. It can be one of the following:

  • AUTO_BACKUP
  • AUTO_RUNSTATS
  • AUTO_REORG
  • MAINTENANCE_WINDOW

Note: The maintenance window is not an automatic maintenance policy, but it is configurable using the same interface.

policy_file_name: Name of the XML file containing the policy settings. This file must be present in the tmp subdirectory of the DB2 instance directory and must be readable by the DB2 fenced user id. (The SQL procedures to update automatic maintenance policies are fenced.)

This file name can include a directory name that is present under the tmp subdirectory of the DB2instance directory. For example, if the user is creating policy configuration settings of automatic backup for the database mydb, she can create a directory called mydb under the tmp subdirectory of the DB2instancnce and copy the XML file under the mydb subdirectory.

Therefore, the policy_file_name parameter will be mydb/backup.xml.

Note: You must specify the directory using the correct path separator.

On UNIX the instance directory is defined as $HOME/sqllib. With the backup configuration file named mydb/backup.xml, the file name will be $HOME/sqllib/tmp/mydb/backup.xml.

On Windows, the instance directory name can be determined from the values of the DB2INSTPROF registry variable and the DB2INSTANCE environment variable. For a policy file named automaint\policy.xml, if db2set shows DB2INSTPROF=C:\DB2PROF and %DB2INSTANCE%=db2, then the file name will be C:\DB2PROF\db2\tmp\mydb\backup.xml.

For example:

db2 "call sysproc.automaint_set_policyfile('AUTO_BACKUP','mydb/backup.xml');"
db2 commit

AUTOMAINT_SET_POLICY

The AUTOMAINT_SET_POLICY procedure takes two parameters:

>>-AUTOMAINT_SET_POLICY---(--policy_type--, --policy--)------------><

policy_type: Type of automatic maintenance policy to be updated. It can be one of the following:

  • AUTO_BACKUP
  • AUTO_RUNSTATS
  • AUTO_REORG
  • MAINTENANCE_WINDOW

Note: The maintenance window is not an automatic maintenance policy, but it is configurable using the same interface.

policy: BLOB of maximum 2M that specifies the automatic maintenance policy in XML format.

For example:

Listing 2. Example of automaint_set_policy procedure
CALL SYSPROC.AUTOMAINT_SET_POLICY ( 'AUTO_RUNSTATS', BLOB('<?xml 
version="1.0" encoding="UTF-8"?> <DB2AutoRunstatsPolicy  
xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config"> 
<RunstatsTableScope><FilterCondition/></RunstatsTableScope> 
</DB2AutoRunstatsPolicy> ') ) ;

COMMIT ;

Either procedure can be used to update automatic maintenance policies programmatically. For example, the following code snippet shows how automatic maintenance policies can be specified using a Java™ program:

Listing 3. Specify automatic maintenance policies using a Java program
{ . . .
  . . .

        try
        {
          File f = new File(policyFile);
          FileInputStream xmlfile = new FileInputStream(f);
          byte[] bytes = new byte[xmlfile.available()];
          xmlfile.read(bytes);
          xmlfile.close();
          Blob xmldata = com.ibm.db2.jcc.t2zos.DB2LobFactory.createBlob(bytes);

          // Call the SYSPROC.AUTOMAINT_SET_POLICY Stored procedure
          setPolicy("AUTO_BACKUP",xmldata);
        }
 }

public void setPolicy(String action, Blob xmldoc)
{
  Connection con = getConnection();

  //Call the SYSPROC.AUTOMAINT_SET_POLICY
  try
  {
    String procName = "SYSPROC.AUTOMAINT_SET_POLICY";
    String sql = "CALL " + procName + "(?, ?)";
    CallableStatement callStmt = con.prepareCall(sql);

    // set input parameters
    callStmt.setString(1, action);
    callStmt.setBlob(2, xmldoc);

    callStmt.execute();
    con.commit();
    con.close();
   }
   catch (Exception e)
   {
     String errorMessage=e.getMessage();
     System.out.println(errorMessage.substring(
                  errorMessage.indexOf("SQL")));
   }

}

Retrieving automatic maintenance policies

Automatic maintenance policies are retrieved using the AUTOMAINT_GET_POLICY and AUTOMAINT_GET_POLICYFILE procedures. Both procedures take the type of policy being retrieved as input and return an XML document (either returned as a BLOB to the caller or written to a file) containing the policy information.

AUTOMAINT_GET_POLICYFILE

The AUTOMAINT_GET_POLICYFILE procedure takes two parameters:

>>-AUTOMAINT_GET_POLICYFILE--(--policy_type--,--policy_file_name--)------------><

policy_type: Type of automatic maintenance policy to be retrieved. It can be one of the following:

  • AUTO_BACKUP
  • AUTO_RUNSTATS
  • AUTO_REORG
  • MAINTENANCE_WINDOW

policy_file_name: Specifies the target file where the policy document will be written. The file will be created in the tmp subdirectory of the DB2 instance directory. For example:

db2 "call sysproc.automaint_get_policyfile( 'AUTO_BACKUP','AutoBackup.xml');"

This will create an XML file named AutoBackup.xml in the tmp subdirectory under the DB2 instance directory.

AUTOMAINT_GET_POLICY

The AUTOMAINT_GET_POLICY takes two parameters:

>>-AUTOMAINT_GET_POLICY--(--policy_type--,--policy--)----------><

policy_type: Type of automatic maintenance policy to be retrieved. It can be one of the following:

  • AUTO_BACKUP
  • AUTO_RUNSTATS
  • AUTO_REORG
  • MAINTENANCE_WINDOW

policy: An output argument of type BLOB of maximum 2M that specifies the automatic maintenance settings for the given policy type, in XML format.

Listing 4 provides an example of a call to the AUTOMAINT_GET_POLICY procedure from within Java code. A BLOB variable is declared for the procedure output parameter. The procedure is called with automated backup as the type of automatic maintenance policy and BLOB variable as the output parameter in which the procedure will return the requested policy (identified by the action parameter) for the currently connected database.

Listing 4. Call to the AUTOMAINT_GET_POLICY procedure from within Java code
public Blob getPolicy(String action)
{
  Connection con = getConnection();

  Blob xmldata = null;
  //Call the SYSPROC.AUTOMAINT_GET_POLICY
  try
  {
    String procName = "SYSPROC.AUTOMAINT_GET_POLICY";
    String sql = "CALL " + procName + "(?, ?)";
    CallableStatement callStmt = con.prepareCall(sql);

    // set input parameters
    callStmt.setString(1, action);
                    callStmt.registerOutParameter(2,Types.BLOB);

    callStmt.setBlob(2,xmldata);
    callStmt.execute();
    xmldata = callStmt.getBlob(2);
   }
   catch (Exception e)
   {
     String errorMessage=e.getMessage();
     System.out.println(errorMessage.substring(
                   errorMessage.indexOf("SQL")));
   }
   con.close();
   return xmldata;
}

Listing 5 provides another example of a call to the AUTOMAINT_GET_POLICY procedure using embedded SQL in C source code:

Listing 5. Call to the AUTOMAINT_GET_POLICY procedure using embedded SQL in C source code
EXEC SQL BEGIN DECLARE SECTION;
SQL TYPE IS BLOB(2M) backupPolicy;
EXEC SQL END DECLARE SECTION;

EXEC SQL CALL AUTOMAINT_GET_POLICY
         (
         'AUTO_BACKUP', :backupPolicy
         );

Automatic maintenance policies

This section examines each automatic maintenance policy. The automatic maintenance policies are all XML documents. The schemas for these documents can be found in the sqllib\misc directory:

Table 2. Schemas for automatic maintenance policies XML documents
SchemaDescription
DB2MaintenanceWindowPolicy.xsdSchema for document describing the online (and optional offline) maintenance windows
DB2AutoBackupPolicy.xsdSchema for the document used to configure the behavior of automatic backup
DB2AutoReorgPolicy.xsdSchema for the document used to configure behavior of automatic table reorganization
DB2AutoRunstatsPolicy.xsdSchema for the document used to configure the behavior of automatic table statistics collection

You can use these schemas when testing your deployment process to manually verify that the XML documents you are providing as input to the DB2 procedures are well formed.

Maintenance window policy

The maintenance window policy is used to specify a period of time during which DB2 may schedule and run automatic maintenance tasks.

A DB2 database can have at most two maintenance windows specified: an online window (for maintenance operations that leave the object of the maintenance accessible) and an offline window (during which time the object undergoing maintenance is inaccessible to users). The offline window is optional.

Maintenance windows typically consist of a start time and duration. Also, the user can specify the frequency of the windows by specifying the DaysOfWeek, DaysOfMonth, and MonthsOfYear on which the windows should occur.

Refer to the sample XML file DB2MaintenanceWindowPolicySample.xml under the sqllib\samples\automaintcfg directory for full details about each element and attribute that can be specified when configuring a maintenance window.

Note: It is generally recommended that if both an online and offline window are provided, they do not overlap. Typically 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.

Example 1:

Online maintenance window that is 24x7 (that is all hours of all days of the week; online maintenance tasks can be scheduled any time).

Listing 6. Example of maintenance window policy for 24 * 7 settings ( Default )
<?xml version="1.0" encoding="UTF-8"?>
<DB2MaintenanceWindows
 xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config" >

 <!-- Online Maintenance Window  -->
 <OnlineWindow Occurrence="During" startTime="00:00:00" duration="24">
  <DaysOfWeek>All</DaysOfWeek>
  <DaysOfMonth>All</DaysOfMonth>
  <MonthsOfYear>All</MonthsOfYear>
 </OnlineWindow>
</DB2MaintenanceWindows>

Example 2:

Online window is 24 hours a day, on each weekday, and the offline window is from 6:00 PM to 6:00 AM every Saturday and Sunday (that is night time on weekends).

Listing 7. Example of a customized maintenance window policy
<?xml version="1.0" encoding="UTF-8"?>
<DB2MaintenanceWindows
xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config" >

 <!-- Online Maintenance Window  -->
 <OnlineWindow Occurrence="During" startTime="00:00:00" duration="24" >
     <DaysOfWeek>Mon Tue Wed Thu Fri</DaysOfWeek>
    <DaysOfMonth>All</DaysOfMonth>
    <MonthsOfYear>All</MonthsOfYear>
  </OnlineWindow>

 <!-- Offline Maintenance Window -->
   <OfflineWindow Occurrence="During" startTime="18:00:00" duration="12" >
     <DaysOfWeek>Sat Sun</DaysOfWeek>
     <DaysOfMonth>All</DaysOfMonth>
     <MonthsOfYear>All</MonthsOfYear>
  </OfflineWindow>

</DB2MaintenanceWindows>

Automatic backup policy

The automatic backup policy is used to control the behavior of automatic backup. The following elements are configurable:

  • Backup criteria: Specifies when automatic backup should consider scheduling a backup. A backup is performed if any of the criteria specified are true. Criteria include number of full backups (for example, take backup if you have less than three full backups), time since last backup (for example, take backup if it has been more than two weeks since the last backup), log space consumed since last backup (for example if more than 100K of log space has been used, take a backup). This last criterion can be used to try and estimate the data change (that is, if data in the database has not changed since last backup, there is no need to take a new backup).
  • Backup mode: Specifies whether online or offline backups should be taken. If the online mode is specified, archive logging must be enabled.
  • Backup target and target options: Specifies the location where the backup will be placed and any options for the backup target.

Note:A single policy configuration is shared for all database partitions on a system that is DPF (data partitioning feature) enabled. Therefore, any pathname specified for the backup location must be accessible on all partitions.

Refer to the sample XML file DB2AutoBackupPolicySample.xml under the sqllib\samples\automaintcfg directory for full details about each element and attribute that can be specified when configuring automatic backup.

Example 1:

Perform online backups every 2 weeks (2 weeks = 2 (weeks) * 7 (days) * 24 (hours) = 336 hours) OR if there is not at least 1 backup OR if the log space consumed is greater than 1000 * 4KB. Backup images should be stored in the /TMP/mydb1/backupimages directory.

Listing 8. Example of customized automatic backup policy
<?xml version="1.0" encoding="UTF-8"?>
<DB2AutoBackupPolicy
xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config" >
 <BackupOptions mode="Online" >
  <BackupTarget>
   <DiskBackupTarget>
    <PathName>/TMP/mydb1/backupimages/</PathName>
   </DiskBackupTarget>
  </BackupTarget>
 </BackupOptions>
 <BackupCriteria
    numberOfFullBackups="1"
    timeSinceLastBackup="336"
    logSpaceConsumedSinceLastBackup="1000" />

</DB2AutoBackupPolicy>

Automatic table reorganization policy

The automatic table reorganization policy is used to control the behavior of automatic table reorganization. The following elements are configurable:

  1. Table scope: Identifies which tables are considered for automatic reorganization. Specification is done by completing a WHERE clause that is applied to a SELECT from SYSCAT.TABLES. For example, TABSCHEMA NOT LIKE 'EMP%'.
  2. Size criteria: Optional element used to exclude tables on the basis of size. Tables whose estimated size is greater than the size criteria are not considered by automatic table reorganization. Used to omit large tables for which reorganization would be very time consuming and expensive.
  3. Reorganization options: Elements that configure the behavior of the reorganization operations performed by automatic table reorganization. Options include whether or not to perform index reorganizations online, whether or not to rebuild compression dictionaries when tables are reorganized, and whether or not to use a system temporary table space during reorganization.

Refer to the sample XML file DB2AutoReorgPolicySample.xml under the sqllib\samples\automaintcfg directory for full details about each element and attribute that can be specified when configuring automatic reorganization.

Example 1:

Consider all tables for automatic table reorganization that are smaller than 52KB, perform all index reorganizations online, and use a system temporary table space when doing reorganization.

Listing 9. Automatic reorganization policy (Default)
<?xml version="1.0" encoding="UTF-8"?>
<DB2AutoReorgPolicy
xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config">

<ReorgOptions indexReorgMode="Online"  useSystemTempTableSpace="true" />

<ReorgTableScope maxOfflineReorgTableSize="52">
  <FilterClause />
 </ReorgTableScope>

</DB2AutoReorgPolicy>

Example 2:

Only consider user tables for automatic table reorganization. Perform index reorganizations online and rebuild compression dictionaries when table reorganization takes place.

Listing 10. Example of customized automatic reorganization policy
<?xml version="1.0" encoding="UTF-8"?>
<DB2AutoReorgPolicy
xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config">

<ReorgOptions dictionaryOption="Rebuild" indexReorgMode="Online"  />

<ReorgTableScope>
  <FilterClause>
      TABSCHEMA NOT LIKE 'SYS%'
   </FilterClause>
 </ReorgTableScope>

</DB2AutoReorgPolicy>

Automatic table statistics collection policy

The automatic table statistics collection policy is used to control the behavior of automatic table statistics collection. The following elements are configurable:

  1. Table scope: Identifies which tables are considered for automatic statistics collection. Specification is done by completing a WHERE clause that is applied to a SELECT from SYSCAT.TABLES. For example, TABSCHEMA NOT LIKE 'EMP%'.

Refer to the sample XML file DB2AutoRunstatsPolicySample.xml under the sqllib\samples\automaintcfg directory for full details about each element and attribute that can be specified when configuring automatic statistics collection.

Example 1:

Consider all tables for automatic statistics collection.

Listing 11. Automatic statistic collection policy (Default)
<?xml version="1.0" encoding="UTF-8"?>
<DB2AutoRunstatsPolicy
  xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config">
  <RunstatsTableScope>
     <FilterCondition/>
  </RunstatsTableScope>
</DB2AutoRunstatsPolicy>

Example 2:

Only consider user tables for automatic statistics collection.

Listing 12. Example of customized automatic statistic collection policy
<?xml version="1.0" encoding="UTF-8"?>
<DB2AutoRunstatsPolicy
   xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config">
   <RunstatsTableScope>
     <FilterCondition>
        TABSCHEMA NOT LIKE 'SYS%'
     </FilterCondition>
   </RunstatsTableScope>
</DB2AutoRunstatsPolicy>

Additional information

Common error messages

The stored procedures return 0 (zero) when they run successfully and SQL error codes if it finds any issues with the parameters or policy file content. Common errors include SQL0171, SQL01436.

When the procedures are called with wrong input parameter types, this error is returned:

SQL0171N
The data type, length or value or argument n of routine name is incorrect.

For example, when the automaint_set_policyfile procedure is called with an invalid policy type value, it gives the following error:

SQL0171N 
The data type, length or value of argument "1" of routine "AUTOMAINT_SET_POLICY" is
incorrect. SQLSTATE=42815

The following error is returned when the file specified as input to the automaint_set_policyfile procedure is not present in the sqllib/tmp/ directory:

SQL1436N
Automated maintenance policy configuration file named file-name could not be opened.

For example, if the input XML file is named mybackup.xml, and the caller mistypes the filename and passes mbyackup.xml as input, the error below will be returned;

SQL1436N 
Automated maintenance policy configuration file named "mybackup1.xml"
could not be opened. SQLSTATE=5U0ZZ

Confirming policy modification

After running the AUTOMAINT_SET_POLICY or AUTOMAINT_SET_POLICYFILE procedures for updating an automatic maintenance policy, you can confirm the changes by looking at the DB2 admin notification log. On UNIX, the DB2 admin notification log is a file named <instance>.nfy under the sqllib/db2dump directory. On Windows, DB2 admin notification messages are written to the system event log (and can be viewed using the Windows Event Viewer).

Whenever the policy gets updated, the admin message ADM105151 is written to the DB2 admin notification log. This message contains both the original and the modified policy configuration. Use this message to verify configuration changes and to determine the original configuration (for example, in the event that the new policy configuration does not perform as expected). Note: This message is written when the stored procedure is invoked, not at COMMIT time. If you rollback the transaction before committing the changes made by the procedure, this logpoint should be ignored.

ADM10515I
The automatic maintenance policy Policy-Name has been updated in
database Database-Name. The policy options have been updated from
Original-Policy-Options-List to New-Policy-Options-List

For example:

Listing 14. Admin notification log message after applying automatic backup policy
ADM10515I

ADM10515I The automatic maintenance policy "AUTO_BACKUP" has been updated in
database "TEMP1". The policy options have been updated from "Backup criteria:
Number of full backups < 1 or Elapsed time since last backup >
168 hours or <Log space consumed since last backup > 1638400 MB  Backup
mode: DEFAULT Backup policy decision target:  Backup Type: DISK, Pathname:  "
to "Backup criteria: Number of full backups < 1 or  Elapsed time since last
backup > 336 hours or Log space consumed since last backup > 256000 MB  Backup
mode: ONLINE Backup policy decision target: Backup Type: DISK, Pathname:
/home/kaganesa/mydb1/backupimages/"

Conclusion

This article has introduced the new stored procedures for configuring automatic maintenance policies using many examples, and it has demonstrated how they can be used as a simple alternative to the DB2 Control Center for configuring automatic maintenance operations.


Acknowledgment

We would like to acknowledge and thank Randy Horman, Serge Boivin, and Agatha Colangelo for reviewing this article.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.

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=281403
ArticleTitle=Configure automatic maintenance in DB2 for Linux, UNIX, and Windows with SQL interface
publish-date=01102007