Using the db2pd Command

Dean Compher

29 June 2012

 

 

This article is for all of you command line freaks out there.  I am one of you!  The db2pd command is very useful for monitoring and troubleshooting your database and generally looking at information about it.  However, many people do not even know that this valuable command exists.  You can use it to review the status of various aspects of DB2 including HADR and WLM, look at performance information such as memory use and page cleaning, and many other things.  It is very handy because you can easily define the scope of the options you choose.  Options can be run against a single database, several databases, everything on a server or even all the instances in a cluster.    In this article I will cover some of the more useful aspects of the tool by functional area.

 

This article will describe several of the useful features of the db2pd command and shows some examples, but does not show you all of the options for each function or their usage notes. For that please see the db2pd command section in the DB2 Information Center. This link also provides many more examples. 

 

General Parameters

 

Option

Description

-file <file_name>

Specifies the name of an output file. 

-command <file_name>

Allows you to put your favorite sets of options into files and execute db2pd using those files as input. 

-h

Show parameter syntax with a short description of each option.

-repeat <number, seconds>

Repeats the command the specified number of times waiting the specified number of seconds between each execution.  This is useful for several of the performance options where watching a trend is more useful than a single snapshot in time. 

-everything

Runs the db2pd command with all options against all databases on all partitions on the local server

-full

Expand all output to its maximum length.  Otherwise some verbose output can be truncated. 

 

 

Scope Options

A number of the options for the db2pd command can be run at different levels of the database/instance/cluster structure.  For example, you can run some of the commands that display memory use to show a summary of the instance memory, tell it to show the instance an all databases in that instance or just show the memory for one database. 

 

Option

Description

-alldbs

Specifies that the command being used runs for all databases

-db <db_name> 

 Specifies that the command being run is for the specified database.

-everything

Runs the db2pd command with all options against all databases on all partitions on the local server

-inst

Specifies that the command being run is for everything in the current instance.

-alldbpartitionnums

DPF: Output for the db2pd command will be shown once for every partition in the database.

-dbpartitionnum <number>

DPF:  Output for the db2pd command will be shown once for the specified partition or range of partitions specified.

-allmembers

pureScale:  Output for the db2pd command will be shown once for every member in the cluster.

-member <member_nbr>

pureScale:  Output for the db2pd command will be shown once for the specified member or range of members specified.

-host <hostname>

pureScale:  Output for the db2pd command will be shown once for each member on the host or set of hosts specified. 

 

 

 

 

HADR Status

Check the status of your HADR database cluster anytime to see things like statistics, the peer status and how far behind the secondary databases are.   The –hadr option also shows all of the HADR settings you configured in the database configuration.  

 

Option

Description

-ha

Reports high availability statistics

-hadr 

 Shows the role and peer status of each member, how far behind each member is and the relevant HADR DB CFG settings.    Can be issued from the primary or any secondary server. 

 

 

 

 

Example:  db2pd –db SAMPLE –hadr

 

pureScale Options

 

Option

Description

-cfinfo

Shows information about the Cluster Facility.  This information is useful for performance tuning operations like global buffer pool statistics.  There are several options to narrow the amount of data returned. 

-cfpool

Displays information about each connection pool for the current member.  Can be used to tune and balance HCA card traffic

-sysplex

Returns a list of servers for the database and gives some information about them. 

-ha

Reports high availability statistics

 

-serverlist

Shows the list of hosts in the pureScale cluster and some information about them. 

-totalmem

Shows the amount of memory used by the members on the host including the amount in use for each “restart light” process.

-host

Output for the db2pd command will be shown once for each member on the host or set of hosts specified. 

-member <member_nbr>

Output for the db2pd command will be shown once for the specified member or range of members specified.

-allmembers

Output for the db2pd command will be shown once for every member in the cluster.

 

Example:  db2pd –db PSDB –cfinfo 128 perf

 

Display Configuration Settings

 

Option

Description

-dbcfg

Shows the Database Configuration for all databases   (-alldbs) or the specified database.

-dbmcfg

Shows the instance (Database Manager) configuration for the instance.

-diagpath

Shows the path to the diagnostic log files.  For pureScale it shows the paths for the members and CFs.  It can be qualified for a particular member or CF.

-v

Shows the DB2 version running.  Provides information similar to the “db2level” command.

 

Example:  db2pd –v -dbmcfg

 

Display OS and Hardware

 

This is an especially useful command!

 

Option

Description

-osinfo

Shows static and dynamic information about the OS and HW like OS version and number of CPUs plus dynamic information like current CPU use and amount of swap used.  It can be used with the repeat command to check cpu and memory use over time.

 

Example:  db2pd –osinfo –repeat 5 5

 

Status

 

Option

Description

-quiesceinfo

Show if the instance or database is in a quiescent state.

-temptable

Shows what temporary tables exist along with statistics about the compression of those tables. 

-tcbstats

Shows information about tables including table id, the number of updates, inserts, and deletes performed and information about compression.  Gives information similar to the table snapshot

-logs

Shows information about the status of the transaction logs including archive status. 

-gfw

Shows the event monitors that are currently active in the database and if any have been deactivated.  It also shows statistics and information about the targets where the event monitors are writing information. 

 

Example:  db2pd –db SAMPLE -logs

 

Utilities Running

These options show you what utilities are running in your system and provides information about them. 

 

Option

Description

-utilities

Shows the utilities like LOAD and RUNSTATS currently running in the database and information about them like start time and utility state.

-load

Shows information about the specified running load command with information like start time, table name and the load phase. 

-reorgs

Shows the reorg utilities currently being run in the database and the table, partition information and status information for each reorg.  Make sure to include the “index” option to see information about index reorganizations. 

-recovery

Shows the status of a recovery whether you are specifically doing a roll forward recovery, or a crash recovery is being done because you started a database that went down as part of a server crash.  Allows you to see if anything is happening. 

-runstats

Shows the status of any RUNSTATS are being done in your database. 

 

Example:  db2pd –db SAMPLE –utilities

 

Applications and Agents

See what is currently running in your database and by whom.

 

Option

Description

-applications

Similar to the LIST APPLICATIONS command.  Shows a list of applications connected to the database or instance.

-apinfo

Similar to the GET SNAPSHOT…APPLICATIONS command.  Shows the applications and a lot of details about what the application is doing and lots of statistics about the resources the application has used since the connection was first made.

-agents

An agent is the thread that runs the statements on behalf of the user.  An agent is assigned to an application when a user is connected.  This option shows the agent threads and information about them.  You can specify an individual agent if you just need information about one.  

-activestatements

Display the statements that are currently being executed in the database, some information about them, and which application is executing them. 

-transactions

Shows the active transactions in the database and some information about them including execution time, the number locks held and transaction log information. 

 

Example:  db2pd –db SAMPLE –transactions –repeat 5 5

 

Performance

Much of the information that you get from snapshots, you can get from the db2pd command.  The advantage of using db2pd is that it consumes fewer system resources to get the same information. 

 

Option

Description

-statisticscache

Shows you information about the statistics cache at the database level.  Using the detail option with it, you can see statistics about individual tables collected by real time statistics. 

-cleaner

Shows you what the page cleaners are up to and statics about dirty pages. 

-sort

Shows detailed information about the sorts happening in the database including the amount of sort heap being used and the number of rows spilled to disk.  You can specify a particular application to see just the sort information for it. 

-dynamic

Shows summary information about the dynamic package cache and information about each statement in the cache.  You can use this view for things like the package cache hit ratio and the number of times a statement has been executed.

-catalogcache

Shows summary information about the catalog cache including size and high water mark plus detailed statics about tables and tablespaces in the database. 

-scansharing

Scan Sharing can significantly improve performance of databases that have queries that frequently scan through data in the same tables.  This option shows statists about how well scan sharing is doing in your environment. 

-edus

This option only works on UNIX systems.  It shows statistics about the various threads (Engine Dispatchable Units) that perform the major functions of DB2 including logging, page cleaning and pre-fetching. 

-fmpexechistory

Shows the execution history of your fenced routines. 

 

Example:  db2pd –db SAMPLE -cleaner

 

Locking

 

Option

Description

-locks

Shows the information about locks currently held in the system. 

-wlocks

Shows transactions waiting on locks and the owners of the locks.  This is the most useful option when trying to discovery why something is waiting for a lock. 

-latches

Shows information about all latch holders and latch waiters in the database. 

 

Example:  db2pd –db SAMPLE –wlocks –repeat 5 5

 

Storage

These options show you information about your storage groups and tablespaces. 

 

Option

Description

-storagegroups

Shows information about each storage group including its status and the paths (containers) defined to it. 

-storagepaths

Shows information about the paths defined to storage groups. 

-tablespaces

Displays static information about the table space as defined and dynamic statistics about the tablespace. Static information includes the page size of the tablespace and the type of tablespace.  There is a significant amount of statistical information about the tablespace including the tablespace state and number pages used.

 

Example:  db2pd –db SAMPLE –tablespaces

 

Memory

The memory related parameters show you the amounts of memory your instance or database is using and how well it is using the buffer pools.  The “mem” prefixed options show instance or database level information and have many different optional sub-parameters that you should review before using them that are described in the db2pd command

 

Option

Description

-memsets

Shows information about the memory sets.

-mempools

Shows information about the memory pools.

-memblocks

Shows information about the memory blocks.

-bufferpools

Shows information about how the buffer pool is defined as well as many dynamic status and performance statistical details.  Static information includes page size and whether the size is set to automatic.  A lot of current state information is included like current size and hit ratio.  This option displays information similar to the bufferpool snapshot.

-dirtypages

Shows information and statistics about the dirty pages in all the buffer pools or in a buffer pool that you specify. 

-pages

Shows information and statistics about the pages in all of the buffer pools or in a buffer pool that you specify. 

 

Example:  db2pd –db SAMPLE –bufferpools

 

 

Workload Manager (WLM)

The WLM options in db2pd allow you to monitor the work being done in your system that can in turn be used to adjust your WLM settings. 

 

Option

Description

serviceclasses

Shows information about the service classes that you have defined. 

workactionsets

Shows information about all work action sets. 

workclassets

Shows information about all work class sets that are referenced by an enabled work action set along with other information.

workloads

Shows the list of workload definitions, user privilege holders and other information in memory at the time this command is run. 

thresholds

Shows information about the thresholds that you have created in WLM. 

 

Distributed Partition Facility (DPF)

 

Option

Description

addnode

Shows the progress of the “ADD DATABASE PARTITION SERVER” operation.  It must be run on the partition server that is being added. 

dbptnmem

Shows database partition memory statistics. 

-fcm

Shows information about the fast communications manager that facilitates communication between the partitions.

alldbpartitionnums

Output for the db2pd command will be shown once for the specified partition or range of partitions specified.

dbpartitionnum

Output for the db2pd command will be shown once for the specified partition or range of partitions specified.

 

 

***

 

I hope that this article has given you some new ideas about how you can view information regarding your databases.  If there are any db2pd options that you like that I did not cover here or did not adequately cover, please add any insights to my Facebook Page or my db2Dean and Friends Community

 

HOME | Search