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.
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
2. Search "db2pd" as shown Figure 2.
Figure 2. Search results of DB2 command dp2pd 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
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
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
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
Figure 5. 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
-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
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
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
Alternatively, you can limit the scope of the information by specifying the scope options shown
in Table 1.
|Instance||-inst or -ins||for an instance|
|Database||-database x or -db x||for a specific database x|
|Database||-alldatabase or -alldb||for all databases|
|Partition||-dbpartitionnum n or -dbp n||for a specific database partition server n|
|Partition||-alldbpartitionnums or -alldbp||for 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.
|agents||Returns information about agents||Instance|
|applications||Returns information about applications||Database|
|bufferpools||Returns information about the buffer pools||Database|
|Catalogcache||Returns information about the catalog cache||Database|
|dbcfg||Returns the settings of the database configuration parameters||Database|
|dbmcfg||Returns the settings of the database manager configuration parameters||Instance|
|dynamic||Returns information the execution of dynamic SQL||Database|
|fcm||Returns information about the fast communication manager||Instance|
|help||Returns help information of the db2pd command||NA|
|logs||Returns information about the logs||Database|
|locks||Returns information about the locks||Database|
|mempools||Returns information about the memory pools||Both|
|memsets||Returns information about the memory sets||Both|
|osinfo||Returns information about the operating system||Instance|
|recovery||Returns information about recovery activity||Database|
|reopt||Returns information about Cached SQL statements that were reoptimized using REOPT ONCE option applications||Database|
|reorg||Returns information about table reorganization||Database|
|static||Returns information about the execution of static SQL and packages||Database|
|sysplex||Returns information about the list of servers associated with the database alias for all databases or for a particular database||Instance|
|tablespace||Returns information about the table spaces||Database|
|tcbstats||Returns information about tables and indexes||Database|
|transactions||Returns active transaction information||Database|
|version||Returns information about the current DB2 version and level||Instance|
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.
Here are some examples that show you how the db2pd tool can help you to monitor your database environment.
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
-version option shows the current version and level of DB2 running on the system. You can get the same information by typing
-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.
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
-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
-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.
-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
It should be noted that the
-db option is ignored by the
The following listing (Figure 9b) shows the partial output of the lock.txt file.
Figure 9b. Listing from lock.txt file
You can easily identify who is holding the lock using the following steps.
- In Figure 9b, the first section of the output produced by the
-lockoption indicates that a share lock (
Mode=S) is held by a transaction handle number 3 (
TranHdl=3), and the status of lock is granted (
- The second section of the output produced by the
-transactionsoption indicates the transaction handle number 3 has an application handle number 28 (
- 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.
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
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
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
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
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
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
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.
- Download the latest versions of
DB2 Express-C and DB2 for Linux, UNIX, and Windows.
- Get more information from the
DB2 for Linux, UNIX, and Windows Information Center.