db2pd origins
. After the acquisition of Informix, IBM converted the "onstat" tool and put it into DB2 version 8.2 as "db2pd".
. db2pd runs quickly without any locks or latches and runs outside of the DB2 engine. No database connection is necessary.
. This makes it possible to capture information that changes quickly without negatively impacting the system.
. Benefits include the collection of information without latching, faster retrieval, and no competition for engine resources.
View Bufferpool Statistics
These include current size, hit ratio, direct reads, direct writes.
db2pd -db dbname -bufferpools
View Tablespace Statistics
. Includes total pages, usable pages, used pages, and free pages
. Easily see if automatic resize is turned on and if it is an automatic storage tablespace
db2pd -db dbname -tablespaces 3
View Index statistics
. These include size of indexes, scans, Index only scans, leaf splits.
. Send output to a file usually.
db2pd -db dbname -tcbstats index
View Table statistics
View the table size,scans,page reorgs,inserts,updates and deletes.
db2pd -db dbname -tcbstats
Troubleshoot Locking
db2pd -db dbname -wlocks show detail
db2pd -db dbname -apinfo 2047
View Dynamic SQL
View the Dynamic SQL statements that are in the package cache.
Consider flushing the package cache fist to catch new statements. (db2 flush package cache dynamic)
db2pd -db dbname -dynamic -file filename
Check version of DB2 and OS
Wondering about the DB2 and OS version along with memory usage and CPU load?
db2pd -version -osinfo
Check database configuration settings
Quickly check the database or instance configuration settings and see what’s saved in memory and disk. Good when many settings are set to AUTOMATIC because values come back with current numbers.
db2pd -db dbname -dbcfg
View HADR status
View Role, State, LogGapRunAvg, Primary log file, Standby log file.
db2pd -db dbname -hadr
DB2 Memory Use
Monitor DB2 Memory use at a high level.
db2pd -dbptnmem
Determine whether instance or database is up
"db2pd -" tells whether instance is up and for how long
$ db2pd -
Database Member 0 -- Active -- Up 110 days 13:47:41 -- Date 2016-11-02-14.31.26.294249
$
"db2pd -db -" tells how long the database has been active
$ db2pd -db sample -
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 16:51:09 -- Date 2016-11-02-14.32.34.726288
$
$ db2pd -alldbs -
Database Member 0 -- Database WLDB -- Active -- Up 110 days 04:25:21 -- Date 2016-11-02-14.32.58.518864
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 16:51:33 -- Date 2016-11-02-14.32.58.560294
$

View running utilities
. db2pd -utilities
Use the –repeat flag to run the command iteratively
Repeat Option: db2pd –rep [num sec] [count]
. db2pd –option –repeat 5 5
Use the –file option to send output to a filename
. db2pd –options –file filename
Examples:
.db2pd -age -rep 10 3
.db2pd -age file=agents.out -rep 10 3
.db2pd –db sample –loc –tra –age –fil lock.txt
For multi-partitions
. db2pd –options –alldbpartitionnums
. db2pd –options –dbpartitionnum 4