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.