DB2 Universal Database administration using Tivoli

Overview

Are you using the IBM® Tivoli® family of products to manage your distributed environment? Get the most out of these products by using them to perform DB2® Universal Database™ (DB2 UDB) administration tasks as well, including database backup and database monitoring. This article explains how.

Share:

Nanda Pilaka (nandap@us.ibm.com), Software Engineer, IBM

Nanda Pilaka photoNanda Pilaka works as a Software Engineer with the Content Manager Business Partner Support team in Dallas, Texas. He has DB2 certifications in both application development and administration areas as well as in Content Manager. Prior to working on the Content Manager support team, Nanda was part of the Developer Relations DB2 Technical Support team at IBM Dallas, working on a variety of DB2 application development and administration issues. You can reach Nanda at nandap@us.ibm.com.



Suita Gupta, Software Engineer, IBM

Suita Gupta photoSuita Gupta is part of the i3000 Application Modernization team and provides technical support for ISVs interested in modernizing their iSeries applications. She also provides support for DB2 to IBM Virtual Innovation Center (VIC) users. Suita has DB2 certifications in both application development and administration areas. Prior to working on the i3000 support team, Suita worked with the Developer Relations DB2 Technical Support team on a variety of DB2 application development and administration issues. You can reach Suita at suitag@my.ibm.com.


developerWorks Contributing author
        level

29 September 2005

Introduction

If you have a large number of DB2 Universal Database servers, and if your enterprise is already using Tivoli to manage other components such as your network, operating system, and security, you can also use Tivoli to assist you with management of your databases. This article will show you the ways in which two Tivoli products can be used to administer your DB2 databases. We'll cover crucial functions like database backup, recovery, and monitoring of your database. You'll see step-by-step examples which you can try out for yourself, as well as the steps to set up the required Tivoli products.

The information in this article is presented at a high level. There is a lot to know about using Tivoli to administer and monitor your databases, with specific information about each supported database management system. The goal of this article is to point you in the right direction, leaving you free to explore the details on your own.


Why Tivoli?

DB2 UDB V8.2 and later releases already have a great many autonomic and monitoring features, so the Tivoli software products are not required in order to administer a large DB2 environment. However, if you already have a Tivoli environment and are also using DB2 databases, you may want to ensure that administration of all the components of your system, including your databases, is performed from a centralized console. The following sections will show how you can perform database backup and recovery, as well as monitor the database using Tivoli products.


Tivoli architecture fundamentals

The main function of the Tivoli suite of products is enterprise management. "Enterprise" in this context refers to the resources that need to be managed, including network components, databases, operating systems, and so on. Tivoli software products address the management requirements of specific resources such as systems and devices, and include products for auditing, intranet and Internet management, and user administration. An example of a Tivoli software product would be IBM Tivoli Monitoring for Databases, which we will be using later in the article.

The Tivoli architecture comprises the Tivoli products and the Tivoli Management Framework (TMF). The TMF provides the runtime platform, infrastructure, services, and interfaces (such as Tivoli Desktop), that the Tivoli software products need. Figure 1, taken from the Tivoli InfoCenter, illustrates the general Tivoli architecture:

Figure 1. Tivoli architecture
Tivoli architecture

TSM and database backup and recovery

Introduction to Tivoli Storage Manager

The Tivoli storage management solution is made up of several products, with IBM Tivoli Storage Manager (TSM) being the core product. IBM Tivoli Storage Manager is an enterprise-wide storage management application, in the sense that it provides a centralized backup management solution to administer and manage backup of a variety of workstations, personal computers, and files with a variety of operating systems. The main TSM components are server, administrative interface, backup archive client, and the API (application program interface) component. TSM operates as a client/server application. For details on TSM concepts and architecture please refer to the IBM Redbook IBM Tivoli Storage Management Concepts (see Resources.

What you need to try our examples

For the examples we show in the following sections, we used the following on the Windows® XP platform:

  • Tivoli Storage Manager v5.3
  • DB2 UDB V8.2

DB2 UDB already comes equipped with the necessary functionality to work with Tivoli, so there is no need to purchase any other additional Tivoli modules. If you are working with Oracle or Microsoft SQL Server databases, then you will need to purchase another module called IBM Tivoli Storage Manager for Databases.

Offline database backup and recovery

For this article, we have all the prerequisite software installed on one single machine. However, this is obviously not a requirement in a real-life scenario. Our machine, IBM-991KR30, has the following software installed:

  • TSM Server
  • TSM Client and API
  • DB2 UDB

TSM operates as a client/server application, so if the TSM server is located on a different machine from the DB2 server, then the DB2 Server needs to have just the TSM Client and API installed on it. One advantage of having both the TSM server and TSM client on one machine is that it allows you to do LAN-free backup and archival.

Step 1:
If your DB2 server is on a different machine from the TSM Server, you will need to explicitly register your DB2 server using the register node command. You can create a new storage policy for the DB2 node and provide this information while registering the node, or the node will be assigned to the default policy domain called STANDARD policy domain.

In simple terms, a policy is a set of rules that determine how the backup must be managed, how many versions of the backup should be kept, and where the images should be stored. You can group a set of DB2 server nodes under one policy domain so that all the DB2 servers follow a consistent backup policy. The policy domain links all the nodes to a policy set, which in turn consists of one or more management classes. The management class contains rules called backup copy groups that specify factors such as number of stored copies, retention period, storage media, and so on. For more information on creating policies, please refer to the Tivoli InfoCenter: Defining and updating policy sets page (see Resources).

For this example, you do not need to register the node, and you will be using the default STANDARD policy domain. Therefore you can proceed to the next step.

Step 2:
Set the environment variables needed by the client API. To set these variables, right-click the My Computer icon on the desktop, and select Properties. Click the Advanced tab, then click Environment Variables. You can add these variables using the New button.

  • DSMI_DIR: Set this variable to point to the installation directory of the TSM API.
  • DSMI_CONFIG: Set this variable to point to the API client options file (that you will be create in Step 3).
  • DSMI_LOG: Set this variable to point to where the dsierror.log file will be located. This log file contains error messages generated by the API client.

After setting the enviroment variables, execute the db2stop and db2start commands so that your changes can take effect.

Figure 2. Environment variables needed by the client API
Environment variables needed by the client API

Step 3:
Create a new file called dsm.opt using a text editor. Save this file in the C:\Program Files\Tivoli\TSM\api folder (or in the location you specified in the DSM_CONFIG environment variable). This file is used by the TSM client to locate the TSM server on the network. You need to manually define values for the following options:

  • commmethod: tcpip
  • tcpport: the port number where the TSM Server listens for incoming requests (the default port number is 1500)
  • tcpserveraddress: the IP address of your TSM Server machine
  • passwordaccess: generate (This setting allows the TSM server to generate the password automatically.)
  • Nodename: For this scenario, this will be the same value as the tcpserveraddress option. However, in scenarios where the TSM server is on a different machine, this option refers to the node registered using the register node command.
Figure 3. dsm.opt file
dsm.opt file

You will need to reboot the machine for all the changes to take effect.

Step 4:
Since you set the passwordaccess option to generate the password automatically, you need to run the dsmapipw.exe utility to generate an encrypted password. You can find this utility in the DB2_HOME/sqllib/adsm folder. This utility will prompt for the current password and a new password, which it will encrypt.

Step 5:
You are now ready to perform your first db2 backup using the TSM setup. To do so, ensure that your DB2 instance is up and running using the db2start command. First try doing an offline backup using the use tsm option of the db2 backup command:

Listing 1. Backup using TSM
C:\Documents and Settings\Administrator>db2 backup db sa use tsm 
Backup successful. The timestamp for this backup image is: 
20050724132015

To perform an online backup and restore, you must enable the database for roll-forward recovery using TSM. This is covered in the section Online database backup and restore using TSM.

Before restoring the database using the TSM backup, verify that the backup taken is restorable and readable. You can do this using the db2adutl utility. This is similar to checking a non-TSM backup using the db2ckbkp utility. Listing 2 shows an example of running the db2adutl utility.

Listing 2. Verify the backup using the db2adutl utility
C:\Documents and Settings\Administrator>db2adutl verify full 
taken at 20050724132015 db sa
Query for database SA
Retrieving FULL DATABASE BACKUP information.  Please wait.
   FULL DATABASE BACKUP image:
      .\SA.0\DB2\NODE0000\CATN0000\20050724\132015.001, DB Partition 
Number: 0
   Do you wish to verify this image (Y/N)? y
   Verifying file: .\SA.0\DB2\NODE0000\CATN0000\20050724\132015.001
######
WARNING only partial image read, bytes read: 16384 of 4984832
Read 0 bytes, assuming we are at the end of the image
Image Verification Complete - successful.
Retrieving INCREMENTAL DATABASE BACKUP information.

The next step is to restore this backup using version recovery:

Listing 3. Restore using version recovery
C:\Documents and Settings\Administrator>db2 restore db sa use 
tsm taken at 20050724132015
SQL2539W Warning!  Restoring to an existing database that is 
the same as the backup image database.  The database files will 
be deleted.
Do you want to continue? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.

Online database backup and restore using TSM

In order to enable roll-forward recovery for your database you need to have a configured user exit program in place. This program will be responsible for archiving log files from your local disk to the TSM server, and also for retrieving the logfiles during the recovery stage. IBM provides user exit sample code for TSM backup and recovery. You can find this in the DB2_HOME/sqllib/samples/c folder, in a file named db2uext2.ctsm. Instructions for building and compiling this code can be found in the source file. A copy of the compiled code needs to be placed in the DB2_HOME/sqllib/adsm folder, as well as in the DB2_HOME/sqllib/bin folder.

Once you have compiled the user exit, enable the database for roll-forward recovery by configuring the USEREXIT database configuration parameter for the database.

Listing 5. Configuring the USEREXIT parameter
C:\Documents and Settings\Administrator>db2 update db cfg using 
USEREXIT ON
DB20000I  The UPDATE DATABASE CONFIGURATION command completed 
successfully. SQL1363W One or more of the parameters submitted 
for immediate modification were not changed dynamically. For 
these configuration parameters, all applications must disconnect 
from this database before the changes become effective.

C:\Documents and Settings\Administrator>db2 terminate
DB20000I  The TERMINATE command completed successfully.

C:\Documents and Settings\Administrator>db2stop
08/02/2005 17:34:16     0   0   SQL1064N  DB2STOP processing was 
successful.
SQL1064N  DB2STOP processing was successful.

C:\Documents and Settings\Administrator>db2start
08/02/2005 17:34:23     0   0   SQL1063N  DB2START processing was 
successful.
SQL1063N  DB2START processing was successful.

You should take a full offline backup before performing an online backup as follows:

Listing 5. Online backup
C:\Documents and Settings\Administrator>db2 backup db tivbckp 
online use tsm

Backup successful. The timestamp for this backup image is: 
20050802174742

C:\Documents and Settings\Administrator>db2 restore db tivbckp 
use tsm taken at 20050802174742
SQL2539W  Warning!  Restoring to an existing database that is the 
same as the backup image database.  The database files will be 
deleted.
Do you want to continue? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.

C:\Documents and Settings\Administrator>db2 rollforward db 
tivbckp to end of logs and stop
                                 Rollforward Status

 Input database alias                   = tivbckp
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000002.LOG - S0000002.LOG
 Last committed transaction             = 2005-08-02-09.47.55.000000
DB20000I  The ROLLFORWARD command completed successfully.

At this point, you have learned the basics of Tivoli architecture, setting up TSM, taking online and offline backups, restoring backups and doing rollfoward of logs. Let's proceed to learn how to use Tivoli to monitor DB2 databases.


Database Monitoring

Databases are ubiquitous in all business applications today and are a major component of any enterprise software solution. They provide a reliable and efficient way to store and access persistent data. This article considers the topic of monitoring only with regard to DB2 relational databases.

Some of the criteria that databases must meet are:

  • Fast query response time
  • Controlled data availability
  • Efficient use of system resources
  • Data integrity protection mechanisms
  • Failover mechanisms
  • Security mechanisms
  • Scalability features

Why use IBM Tivoli for Database Monitoring?

Database administrators frequently need to be able to administer and monitor various components of a database system from a central point, as well as administer resources that the database system uses. To help you achieve this objective, a systems management tool (such as IBM Tivoli Monitoring for Databases) enables you to meet the wide-ranging needs that depend on the environment, such as:

  • The ability to monitor various database performance metrics
  • A consistent interface to operate the database (to avoid errors)
  • The ability to integrate with other monitoring and reporting tools

These capabilities help to:

  • Reduce the database response time
  • Increase the database throughput
  • Increase the processing efficiency of a database instance
  • Tune resources, such as the CPU, memory, I/O, and so on
  • Ensure uninterrupted availability of data to users
  • Maintain data integrity
  • Monitor database health easily

Some of the capabilities of IBM Tivoli Monitoring for Databases include:

  • Auto-discovery of the resources to be monitored
  • Problem identification, notification, and correction
  • Automated best practices for management and operations
  • Historical reporting through a centralized data warehouse

What is IBM Tivoli for Database Monitoring?

IBM Tivoli Monitoring for Databases is one of a family of solutions based on the IBM Tivoli Monitoring product. It comes with the following modules:

  • IBM Tivoli Monitoring for Databases: DB2, which support DB2 versions 7.*
  • IBM Tivoli Monitoring for Databases: Oracle, which support Oracle versions 8.1.7 and 9.0.1
  • IBM Tivoli Monitoring for Databases: Informix, which support Informix versions 7.31, 9.21, and 9.30

This focuses on IBM Tivoli Monitoring for Databases: DB2.

Product requirements and prerequisites

While there are several components that IBM Tivoli Monitoring for Databases relies on, the primary components required for it to work properly are:

  • Tivoli Framework: As a framework-based application that is deployed on the endpoints, it relies heavily upon features of the Tivoli Framework facility, such as Endpoint level, MDist distribution, and Task library structure.
  • IBM Tivoli Monitoring Advanced Edition: IBM Tivoli Monitoring for Databases uses the latest monitoring technology that is available with IBM Tivoli Monitoring. It uses resource-model-based monitors that allow local analysis and correlation on the endpoint level to facilitate more accurate monitoring schemes.
  • IBM Tivoli Monitoring Component Services: This provides basic utilities for application management, including the necessary tools executable and object properties. This component replaces the Tivoli Application Services and Tivoli Application Proxy Services.
  • One of the supported DBMSs listed above.

The endpoints have an additional requirement: Because the resource model deployed with the IBM Tivoli Monitoring for Databases monitors are written in JavaScript, you need to install a Java Runtime Environment (JRE) on all endpoints to be managed. You may obtain a JRE from the IBM developerWorks portal.

Installation steps overview

This is a generic overview of the complete installation process for IBM Tivoli Monitoring for Databases. All Tivoli products can be installed from the desktop using Software Installation Service (SIS) or from the command line. Please refer to the Tivoli Framework 3.7.1 User's Guide, GC31-8433 if you need more information on the Tivoli installation process.

The following installation description uses the Tivoli Desktop:

  1. Install the appropriate IBM Tivoli Monitoring for Databases product (from the Tivoli Desktop, select Desktop -> Install -> Install Product). The install product dialog illustrated in Figure 4 shows the products available to install:
    Figure 4. Install dialog
    Figure 4. Install dialog

    Tip: Before and after each installation step, remember to create a Tivoli object database backup using the wbkupdb command, or using the Tivoli Desktop menu: Desktop -> Backup. The backup file will allow you to reverse any failure or error caused by the installation.

    You need to install the product at the Tivoli Monitoring Region Server, and on all the gateways in your environment where IBM Tivoli Monitoring for Databases will be used. Click Install, and the product installation screen will show the action that will be performed. Click the Continue Install button. When the installation is complete, click Close.

  2. At all the endpoints that you want to manage, you need to have JRE version 1.3 installed. Then you will need to run the DMLinkJRE task. This task can be run to multiple endpoints simultaneously if all these endpoints have JRE installed in the same directory. You may want to run this task separately for each platform, as they have different installation conventions. These are the steps:
    1. Select Desktop -> Navigator from the Navigator window. Check the Task Library at the bottom of the Navigate To list.
    2. Open the IBM Tivoli Monitoring Tasks task library.
    3. Run the DMLinkJre task. Specify the endpoints for the Execute Task dialog (you need to selectively execute to all endpoint objects that have the same JRE installation directory). You can also identify endpoints using one or more profile managers. Click Execute.

      Note: Ensure that the path where JRE is physically installed is the same on all selected endpoints.

    4. On the DMLinkJre dialog shown in the figure below, enter the complete path where JRE is installed. For example, C:/jre means that you can execute the C:/jre/bin/java -version command to get the JRE version.
      Figure 5. DMlink JRE task argument
      Figure 5. DMlinkJRE task argument
    5. Click Set & Execute. JRE will be linked to the product on all selected endpoints. When the execution is successful, the result will be similar to the figure below.
      Figure 6. DMLinkJRE result
      Figure 6. DMLinkJRE result
  3. You might want to create additional Tivoli administrators to manage each database environment. (See the Tivoli Framework 3.7.1 User's Guide, GC31-8433 for more information on creating an administrator.) The IBM Tivoli Monitoring for Databases products have additional roles defined for managing databases. The Tivoli administrator that will manage the database environment needs to have these roles assigned to it. Table 1 shows the list of needed authorization roles for each product.

    Table 1. List of needed authorization roles, IBM Tivoli Monitoring for Databases
    RoleDB2OracleInformix
    System administratorseniorseniorsenior
    IBMInformix_super
    Database administratordb2_dbadb2_dbaoracle_dba
    admin
    IBMInformix_admin
    Userdb2_useroracle_userIBMInformix_user

    You can assign the above roles to an administrator from the Tivoli desktop. Double-click the Administrators icon and right-click the Administrator icon and select Edit Resource Roles or Edit TMR Roles. The roles are:

    • System administrator: Assigns Tivoli roles and resources based on the Policy Region structure
    • Database administrator: Performs day-to-day database maintenance and monitoring
    • User: Performs operation of the Tivoli environment, and needs the ability to check basic database health
  4. When you install IBM Tivoli Monitoring for Databases, the policy region for managing resources is created for you. The default policy region is called:
    • Monitoring for DB2
    • Monitoring for Oracle
    • Monitoring for IBM Informix
    For DB2, you need to link this policy region to an administrator desktop. The managed resources for the policy regions are shown in Table 2:

    Table 2. Managed resources assignment
    DB2OracleInformix
    CommonProfileManager
    Tmw2kProfile
    SpecificsDB2DatabaseManager
    DB2Discovery
    DB2Gateway
    DB2InstanceManager
    DB2PartitionGroupManager
    DB2PartitionManager
    OracleDiscovery
    OracleDatabaseManager
    OracleInstanceManager
    OracleRoleManagerProfile
    OracleUserManagerProfile
    OracleResourceManagerProfile
    IBMInformixServer
    InformixDiscovery
    InformixDiscoveryHBO
  5. Next, to enable IBM Tivoli Monitoring for Databases, you need to configure it for DB2. This is discussed in the next section. Figure 7 shows the root desktop when only IBM Tivoli Monitoring for Databases components are installed.
    Figure 7. Tivoli desktop with IBM Tivoli Monitoring for Databases
    Tivoli deskotp with IBM Tivoli Monitoring for Databases

Configuring IBM Tivoli Monitoring for Databases: DB2

Once you have installed IBM Tivoli Monitoring for Databases: DB2, you need to perform the following configuration steps:

  1. Create a db2ecc user ID at the endpoint where you have the DB2 database. This user ID will be used to monitor DB2.

    On Windows, you need to grant this user ID the permission to run a service called Tivoli-DB2Monitoring Service. To do this, select Start -> Programs -> Administrative Tools -> Local Security Policy, and then select Local Policies -> User Rights Assignment in the Local Security Settings window. Double-click Log on as a service and the Local Security Policy Setting dialog will pop up. Click the Add button and add the db2ecc account to the list.

    For AIX, create the db2ecc user ID using the smit user command and select Add User.

    Within DB2, monitoring requires SYSADM, SYSMAINT, or SYSCTRL authority. Assign db2ecc to a group with sufficient authority. For a typical installation, the db2iadm1 group is sufficient.

  2. Perform resource discovery using the following steps:
    1. Right-click the DB2 discovery icon from the policy region, and select Edit endpoints. From the list, select the endpoints where you want to run the discovery (that is, the machines that have DB2 installed), as shown in Figure 8. On the Windows platform, be sure to specify the password for the db2ecc user ID.
      Figure 8. Edit task endpoints dialog
      Figure 8. Edit task endpoints dialog
    2. Run the discovery by double-clicking the DB2 discovery icon. The discovery progress is shown in a dialog box, as shown in Figure 9:
      Figure 9. DB2 instance discovery dialog
      Figure 9. DB2 instance discovery dialog
  3. Once the discovery is complete, the policy region where you run the discovery from should have icons for the database instances, as shown in Figure 10:
    Figure 10. DB2 policy region
    Figure 10. DB2 policy region
  4. From each instance, you may want to discover the databases. To do this, right-click an instance icon and selecting Discover Databases.

    Restriction: The discovery cannot discover DB2 Extended Enterprise Edition databases, because they are considered to be partitioned.

    The list of databases in the instance will be shown. You can move the databases that you want to manage into the right area and click on Add & Close as shown in Figure 11:
    Figure 11. Discover database dialog
    Figure 11. Discover database dialog
  5. For a DB2 Extended Enterprise Edition database, the databases can be created as DB2 Partition objects using Create -> DB2PartitionManager, as shown in Figure 12:
    Figure 12. DB2 partition creation
    Figure 12. DB2 partition creation

    When you click Discover, the databases belonging to that instance will be listed as shown in Figure 13:

    Figure 13. DB2 EEE database list
    Figure 13. DB2 EEE database list
  6. You can group DB2PartitionManager into a group as an object called DB2PartitionGroupManager. Select Create -> DB2PartitionGroupManager to create it. Figure 14 shows creating this group:
    Figure 14. DB2 partition group manager creation
    Figure 14. DB2 partition group manager creation
  7. At this point, the policy region is now similar to the figure below:
    Figure 15. DB2 partition group manager creation
    Figure 15. DB2 partition group manager creation

How is database monitoring performed?

Database monitoring is performed through the use of resource models which take into account various database metrics to monitor the health of the database system.

IBM Tivoli Monitoring Version 5, which provides the basic environment for IBM Tivoli Monitoring for Databases, uses a set of resource models to understand database health. The resource model contains rules or schemas that determine what data is to be accessed from an endpoint at runtime, and how this data is to be handled. Each resource model obtains resource data from the endpoint it is distributed to, analyzes it using built-in logic, and proceeds to execute actions that are either built-in actions or user-defined. The implementation of a resource model can be seen in the figure below.

Figure 16. IBM Tivoli Monitoring resource model
Figure 16. IBM Tivoli Monitoring resource model

As shown in the above figure, the implementation of a resource model has two components: the dynamic model and the reference model.

  • The dynamic model is the definition of a set of attributes that specify the current state of the resource. The attribute values typically will change dynamically while the system is working. For the database resources, IBM Tivoli Monitoring for Databases provides several data providers for acquiring the database metrics.
  • The reference model is the active part of the monitoring that:
    • Acquires the resource metric
    • Interprets the quality of a resource against a threshold
    • Determines the root cause of a quality reduction by a set of logic
    • Generates the indication of the problem, such as sending an event or executing an action

All the IBM Tivoli Monitoring for Databases products come equipped with their own resource models. Please refer to refer to IBM Tivoli Monitoring Version 5.1: Advanced Resource Monitoring, SG24-5519 for more information on how IBM Tivoli Monitoring and its resource model works.

Supplied resource models:
The resource models that are supplied by the IBM Tivoli Monitoring for Databases products are categorized as follows:

  • Process monitoring: This is the basic measurement of the database availability. This monitors whether a specified database server process exists or not.
  • Memory usage monitoring: Relational database systems use memory to buffer or cache a lot of information to minimize the need to access the data from the disk. The memory usage needs to be monitored to ensure that there is always enough space and that it has a high hit ratio (the data that is searched is found in memory).
  • Activity monitors: Monitors the activity of the database, the number of connections, CPU utilization, I/O rate, and number of applications. These need to be monitored to ensure that there is no sudden change of load, and to predict future growth.
  • Disk space usage monitors: The actual data of a database is stored on the disk for persistence. Disk space needs to be monitored to ensure availability to insert more data, and disk organization needs to be monitored to ensure performance when accessing data in the disk.
  • Locking monitors: Data is locked in memory before transactions are committed. Some of these transactions may lock data for a long time or lock contention may occur. While locks guarantee the data integrity, it may possibly defer access to data and even, in the case of deadlocks, disable access to data.
  • Log monitors: All updates to data in memory are logged, and these logs are necessary to ensure that data can be recovered. Some transactions may perform a lot of updates before committing them, causing a large chunk of needed logs.
  • Replication monitors: Monitors the status of data replication activities. These resource models only apply to DB2 and Informix.
  • Others: Each database server has its own special monitors for functions that are unique to that database system.

DB2 resource models:
IBM Tivoli Monitoring for Databases: DB2 comes with a number of resource models ready to use with little or no customization. The available resource models for IBM Tivoli Monitoring for Databases: DB2, grouped by their functions, are shown below:

  • Process monitoring resource models:
    • DB2 Agent resource model
    • DB2 Instance Status resource model
  • Memory usage monitoring resource models:
    • DB2 Buffer Pools resource model
    • DB2 Buffer Pool/Extended Storage resource model
    • DB2 Catalog Cache resource model
    • DB2 Package Cache resource model
  • Activity monitoring resource models:
    • DB2 CPU Utilization resource model
    • DB2 Database Activity resource model
    • DB2 Direct IO resource model
    • DB2 Host Throughput resource model
    • DB2 SQL Cursor Activity resource model
    • DB2 SQL Statement Activity resource model
    • DB2 Table Activity resource model
  • Disk space usage resource models:
    • DB2 SAP Tablespace Usage/Status resource model
  • Lock monitoring resource models:
    • DB2 Locks and Deadlocks resource model
    • DB2 Lock Waits resource model
  • Log monitoring resource models:
    • DB2 Logging resource model
  • Replication monitoring resource models:
    • DB2 Apply Replication Status resource model
    • DB2 Replication Capture resource model
    • DB2 Table Apply Replication resource models
  • Miscellaneous resource models:
    • DB2 Fast Communication Manager resource model
    • DB2 Sorting resource model

Customizing resource models:
IBM Tivoli Monitoring for Databases also gives you the ability to customize an existing resource model, for situations when you need to perform a monitoring scenario that is slightly different from what is supplied. Customization would only be required if the feature is not provided by the resource model or if you would like to add some new features and functionality. You also create a resource model from scratch, or you can use an existing resource model and modify it.

Here is an example of DB2InstanceStatus resource model. Conceptually, it is very simple. Open the resource model, choose Modify action, modify the action to be performed, save it under a new name, and re-deploy the new resource model.

The customized DB2InstanceStatus resource model will automatically start the DB2 service if it happens to fall over. In order to customize resource models, IBM Tivoli Monitoring Workbench Version 5.1.1 is required. For more information on how to install the Workbench, refer to the IBM Tivoli Monitoring Version 5.1: Advanced Resource Monitoring, SG24-5519.

To customize the resource model:

  1. Start the IBM Tivoli Monitoring Workbench.
  2. Open the Distributed Monitoring JavaScript Workspace (dmjsws) file provided on the IBM Tivoli Monitoring for Databases:DB2 installation CD, under the Workbench directory.
    Figure 17. Opening the Java script workspace file
    Opening the Java script workspace file
  3. Once the JavaScript workspace is opened, you should see DB2InstanceStatus in the left window pane. Rename the resource model as DB2InstanceStatus1, to avoid overwriting the existing one. Right-click the DB2InstanceStatus and select Modify as shown below. Click Apply.
    Figure 18. General settings window
    General settings window
  4. Expand the resource model by clicking on the + signs until you get to the DB2DownStatus, as shown below:
    Figure 19. DB2InstanceStatus tree
    DB2InstanceStatus tree
  5. Right-click DB2_Down_Status, and select Modify from the pop-up menu. The dialog in the figure below is displayed.
    Figure 20. Events dialog
    Events dialog
  6. Click the Actions button. The Actions dialog box will appear, as shown in the figure below.
    Figure 21. Actions dialog
    Actions dialog
  7. Click the Add Program button, and fill in all the fields in the Define the Run Program Action dialog box:
    Figure 22. Run program action
    Run program action
  8. Once you have filled in all the required fields, click OK. The Actions dialog box is displayed with the new action you have just added. Click the Close button to close this window. Click OK on the Events dialog box as well to close it. The db2start.bat file is shown below:
    @echo off
    cmd /c "net start db2 > c:\temp\started.txt"
    exit
  9. Now you need to configure the endpoint to run the dbstart.bat file in the event that the DB2 service stops. To do this, you need to create a dependency. From the main left window pane in the Workbench:
    1. Select Dependencies.
    2. Click on the plus sign to expand it.
    3. Select w32-ix86.
    4. Right-click and select Add from the pop-up menu.
    5. Select the dbstart.bat file from the directory where you created the file, and click Open.
  10. After this, the dependency will be seen under the w32-ix86 Dependencies, as shown below:
    Figure 23. Dependencies
    Dependencies
  11. Build the package. From the Build menu, select Build Package. The Save As dialog box opens, as shown below:
    Figure 24. wdmrm output
    wdmrm outputImportant: Save the file with a .tar extension.
  12. Copy the tar file onto your Tivoli Management Region (TMR) server in order to import the resource model.
  13. Once the file is copied, issue the wdmrm command. The output should appear as shown below:
    Figure 25. DB2InstanceStatus tree
    DB2InstanceStatus tree
  14. At this point, if you create a new profile containing the DB2InstanceStatus1 resource model, you should see the DB2 Service action in the Indications section under the Action List. If the Instance Status is down, the DB2 Service action is selected from the indications.

Once you have the new resource model, you can create a new profile that contains the new resource model and distribute it.

Sample monitoring scenario -- Backup and restore

There are many areas of database administration from which we can draw sample scenarios. Some of the most commonly performed tasks fall under the following areas:

  • Installation and configuration
  • Administration
  • Performance monitoring and tuning
  • Backup and restore

Let's look at the backup and restore scenario and consider how monitoring can be utilized in this case. This scenario encompasses the following tasks:

  • Backing up a database or table space
  • Checking data in a table space and copying a table space
  • Restoring a database or table space
  • Rolling forward a database or table space

To do this, you would follow a model called the KIMA process. The steps of this process are shown below:

Figure 26. KIMA process
KIMA process

The process shown can be broken down into these steps:

  1. Know the database management objectives and tasks.
  2. Identify the critical parameters to be controlled.
  3. Monitor those parameters.
  4. Act proactively.

These stages are relevant for all types of databases and scenarios.

In the Know step, recognize the basic objectives of the backup and restore scenario, which are:

  • Backing up a database or table space
  • Restoring a database or table space

In the Identify step, determine which parameters you need to monitor. Most of these are related to the backup size, backup frequency, and success of the backup process.

In the Monitor step, monitor the database systems, along with the backup and restore activities, using the following resource models to monitor the resources:

  • DB2 Apply Replication Status resource model: Monitors the status of the DB2 Apply component from IBM Replication. Replication is a process of maintaining a defined set of data in more than one location. It involves copying designated changes from one location (a source) to another (a target), and synchronizing the data in both locations. The source and target can be in logical servers that are on the same machine or on different machines in a distributed network.
  • DB2 Replication Capture resource model: Monitors the status of DB2 Capture component from IBM Replication.

In the Act step, set up automated tasks to perform the action that relates to the backup and restore process.

Conclusion

Tivoli products such as IBM Tivoli Storage Manager and IBM Tivoli Monitoring for Databases offer database administrators the ability to administer and monitor databases on a wide range of criteria from a centralized location. This article has provided an introduction to Tivoli, and also quick steps on how to set up and use two major Tivoli products for administering DB2 databases. For further details, please do look up the guides and Redbooks mentioned throughout the article and in the References section.

References

  • IBM Tivoli Storage Management Concepts redbook (www.redbooks.ibm.com # SG24-4877-03).
  • Tivoli InfoCenter: http://publib.boulder.ibm.com/infocenter/tivihelp/v1r1/index.jsp
  • Backing up DB2 using Tivoli Storage Manager (www.redbooks.ibm.com # SG24-6247-00)
  • DB2 InfoCenter: http://publib.boulder.ibm.com/infocenter/db2help/index.jsp
  • IBM Tivoli Monitoring for Databases Database Management Made Simple Redbook (www.redbooks.ibm.com #SG24-6613-00)

Resources

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, Tivoli, Tivoli (service management)
ArticleID=95097
ArticleTitle=DB2 Universal Database administration using Tivoli
publish-date=09292005