Automate DB2 9.7 database maintenance in an embedded database environment

Hassle-free DB2 9.7 database administration with automatic-maintenance toolkit

Within an embedded database environment, it is important that you, as a database administrator, automate as many maintenance tasks as possible so that you can run the database with minimal intervention. IBM® DB2® for Linux®, UNIX®, and Windows® provides advanced automation features for configuring, tuning, and managing databases. These automation features allow you to spend less time managing routine tasks, and more time focusing on strategic issues that help your businesses gain and maintain a competitive advantage. This tutorial shows you how to automate routine maintenance tasks for DB2 on Linux or UNIX.

Sreeni Paidi, Enablement Consultant, IBM

Sreeni Paidi photoSreeni Paidi is an Advisory Software Engineer and Enablement Consultant with IBM's Information Management Technology Enablement organization. Sreeni has over 13 years of experience working with DB2, Informix, Oracle, and PostgreSQL database servers as a Database Programmer, DBA, and an Integration Architect. He joined IBM as part of Trigo Technologies acquisition in 2004. Sreeni is part of a worldwide team that provides technical skills and consulting services to help business partners and customers accelerate the integration of IBM database software with their applications.



Angela Carlson, Enablement Consultant, IBM

Angela Carlson photoAngela Carlson is a Software Engineer and Enablement Consultant with IBM's Information Management Technology Enablement organization. Angela has four years of technical experience working in the IT industry, focusing on relational database technology and application development. She has experience in developing relational database applications with PHP, Perl, and Java. In Angela's current position, she works closely with IBM Business Partners and their database needs to enable their solutions with DB2. She also researches and develops competitive material on DB2 and MySQL. Angela holds a Bachelor's Degree in Software Engineering Science from the University of Western Ontario.



06 May 2010

Also available in

Before you start

Before you start, read about what is covered in this tutorial, prerequisites, and system requirements.

About this tutorial

The purpose of this tutorial is to show you how to enable some of the autonomic features of IBM DB2 9.7 for Linux, UNIX, and Windows, and how to automate some routine database maintenance tasks. These features and techniques are particularly useful when you are embedding a DB2 database server instance in a virtual environment.

The tutorial covers the following topics:

  • Enabling automatic maintenance
    • Automatic database backup and restore
    • Automatic statistics collection
    • Automatic reorganization
  • Enabling silent updates to the database server
  • Enabling self-tuning memory manager
  • Enabling automatic storage management

Prerequisites

This tutorial is written for IBM DB2 database administrators, application architects, and developers. You should have a general familiarity with installing and configuring DB2, and using a UNIX or Linux command-line shell.

System requirements

To run the examples in this tutorial, you need a Linux or UNIX system with DB2 9.7 installed. You must have root access to the operating system and instance owner access to DB2.

Unpacking the toolkit

The tutorial includes a toolkit that consists of scripts and policy files you can use to enable and configure the features described in the tutorial. These scripts and policy files are packaged in a file named embed-bundle.zip that you can obtain from the Download section of this tutorial.

Copy the embed-bundle.zip file to the target computer that is running DB2, login to the computer as the DB2 instance owner (for example, db2inst1), and use the commands shown in Listing 1 to unpack the toolkit.

Listing 1. Commands to unpack the embed-bundle.zip toolkit
mv embed-bundle.zip /home/db2inst1
unzip /home/db2inst1/embed-bundle.zip
chmod +x /home/db2inst1/embed-bundle/*.sh

Note: The toolkit is developed specifically for DB2 9.7 running on either Linux or UNIX. However, if you want to use it for DB2 9.7 running on Windows, you should be able to do so with only minor modifications.

One of the files in the toolkit is a sample DB2 script named create_mydb.db2. This script creates a database and modifies the configuration of the database to enable the autonomic features discussed in this tutorial.


Automating maintenance

The DB2 database manager provides automatic maintenance capabilities for performing database backups, keeping statistics current, and reorganizing tables and indexes as necessary. Performing maintenance activities on your databases is essential for ensuring that they are optimized for performance and recoverability.

Determining whether and when to run maintenance activities can be time consuming. However, automatic maintenance helps to relieve you of some of this burden. You can manage the enablement of the automatic maintenance features simply and flexibly by using the automatic maintenance database configuration parameters. Using the Configure Automatic Maintenance wizard, you can specify your maintenance objectives. Database manager then uses these objectives to determine which maintenance activities need to be performed, and runs only the required ones during the next available maintenance window (a time period that you define).

Automatic database backup

DB2 database manger provides automatic maintenance capabilities for performing routine backup activities that would otherwise require the time and attention of a database administrator. The database manager lets you define an online/offline maintenance schedule and backup criteria. DB2 then automatically takes care of performing the backup as needed. Backups can be used for restoring the database after a system crash or hardware update. Backups are also useful for replicating a test or development environment. Automatic database backups help to ensure that your database is backed up properly and regularly.

Enabling database for automatic backups

This section describes how to configure automatic backup for your database. Automatic backup is disabled by default on new databases. To enable automatic backup on a database, you also need to enable automatic maintenance. Use the commands shown in Listing 2 to update your database configuration to enable both automatic maintenance and automatic backup.

Listing 2. Commands to enable automatic maintenance and automatic backup
UPDATE DB CFG FOR <dbname> USING AUTO_TBL_MAINT ON;
UPDATE DB CFG FOR <dbname> USING AUTO_DB_BACKUP ON;

Configuring maintenance window policy

The configuration for each automatic maintenance task and the maintenance window can be contained in a simple XML document that is referred to as an automatic maintenance policy. In the embed-bundle.zip toolkit, the file named DB2MaintenanceWindowPolicy.xml (shown in Listing 3) defines an online maintenance window for Monday through Friday and an offload maintenance window for Saturday and Sunday evenings.

Listing 3. DB2MaintenanceWindowPolicy.xml file contents
<?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>

You can use the sysproc.automaint_set_policyfile stored procedure to update policies. Listing 4 shows an example of using the stored procedure to update the maintenance window policy with the parameters specified in the DB2MaintenanceWindowPolicy.xml file. This same command is used in the backupsetup.sh script included with the toolkit. If you follow the instructions below for using the toolkit script, you do not have to run this command manually.

Listing 4. Command to set the maintenance window policy
db2 "call sysproc.automaint_set_policyfile 
  ('MAINTENANCE_WINDOW', ‘DB2MaintenanceWindowPolicy.xml')"

Configuring automatic backup policy

The automatic backup policy can also be contained in an XML document that controls which type of backup (online or offline) is performed, the location of the backup, and the backup criteria (when should automatic backup consider taking a backup). In the toolkit, the file named DB2AutoBackupPolicy.xml (shown in Listing 5) contains an automatic backup policy.

Listing 5. DB2AutoBackupPolicy.xml file contents
<?xml version="1.0" encoding="UTF-8"?>
<DB2AutoBackupPolicy
 xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config" >
 <BackupOptions mode="Online" >
  <BackupTarget>
   <DiskBackupTarget>
    <PathName>/home/db2inst1/dbname/backupimages/</PathName>
   </DiskBackupTarget>
  </BackupTarget>
 </BackupOptions>
 <BackupCriteria
    numberOfFullBackups="1"
    timeSinceLastBackup="168"
    logSpaceConsumedSinceLastBackup="1000" />
</DB2AutoBackupPolicy>

Listing 6 shows an example of using the sysproc.automaint_set_policyfile stored procedure to set the automatic backup policy with the parameters specified in the DB2AutoBackupPolicy.xml file. This same command is used in the backupsetup.sh script included with the toolkit. If you follow the instructions below for using the toolkit script, you do not have to run this command manually.

Listing 6. Command to set the automatic backup policy
db2 "call sysproc.automaint_set_policyfile 
  ('AUTO_BACKUP', 'DB2AutoBackupPolicy.xml')"

Setup instructions

Follow these steps to setup automatic backup using the embed-bundle.zip toolkit files:

  1. Make sure that DB2 is running.
  2. Login to the target computer as the DB2 instance owner (for example, db2inst1).
  3. Run the backupsetup.sh script from the /home/db2inst1/embed-bundle directory as shown in Listing 7.
    Listing 7. How to use the backupsetup.sh script
    Syntax:
    backupsetup.sh <dbname>
    
    Example:
    ./backupsetup.sh mydb

Database restore

DB2 provides the restore utility for you to use to rebuild a database or table space after a problem. You can replace a database with a different image or restore the backup copy to a new database. The RESTORE DATABASE command rebuilds the database data or table space to the state that it was in when the backup copy was made. Listing 8 shows how to use the command to restore to the latest backup, and Listing 9 shows how to restore to a backup image made at a specific time.

Listing 8. Restoring from the latest backup
Syntax:
db2 restore db <dbname> from <bakup-image-location> replace existing

Example:
db2 restore db mydb from /data/mydb/backupimages replace existing
Listing 9. Restoring from a specific backup image taken at a given timestamp
Syntax:
db2 restore db <dbname> from  <bakup-image-location> 
TAKEN AT <time-stamp> replace existing

Example:
db2 restore db secure from /data/mydb/backupimages/ 
TAKEN AT 20091228092339 replace existing

Automatic statistics collection

Having accurate and complete database statistics is critical to efficient data access and optimal workload performance. DB2 provides an automatic statistics collection feature with its automated table maintenance functionality that you can use to update and maintain relevant database statistics.

Enabling the database for automatic statistics collection

To enable automatic statistics collection, you must configure your database for automatic maintenance by setting the auto_maint and the auto_tbl_maint database configuration parameters to ON. You can then set options for defining when and how you want to collect statistics as follows:

  • To enable background statistics collection, set the auto_runstats database configuration parameter to ON.
  • To enable real-time statistics collection, set both the auto_stmt_stats and auto_runstats database configuration parameters to ON.
  • To enable automatic statistics profile generation, set both the auto_stats_prof and auto_prof_upd database configuration parameters to ON. If the auto_runstats database configuration parameter is also set to ON, statistics will be collected automatically using the generated profiles.

Listing 10 shows sample commands for setting the database configuration parameters to enable automatic statistic collection.

Listing 10. Commands to update database configuration parameters to enable automatic statistics collection
UPDATE DB CFG FOR <DBNAME> USING AUTO_RUNSTATS ON;
UPDATE DB CFG FOR <DBNAME> USING AUTO_STATS_PROF ON;
UPDATE DB CFG FOR <DBNAME> USING AUTO_PROF_UPD ON;

Configuring automatic RUNSTATS policy

You can use an XML document to specify which tables to include and which tables to exclude from the automatic statistics collection. In the toolkit, the file named DB2AutoRunstatsPolicy.xml contains a sample file for defining filter conditions. As shown in Figure 11, the filter condition in DB2AutoRunstatsPolicy.xml specifies that statistics should be collected for all tables, including system tables

Listing 11. Filter condition in the DB2AutoRunstatsPolicy.xml file
<RunstatsTableScope>
  <FilterCondition/>
</RunstatsTableScope>

Listing 12 shows an example of using the sysproc.automaint_set_policyfile stored procedure to set the automatic RUNSTATS policy with the parameters specified in the DB2AutoRunstatsPolicy.xml file. This same command is used in the runstatssetup.sh script included with the toolkit. If you follow the instructions below for using the toolkit script, you do not have to run this command manually.

Listing 12. Command to set the automatic RUNSTATS policy
db2 "call sysproc.automaint_set_policyfile('AUTO_RUNSTATS', 'DB2AutoRunstatsPolicy.xml')"

Setup instructions

Follow these steps to setup automatic RUNSTATS using the embed-bundle.zip toolkit files:

  1. Make sure that DB2 is running.
  2. Login to the target computer as the DB2 instance owner (for example, db2inst1).
  3. Run the runstatssetup.sh script from the /home/db2inst1/embed-bundle directory as shown in Listing 13.
    Listing 13. How to use the runstatssetup.sh script
    Syntax:
    runstatssetup.sh <dbname>
    
    Example:
    ./runstatssetup.sh mydb

Automatic table and index reorganization

Having well-organized table and index data is critical to efficient data access and optimal workload performance. After many insert, update, and delete operations, logically sequential table data might reside on non-sequential data pages, which results in the database manager having to perform additional read operations to access data. Additional read operations are also required when accessing data in a table from which a significant number of rows have been deleted. You can enable the DB2 server to reorganize the system catalog tables as well as user tables.

The statistical information that is collected by the runstats utility shows the distribution of data within a table. Analysis of these statistics can indicate when and what kind of reorganization is necessary.

The automatic reorganization process determines the need for table or index reorganization by using formulas that are part of the reorgchk utility. It periodically evaluates tables and indexes that have had their statistics updated to see if reorganization is required, and schedules such operations when they are necessary.

Enabling the database for automatic reorganization

To enable your database for automatic reorganization, set the configuration parameters auto_maint, auto_tbl_maint, and auto_reorg to ON. Listing 14 shows sample commands for setting the database configuration parameters to enable automatic database reorganization.

Listing 14. Commands to enable automatic database reorganization
UPDATE DB CFG FOR <DBNAME> USING AUTO_MAINT ON;
UPDATE DB CFG FOR <DBNAME> USING AUTO_TBL_MAINT ON;
UPDATE DB CFG FOR <DBNAME> USING AUTO_REORG ON;

Configuring automatic reorganization policy

You can use an XML document to specify the reorganization policy for your database. In the toolkit, the file named DB2AutoReorgPolicy.xml contains a sample policy to automatically reorganize the data in tables and indexes in your database according to the following criteria:

  • Index reorganization is done online.
  • System temporary table space is used for reorganization.
  • System tables are excluded in the automatic reorganization.

Listing 15 shows an example of using the sysproc.automaint_set_policyfile stored procedure to to specify the reorganization policy for your database with the parameters specified in the DB2AutoReorgPolicy.xml file This same command is used in the reorgsetup.sh script included with the toolkit. If you follow the instructions below for using the toolkit script, you do not have to run this command manually.

Listing 15. Command to set the automatic reorganization policy
db2 "call sysproc.automaint_set_policyfile('AUTO_REORG', 'DB2AutoReorgPolicy.xml')"

Setup instructions

Follow these steps to setup automatic reorganization using the embed-bundle.zip toolkit files:

  1. Make sure that DB2 is running.
  2. Login to the target computer as the DB2 instance owner (for example, db2inst1).
  3. Run the reorgsetup.sh script from the /home/db2inst1/embed-bundle directory as shown in Listing 16.
    Listing 16. How to use the reorgsetup.sh script
    Syntax:
    reorgsetup.sh <dbname>
    
    Example:
    ./ reorgsetup.sh mydb

Silent updates to the DB2 database server

IBM periodically delivers updates to DB2 in the form of installable images called fix packs.

Each fix pack image contains a command line utility named installFixPack that you can use to silently update the installed DB2 product in a given location. The utility can be used on any UNIX or Linux platform to bring every instance on the installation path up to the same level as the fix pack image.

Before installing a DB2 fix pack, the database manager (DBM) of every instance on the installation path must be stopped and all DB2 libraries must be unloaded. If these preconditions are satisfied, installFixPack will install the fix pack and update each instance and DB2 administration server (DAS) on the installation path. No additional manual update is required.

The embed-bundle.zip toolkit contains a wrapper script named silent_fixpackinstall.sh that you can use to stop all DB2 services, install the fix pack, and update the database server instance silently using the installFixPack utility.

Downloading the DB2 fix pack

Follow these steps to download a DB2 fix pack:

  1. Determine which fix pack you need to install. In general, IBM recommends that you install the most recent fix pack to avoid encountering problems caused by software defects already known and corrected by IBM. All the DB2 fix packs are cumulative, which means that applying the latest fix pack automatically applies all the fixes that came with the previous fix packs of a specific DB2 server release.
  2. Locate the fix pack you want to download on the DB2 Fix Packs by version for DB2 for Linux, UNIX and Windows support page. Ensure that you choose the appropriate fix pack for your version of DB2 and operating system.
  3. Download the fix pack image and copy it to the target computer.
  4. Uncompress the fix pack image on the target computer. See Listing 17 for samples of commands to uncompress the image.
    Listing 17. Sample commands to uncompress the fix pack image
    gunzip imagefile.tar.gz
    tar -xvf imagefile.tar

Silently applying the DB2 fix pack

Follow these steps to silently apply the DB2 fix pack:

  1. Ensure that the fix pack image has been copied to the target computer.
  2. Login to the target computer as the DB2 instance owner (for example, db2inst1).
  3. Run the silent_fixpackinstall.sh script from the /home/db2inst1/embed-bundle directory as shown in Listing 18
Listing 18. How to use the silent_fixpackinstall.sh script
Syntax:
silent_fixpackinstall.sh --imagepath=<fixpack_imagepath> --path=<db2dir>

--imagepath=<fixpack_imagepath>
Specifies the full path to the fix pack image. 
This is the location where the fix pack image is uncompressed. 
This is also the parent directory of the fix pack’s "server" directory.

--path=<db2dir>
Specifies the path where the DB2 product is installed. 
This is same as the value specified for the DB2DIR environment variable.

Example:
silent_fixpackinstall.sh --imagepath=/opt/software --path=/opt/ibm/db2/V9.7

Note: In order to run the installFixPack utility as the DB2 instance owner, you have to add the name of the instance owner user (for example, db2inst1) to the sudoers list on the target computer. When logged in as root user, you can do this by using visudo to edit the /etc/sudoers file and adding the following line to the end:
db2inst1 ALL=(ALL) NOPASSWD:ALL

If you are not able to add db2inst1 to the sudoers list, then as the DB2 instance owner you have to stop all the DB2 services, and as root user you have to install the fix pack. Refer to the silent_fixpackinstall.sh script in the toolkit to see the commands used to stop the DB2 services.


Self tuning memory manager

Self tuning memory manager (STTM) is a DB2 feature that automatically sets values for several memory configuration parameters at startup. Without any DBA intervention, STTM optimizes performance by dynamically distributing available memory resources among the main memory consumers. STMM works on tuning the sort heaps, lock memory, package cache, and buffer pools of different page sizes.

Enabling STMM

STMM is on by default for newly created databases. However, it is also possible to manually turn STMM on or off. To turn STMM on, set the SELF_TUNING_MEM configuration parameter to ON. Listing 19 shows an example of the command you would use to manually enable STMM.

Listing 19. Command to enable STMM on the database
UPDATE DB CFG FOR DATABASE <DBNAME> USING SELF_TUNING_MEM ON;

Once you ensure STMM is turned on, the next step is to specify which memory parameters you want STMM to manage. You do this by setting those parameters to AUTOMATIC. Listing 20 shows an example of a command to turn on STTM for a specific parameter.

Listing 20. Command to enable STMM on a specific database parameter
UPDATE DB CFG FOR DATABASE <DBNAME> USING <PARAMETER> AUTOMATIC;

STMM can also tune multiple buffer pools. To enable a buffer pool to be tuned by STMM, you set the buffer pool size to AUTOMATIC. Listing 21 shows two examples of commands you could use to set a buffer pool size to AUTOMATIC.

Listing 21. Command to enable STMM on a specific buffer pool
ALTER BUFFERPOOL <BUFFERPOOL_NAME> SIZE AUTOMATIC;
CREATE BUFFERPOOL <BUFFERPOOL_NAME> SIZE AUTOMATIC;

Automatic storage management

With automatic storage management, DB2 manages storage at the database level. At the time of database creation, the user specifies a group of storage devices for the database manager to control. The database manager then automatically creates, extends, and adds containers as needed. This feature eliminates the need to use explicit container definitions to manually manage storage at the table space level.

Creating a database with automatic storage

To enable automatic storage management for a DB2 database, specify AUTOMATIC STORAGE YES and the location of the storage paths on the CREATE DATABASE statement. Listing 22 shows an example of a CREATE DATABASE command that enables automatic storage and creates a storage group defined on storagePath1 and storagePath2.

Listing 22. CREATE DATABASE command that enables automatic storage on a given storage path
CREATE DATABASE <DBNAME> AUTOMATIC STORAGE YES ON <storagePath1>, <storagePath2>

Assuming you create a database with the command in Listing 22, any table space that you define to use automatic storage will automatically have storage paths for containers defined on both storagePath1 and storagePath2 locations. Listing 23 shows an example of a CREATE TABLESPACE command that creates a table space that is managed by automatic storage. Because new table spaces that you create in a database where automatic storage is enabled utilize automatic storage by default, the MANAGED BY AUTOMATIC STORAGE clause shown in Listing 23 is optional.

Listing 23. CREATE TABLESPACE command with automatic storage
CREATE TABLESPACE <tbl_space_name> MANAGED BY AUTOMATIC STORAGE

In this example, if storagePath1 and storagePath2 run out of space, you can add additional storage paths for DB2 to manage. Listing 24 shows an example of a command you could use to add storagePath3 to the storage pool. After you issue this command, any automatic storage table spaces would automatically draw storage from this new path.

Listing 24. Command to add a storage path to storage pool
ALTER DATABASE <DBNAME> ADD STORAGE ON <storagePath3>

Automatic storage is enabled by default when you create new databases and uses the storage path specified on the dftdbpath database manager parameter. Therefore, if you want to enable automatic storage and use the default storage path, you do not need to specify AUTOMATIC STORAGE YES or the storage path on the CREATE DATABASE command. Listing 25 shows an example of a CREATE DATABASE command that by default enables automatic storage using the default storage path.

Listing 25. CREATE DATABASE command with default automatic storage management
CREATE DATABASE <DBNAME>

Conclusion

The purpose of this tutorial was to show you how to unleash the power of DB2 9.7 autonomic features. After following the instructions and using the toolkit provided by this tutorial, you should be able to enable DB2 autonomic features and automate routine maintenance tasks. This enables you to embed DB2 databases on Linux, UNIX, and Windows. You can easily customize the policy files and the scripts in the toolkit to meet your own requirements.


Download

DescriptionNameSize
DB2 Automatic Maintenance Toolkitembed-bundle.zip106KB

Resources

Learn

Get products and technologies

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=487534
ArticleTitle=Automate DB2 9.7 database maintenance in an embedded database environment
publish-date=05062010