Skip to content

db2pd: Rapidly Evolving DB2 Performance Tool

db2pd is a relatively new DB2 LUW performance monitoring and troubleshooting tool introduced with DB2 version 8.2. Because it retrieves information from DB2 memory sets, it does not issue latches and is therefore a very low-cost means of monitoring DB2.

Prior to the release of V9.1 Fix Pack 6, it is required that you be instance owner to run the db2pd utility1. As of V9.1 Fix Pack 6, one of the following is required:

  • The SYSADM authority level.
  • The SYSCTRL authority level.
  • The SYSMAINT authority level.
  • The SYSMON authority level.2

The environment variable DB2PDOPT can be used to feed db2pd options without command line arguments.

Running db2pd without arguments takes you to a shell similar to the command line processor in version 9, where apparently most options can be entered at the prompt exactly as the option is used as an argument or value in DB2PDOPT. You can leave the shell by entering q, -q, -quit or quit. This is first documented in the version 9 db2pd docs but was implemented in the initial 8.2 release.

Table of Contents

Where to find db2pd Documentation

Both functionality and documentation of the db2pd utility continue to improve. In version 8.2 the PDF Command Reference section on db2pd was 27 pages long. By version 9 that PDF section had expanded to 39 pages, and by 9.5 was up to 53 pages.

Version 8

Information Center (HTML)

DB2 Command Reference (PDF) pp. 158-184.

Version 9

Information Center (HTML)

DB2 Command Reference (PDF) Page 184-222.

Version 9.5

Information Center (HTML)

DB2 Command Reference (PDF) pp. 666-718.

Comparison with other Monitoring Tools

db2pd possesses advantages and disadvantages with respect to older DB2 System Monitor tools such as snapshot, snapshot administrative views, snapshot table functions, and event monitors.

Advantages

  • First DB2 performance and diagnostic tool offering a shell prompt similar to the db2 CLP.
  • Does not require minimum connection state, so it is possible to obtain information even when connections are hung, and automation does not need to routinely issue a successful connect.
  • Offers an internal repetitive refresh similar to ping, vmstat.
  • Does not issue a latch, making its use very low impact.
  • Reports include some data not available from System Monitor.
  • db2pd reports can be much smaller than System Monitor output.
  • Easy and low risk utility automation.

Disadvantages

  • Prior to version 9 Fixpack 6 db2pd is required to be run as DB2 instance owner.
  • In a high transaction environment db2pd reports can be overwhelmingly large and difficult to interpret.
  • Junior database administrators may have great difficulty understanding the output; I would consider this an advanced utility for senior level people. Some db2pd output is very tough to plow through; it depends on what you are doing.
  • Utility is relatively new and documentation could be better.
  • db2pd cannot be accessed via SQL (unlike snapshot administrative views and table functions).
  • More complicated reports may require parsing by shell script, Perl, etc.

New Features

db2pd has been augmented steadily since its introduction.

db2pd Version 8.2 Command Options

In 8.2 the following command options were available:

-inst
-help
-version
-dbpartitionnum
-alldbpartitionnums
-database
-alldatabases
-file
-everything
-command
-interactive
-full
-repeat
-applications
-agents
-transactions
-bufferpools
-logs
-locks
-tablespaces
-dynamic
-static
-fcm
-mempools
-memsets
-dbmcfg
-dbcfg
-catalogcache
-sysplex
-tcbstats
-reorg
-recovery
-reopt
-osinfo

db2pd Version 9 Command Options

In version 9, -db is now synonym for -database
The -alldbs is now a synonym for -alldatabases

In version 9 the following additional options were made available:

-dump
-fmp
-hadr
-memblocks
-pages
-stack
-storagepaths
-utilities

db2pd Version 9.5 Command Options

Version 9.5 gives us these command options not available under 8.2 or 9:

-activestatements
-apinfo
-dbptnmem
-dmpftbl
-edus
-fvp
-ha
-latches
-serviceclasses
-statisticscache
-thresholds
-wlocks
-workactionsets
-workclasssets
-workloads

db2pd as part of your monitoring strategy

Much of the information available via db2pd is also available via other DB2 utilities, commands, administrative table functions and views; and event monitors. How does db2pd fit into your overall performance monitoring strategy?

  • Use of the utility in scripts and other automation is very simple and easy. Quick shell scripts for monitoring and troubleshooting can be completed and tested in minutes. Use of snapshot objects and event monitors require considerably more preparation, as well as a valid connection state.
  • db2pd requires no instance attachment or database connection state, implicit or otherwise. If you want to run an alert script that might run under any condition, including in hang-wait situations where database connect requests are hanging, this utility is the most certain to complete and return useful information.
  • Because latches are not issued, the performance cost of db2pd is not great. Therefore it is ideal to use when triggered by 24×7 automated shell script alerts that might be triggered under high database stress. The utility itself will not introduce additional problems for the database. When your system is redlining, this is the safest utility to use.
  • db2pd does not have a SQL interface such as what snapshots have with administrative table functions and views. For longitudinal database performance, inserting to historical performance table from Snapshot Administrative Views is much easier.

References


Footnotes

  1. Thanks to Ian Bjorhovde (idbjorh on twitter) for noting the instance owner requirement. Please note that DB2 version 9 documentation on db2pd neglects to document this requirement. []
  2. DB2 Information Center version 9 []

5 Comments

  1. This is a very good article. I appreciate the summary of available command options at each release level.

    I’d like to add two disadvantages to db2pd:

    1) Because db2pd operates outside of the DB2 engine, its utilization is not audited. Since you can see SQL statement texts containing literal values (such as credit card numbers and identity information), security and audit professionals should be aware of this exposure.

    2) db2pd, from the beginning, has been promoted by IBM as a “quick and dirty” utility that is subject to frequent change. From fixpack to fixpack or release to release, the outputs of command options can change. Due to this potential output volatility, any scripts you write to parse the output may need to be maintained/updated.

    Thanks again for a great article.

    Regards,
    Scott Hayes
    President & CEO, DBI
    IBM GOLD Consultant
    IBM Data Champion
    http://www.DBIsoftware.com

    Posted on 02-Jan-09 at 11:12 | Permalink
  2. Thanks Scott for the nice feedback!

    Posted on 06-Jan-09 at 11:22 | Permalink
  3. rakesh

    Yes, indeed a great post…..
    i stumbled upon this from Scott’s performance hero article on ibmdatabasemagazine….and glad to see scott’s feedback here…..
    i just wonder why isn’t here so much buzz in comment’s.
    I’m a Newbie in DB2 on AIX…and i found such articles wonderful and it really helped me to get on board fast.
    Thanks for the post!

    Posted on 09-Feb-09 at 00:57 | Permalink
  4. Thanks Rakesh for your comment. I think my blog is not well known yet – hopefully comment volume will pick up over time.

    Posted on 09-Feb-09 at 12:33 | Permalink
  5. Vinesh S P

    Excellent work Jeffrey!!
    Detailed and really helpful.
    Keep ’em coming.

    Posted on 01-Jul-09 at 00:48 | Permalink

Post a Comment

Your email is never published nor shared. Required fields are marked *
*
*