The db2pd tool

A new DB2 UDB utility for monitoring DB2 instances and databases

In Version 8.2, IBM® DB2® Universal Database™ introduced a new tool for monitoring and administrating DB2 databases and instances called db2pd. Use this tool to keep track of transactions, tablespaces, table statistics, dynamic SQL, and all your configuration settings. It's handy for troubleshooting, problem determination, performance tuning, and more.

Samuel Poon (poons@ca.ibm.com), DB2 Consultant, IBM Toronto

Samuel Poon works as a DB2 Database consultant at IBM Toronto Laboratory. He is a member of the Information Management Technical Enablement team and is certified as an IBM DB2 Advanced DBA and DB2 Application Developer.



07 April 2005

Introduction

DB2 UDB V8.2 comes with a new utility for collecting statistics for DB2 instances and databases. This utility is called db2pd. Similar to the onstat utility of Informix® Dynamic Server, db2pd provides more than twenty options to display information about database transactions, tablespaces, table statistics, dynamic SQL, database configurations, and many other database details. A single db2pd command can retrieve multiple areas of information and can route the output to files. The utility can also be invoked a specified number of times within a specified period of time, to help you understand changes over time. Use this tool for troubleshooting, problem determination, database monitoring, performance tuning, and to aid in application development design. This article introduces this new db2pd utility and uses examples to demonstrate how it can enhance your productivity.


Use the db2pd utility

In order to use this powerful tool, you must have SYSADMIN authority and you must be the instance owner in the UNIX® or Linux™ environment. The command syntax and option descriptions can be easily found using the online DB2 UDB Information Center . An easy way to find the syntax for the db2pd command is to use a Web search engine, as you see in the following example with Google:

1. Access Google search engine as shown Figure 1.

Figure 1. Searching for the DB2 command db2pd using Google search engine
Searching DB2 command db2pd using Google Search Engine

2. Search "db2pd" as shown Figure 2.

Figure 2. Search results of DB2 command dp2pd from Google
Search results of DB2 command db2pd from Google

3. Access db2pd - Monitor and Troubleshoot DB Command as shown in Figure 3.

Figure 3. Information about db2pd utility in DB2 Information Center
Information about db2pd utility in DB2 Information center

There are two ways to invoke the db2pd utility. You can either invoke the db2pd tool in an interactive mode, or directly from an operating system command prompt. To execute the tool using the interactive mode, enter db2pd -interactive or simply type db2pd at an operating system command prompt, and you will receive a db2pd command prompt db2pd> where command options are accepted. You can also obtain help information by using the -help option. To exit the db2pd command prompt, simply enter either quit or q. The example in Figure 4 shows you how to display the current agents using the interactive mode.

Figure 4. Invoking db2pd in interactive mode
Invoking db2pd in interactive mode

To invoke the utility at an operating system command prompt, you can enter the db2pd command with the command option. The following example (Figure 5) shows all the active agents using the -agents option.

Figure 5. Invoking db2pd at an operating-system command prompt
Invoking db2pd at an operating-system command prompt

In addition, you can control the command by storing the options in a file or by setting the options in the DB2PDOPT environment variable. The example below (Figure 6) shows that you can store the -agents option in a file named (in this example) file.out. You can then type db2pd -command file.out to execute the option.

Figure 6. Storing db2pd options in a file
Storing db2pd options in a file

If you want to use the DB2PDOPT environment variable, you can set the DB2PDOPT to your option and then invoke the db2pd utility as follows:

Figure 7. Setting db2pd options in the DB2PDOPT environment variable
Setting db2pd options in the DB2PDOPT environment variable

There are 22 options available for the db2pd command. If you want to run all of them for all active local databases on all database partition servers, you simply enter db2pd -everything. Alternatively, you can limit the scope of the information by specifying the scope options shown in Table 1.

Table 1. db2pd scope option
ScopeScope optionDescription
Instance-inst or -insfor an instance
Database-database x or -db xfor a specific database x
Database-alldatabase or -alldbfor all databases
Partition-dbpartitionnum n or -dbp nfor a specific database partition server n
Partition-alldbpartitionnums or -alldbpfor all database partition servers in the instance

Note that a dbp2pd option is either in the scope of an instance or a database. If a database scope option is used, the specified database must be already active. Table 2 shows each db2pd option, the description of the option, and its corresponding scope.

Table 2. Scope of db2pd options
db2pd optionDescriptionScope
agentsReturns information about agentsInstance
applicationsReturns information about applicationsDatabase
bufferpoolsReturns information about the buffer poolsDatabase
CatalogcacheReturns information about the catalog cacheDatabase
dbcfgReturns the settings of the database configuration parametersDatabase
dbmcfgReturns the settings of the database manager configuration parametersInstance
dynamicReturns information the execution of dynamic SQLDatabase
fcmReturns information about the fast communication managerInstance
helpReturns help information of the db2pd commandNA
logsReturns information about the logsDatabase
locksReturns information about the locksDatabase
mempoolsReturns information about the memory poolsBoth
memsetsReturns information about the memory setsBoth
osinfoReturns information about the operating systemInstance
recoveryReturns information about recovery activityDatabase
reoptReturns information about Cached SQL statements that were reoptimized using REOPT ONCE option applicationsDatabase
reorgReturns information about table reorganizationDatabase
staticReturns information about the execution of static SQL and packagesDatabase
sysplexReturns information about the list of servers associated with the database alias for all databases or for a particular databaseInstance
tablespaceReturns information about the table spacesDatabase
tcbstatsReturns information about tables and indexesDatabase
transactionsReturns active transaction informationDatabase
versionReturns information about the current DB2 version and levelInstance

To make this command even more powerful, you can specify the -repeat parameter to repeat the command. For instance, the following command displays the DB2 memory information once every 2 seconds, 5 times:

db2pd -mempools  -repeat 2 5

In addition, you can send the output of a specific db2pd command option to a file by specifying the file= parameter. You can combine the file and the repeat parameters as follows:

db2pd -mempools file=memp.txt -repeat 2 5

where memp.txt contains the output.


Monitoring examples

Here are some examples that show you how the db2pd tool can help you to monitor your database environment.

Example 1:

If you want to know the current DB2 level and the current operating system information, you can enter the following command:

db2pd -version -osinfo
Figure 8. Monitoring example 1
Monitoring example 1

The -version option shows the current version and level of DB2 running on the system. You can get the same information by typing db2level command. The -osinfo option shows the OS, CPU, physical and virtual memory information. The similar OS information can also be found in the db2diag.log at DB2 start up time. This example also illustrates how you can easily obtain both version and OS information by simply specifying the two options in one db2pd command.

Example 2:

When you want to determine who is holding a lock in your database, you can use the following command to locate the person:

db2pd -database sample -locks -transactions -agents -file lock.txt

All options for db2pd command can be abbreviated by the first three characters with two exceptions, namely -mempools and -memsets. Using the abbreviations, the above command can be rewritten as follows:

db2pd -db sample -loc -tra -age -fil lock.txt

The above command uses mixed scope options. The -lock/-loc and -transactions/-tra are database scope options, and the agents/-age is an instance scope option. Even though the scope options are mixed, you can still display the locking and transaction information for the currently active agent. Using the -file/-fil option, the output of the command is stored in the lock.txt file. The messages of the command are shown as follows.

Figure 9a. Monitoring example 2
Monitoring example 2

It should be noted that the -db option is ignored by the -agents option.

The following listing (Figure 9b) shows the partial output of the lock.txt file.

Figure 9b. Listing from lock.txt file
Listing from lock.txt file

You can easily identify who is holding the lock using the following steps.

  1. In Figure 9b, the first section of the output produced by the -lock option indicates that a share lock (Mode=S) is held by a transaction handle number 3 (TranHdl=3), and the status of lock is granted (Sts=G).
  2. The second section of the output produced by the -transactions option indicates the transaction handle number 3 has an application handle number 28 (AppHandl=28).
  3. Finally, the user id POONS who has the application handle number 28 can be found in the third section of the output.

When a transaction is waiting for a locked resource, the status (Sts) in the -locks output will be W which stands for Waiting status. Then, using the above approach, you can easily identify who is waiting for a locked resource.

This example shows that one simple command can retrieve the necessary information to determine who is holding a lock. It also shows how you can route the output from multiple options to a single file, and shows the abbreviations of the options. When you are familiar with the db2pd utility and you want to execute a quick command, you may use the abbreviated command to save a few keystrokes. However, for clarity, you may want to avoid the use of abbreviated commands in a script file.

Example 3:

When you want to verify the current isolation level of a dynamic SQL statement, you can use the following command:

db2pd -db sample -dynamic
Figure 10. Monitoring example 3
Monitoring Example 3

In Figure 10, you can find the current isolation of an executing dynamic SQL statement in the Dynamic SQL Environments section. In this example, the hashed anchor identifier 171 (AnchID=171) has the most restrictive isolation level, Repeatable Read (RR). By cross referencing with the Dynamic SQL Statements, you can identify the exact SQL statement which has the RR isolation level:

select * from employee

Example 4:

When you want to monitor the reorganization (reorg) status of a table, you can use GET SNAPSHOT FOR TABLES ON database to check the reorg status before v8.2. With the db2pd utility, you can simply get the reorg status by executing the following command:

db2pd -db sample -reorg file=reorg.txt

The reorg.txt file contains the following information.

Figure 11a. Monitoring initial reorg status
Monitoring initial reorg status

In Figure 11a, an offline reorg utitlity has been started on the table STAFF. If you execute the same command later as shown in Figure 11b, the status has been changed from "Started" to "Done". The "Done" status indicates that the reorg utility has been completed.

Figure 11b. Monitoring final reorg status
Monitoring final reorg status

Example 5:

In earlier versions of DB2 UDB prior to V8.2, you could check the state of a tablespace by using the command DB2 LIST TABLESPACES SHOW DETAIL. With V8.2, you can simply issue the following command from the operating system command prompt.

db2pd -db sample -tablespace

In Figure 12, you see three table spaces, namely SYSCATSPACE, USERSPACE1, and SYSTOOLSPACE, in the SAMPLE database with 0x00000800 tablespace state in the Tablespaces section.

Figure 12a. Monitoring example 5
Monitoring Example 5

You can use the db2tbst command to decode the tablespace state. You'll discover that the tablespaces are in "Backup in progress" state. The result is shown in Figure 12b.

Figure 12b. db2tbst command
db2tbst command

Summary

The new db2pd tool provided in DB2 UDB V8.2 is a very handy tools for database administrators. These examples will help you to understand the tool and demonstrate its potential. By exploring the various options of the db2pd tool, you can acquire a better understanding and begin using it to ease your database administration tasks.

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
ArticleID=86533
ArticleTitle=The db2pd tool
publish-date=04072005