Blogs

The DB2 Problem Determination Tool

How to use db2pd to find out what’s really going on

DB2 for LUW Offering Manager, IBM

Sooner or later, every DBA encounters problems. Consequently, a skill that every DBA must possess is the ability to perform a logical, systematic search of a database system for the source of any problems that might arise. Such a search often begins by answering basic questions like, “Where does the problem appear to be happening?” and “Under which conditions does the problem occur?” These types of questions help isolate the problem and provide a frame of reference in which you can limit your investigation. And in many cases, you must collect diagnostic data and then analyze that data to find a resolution.

One of the best ways to collect diagnostic data in DB2 for Linux, UNIX, and Windows (LUW) database environments is by using the DB2 Problem Determination tool (otherwise known as db2pd). In this column, I’ll describe how this tool works, and I’ll show you an example of how it can be used to troubleshoot a performance problem.

The DB2 problem determination tool

The DB2 Problem Determination tool (db2pd) is designed to retrieve diagnostic information about a DB2 environment. Instead of utilizing snapshots, this tool attaches directly to DB2 shared memory sets to collect system and event monitor information. Because it doesn’t go through the DB2 engine, db2pd doesn’t need to compete for resources, making it very lightweight and efficient. And since db2pd works directly with memory, it can retrieve data quickly and in a very non-intrusive manner. The only downside is that it may encounter data that is in the process of being changed at the same time that it’s being collected. Hence, data may not always be retrieved. (A signal handler is used to prevent db2pd from aborting abnormally when changing memory pointers are encountered; instead of aborting or reporting erroneous data, messages like “Changing data structure forced command termination” are returned in the output produced.)

Like the DB2 Command Line Processor, the DB2 Problem Determination tool can be run in interactive mode or directly from an operating system command prompt. To run db2pd in interactive mode, simply execute the command db2pd or db2pd -interactive. When either command is executed, you will be presented with a db2pd> command prompt, along with instructions on how to get information on db2pd’s use. From the db2pd> command prompt, enter one or more db2pd command options to collect the desired information; when you’re ready to exit the tool, enter q. On the other hand, to run the tool from an operating system command prompt, merely execute the command db2pd, followed by one or more of the options supported.

Alternatively, you can store the desired options in an ASCII-formatted file or the DB2PDOPT environment variable and have db2pd retrieve them during execution. For example, to retrieve a set of db2pd command options from a file named PD_COMMANDS.TXT, you would enter a command (from the operating system command prompt) that looks like this:

db2pd -command pd_commands.txt

More than 50 commands and options are available and if you want to use them all, you can do so by executing the command db2pd –everything. This will cause db2pd to retrieve diagnostic information for all databases on all database partition servers. If you want to limit the type of information that gets collected, you must specify one of the following scope options:

  • -inst
    Specifies that only instance-level information is to be collected and displayed
  • -database | -db | -d [DatabaseName]
    Specifies that only database-level information for the database specified is to be collected and displayed
  • -alldatabases | -alldbs
    Specifies that only database-level information for all available databases is to be collected and displayed
  • -dbpartitionnum [PartitionNumber]
    Specifies that db2pd is to run on the database partition server specified
  • -alldbpartitionnums
    Specifies that db2pd is to run on all active database partition servers in the instance (db2pd will report only information from database partition servers on the same physical machine that it is being run on)

Two other handy options are –repeat and file=. With the former, you can repeatedly collect information at regular intervals; the latter tells db2pd to send all output to a specific external file.

>Troubleshooting with db2pd

So just how can db2pd help you isolate the source of a problem? Suppose a database that resides on a storage area network (SAN) storage array exhibits poor performance every time a certain query is run against it. You suspect the poor performance is because a significant amount of disk I/O is being performed to satisfy the query. To test your hypothesis, you need to monitor buffer pool activity before, during, and after the query executes. One way to monitor this activity is by using the snapshot monitor; another is by using db2pd.

You can get a quick summary of all activity in all buffer pools for a database by executing a command that looks like this:

db2pd –db [DatabaseName] –bufferpools

The output produced by this command will contain, among other things, the following information:

  • Buffer pool ID, name, and page size
  • Number of table spaces using the buffer pool
  • Current size of the buffer pool (in pages)
  • Number of logical data page reads
  • Number of physical data page reads
  • Hit ratio for data pages
  • Number of logical index page reads
  • Number of physical index page reads
  • Hit ratio for index pages
  • Number of pages prefetched into the buffer pool, but never read

The hit ratios for data and index pages reflect the number of times a page request was handled by the buffer pool directly without requiring disk I/O. The more page requests that can be satisfied by a buffer pool, the better query performance will be. So, if hit ratios are low, a significant amount of disk I/O is taking place.

Some information db2pd can provide that you can’t easily get from other monitoring tools is the actual contents of a database’s buffer pools. Consequently, if a buffer pool’s hit ratio is lower than expected, you can examine the contents of that buffer pool by executing a command that looks like this:

db2pd –db [DatabaseName] –pages [BufferPoolID]

This command will tell you what objects are stored in the buffer pool specified, as well as how many data, index, long field, large objects (LOBs), and XML pages are currently in the buffer pool for a given object. To correlate table names to object IDs, execute the following command and note the IDs assigned to each table (only tables that have been accessed will be shown in the output produced):

db2pd –db [DatabaseName] –tcbstats

Along with table IDs, this command will show you the number of full table scans that have been executed, as well as the number of insert, update, and delete operations that have been performed on each table.

By comparing the objects being referenced by the query that’s exhibiting poor performance with the information collected with db2pd, you can easily determine if a large amount of disk I/O is the source of the performance problem. And once you have identified the source, you can take the proper steps to remedy it.

With more than 50 options to choose from, using db2pd can be a little intimidating at first. But with a little experimentation, and a close examination of the information provided for the db2pd command in the DB2 Information Center, you should be able to use this tool effectively in a relatively short amount of time. (The material in the Information Center will help you decipher the output produced when different options are used.) And hopefully, the next time a problem arises in your database environment, you’ll be able to take advantage of this tool to resolve the problem quickly.