db2pd - Monitor and troubleshoot Db2 database command

Authorization

Required connection

There is no minimum connection requirement. However, if a database scope option is specified, that database must be active before the command can return the requested information.

Command syntax

Notes:

• ¹ The -global parameter has been deprecated. You can use the -member all parameter options to obtain information globally.

Command parameters

-activestatements

Returns information about activities that are currently being processed for connected applications. Examples of such applications include dynamic SQL statements, static SQL statements and loads.

apphdl=appHandle

If an application handle is specified, information is returned about that particular application.

file=filename

Sends the -activestatements output to a specified file.

See the -activestatements usage notes.

-addnode

Returns progress information about the add database partition server operation. This parameter only returns information when issued on the database partition server that is being added. The progress information is persistent on the new database partition server until it is restarted. If issued on an existing database partition server, this parameter returns no information.

See Sample output of the db2pd -addnode command.

-alldatabases | -alldbs

Specifies that the command attaches to all memory sets of all the databases.

-alldbpartitionnums

Specifies that this command is to run on all active database partition servers on the local host. This parameter reports only information from database partition servers on the same physical machine that db2pd is being run on.

-allmembers

Specifies that this command is to run on all active members for a Db2 pureScale® environment. db2pd will only report information from database members on the same physical machine that db2pd is being run on.

-agents

Returns information about agents.

If an agent ID is specified, information is returned about the agent. If an application ID is specified, information is returned about all the agents that are performing work for the application. Specify this command parameter with the -inst parameter, if you have chosen a database that you want scope output for.

event

This option returns metrics for the event being processed by the agent. The metrics returned include the last time that the event was changed, the state of the event, the type of event, the object of the event, and the event object name.

See the agents usage notes.

-apinfo

Displays the detailed information about applications including the execution of dynamic SQL statements of the current unit of work (UOW), if it is applicable.

AppHandl

If an application handle is specified, information is returned about that particular application. The default is to display information for all applications running at that partition.

MaxStmt

If a number of maximum statements is specified, the information for the most recent of SQL statements, equalling the maximum number specified, is returned. The default is to display information for all the executed SQL statements.

file=filename

Sends the -apinfo output to a specified file.

See Sample output of the db2pd -apinfo command.

Note: To capture the past history of a unit of work (UOW) including the SQL statement text for the applications, activate a deadlock event monitor using the statement history clause. For example, use one of the following statements:

create event monitor testit for deadlocks with details history write to file path global

create event monitor testit for deadlocks with details history write to table

The CREATE EVENT MONITOR statement has additional options, such as the ability to specify the name of the table space and table into which data will be written. For details, see the CREATE EVENT MONITOR statement description. The event monitor with statement history capability affects all applications and increases the monitor heap usage by the Db2 database manager.

See the -apinfo usage notes.

-applications

Returns information about applications.

If an application ID is specified, information is returned about that application.

If an agent ID is specified, information is returned about the agent that is working on behalf of the application. See the -applications usage notes.

-barstats

Displays monitoring information about backup and restore operations, and statistical information about performance.

agent_eduid

The agent EDU ID for the backup or restore operation.

See the topic Monitoring backup and restore performance with db2pd -barstats for usage information.

-bufferpools

Returns information about the buffer pools. If a bufferpool ID is specified, information is returned about the bufferpool. See the -bufferpools usage notes.

-catalogcache

Returns information about the catalog cache, which maintains an in-memory version of statistics.

See Sample output of the db2pd -catalogcache command.

The output for SYSTABLES can have multiple entries for the same table (see DEPT in the output shown previously). Multiple entries correspond to a different version of the statistics for the same table. The usage lock name will be unique among the entries for the same object and soft invalid entries will be marked with an 'S'. See the -catalogcache usage notes.

-cfinfo

Dumps CF information that can be useful when diagnosing performance and other issues. You can specify which specific structure you want information to be dumped for by using any of the following sub-options: gbp, sca, lock, or list. For example, running db2pd -cfinfo 128 sca will dump the SCA structure information from CF 128.

-cfpool

Displays a listing of each CF connection pool entry on the current member and its status, including whether it is being used or not, the Db2 Engine Dispatchable Unit (EDU) that is using it, and the function it is being used for.

The cfpool option can be used to monitor command connections and to display Host Channel Adapter (HCA) port mapping information. You can use this information to validate that load balancing between HCA ports is behaving as expected. You can also use this information to verify that HCA failover is working as expected (for example, draining connections from an offline connection, or reestablishing connections after the port comes back online).

In addition, information about the cluster interconnect netname of the HCA port to which the XI and lock notification connections are established is included in the output from db2pd when you use the cfpool option.

-cleaner

Dumps page cleaner related information from a database. This option must be preceded by an active database by specifying the -database or -db option with the proper active database name.

See Sample output of the -cleaner option.

-command filename

Specifies to read and execute the db2pd command options that are specified in the file.

-database | -db | -d databasename

Specifies that the command attaches to the database memory sets of the specified database. Specify the database name, not the alias name.

-dbcfg

Returns the settings of the database configuration parameters. See the -dbcfg usage notes.

-dbmcfg

Returns the settings of the database manager configuration parameters.

Specify this option with the -inst command parameter, if you have chosen a database for which you want scope output. See the -dbmcfg usage notes.

-dbpartitionnum number

Specifies that the command is to run on the specified local or remote database partition server.

-dbptnmem

Lists database partition memory statistics.

-diagpath

Returns the fully resolved split diagnostic path. In a Db2 pureScale environment, this returns the diagnostic path for all members and CFs.

CF|member

Returns the diagnostic path for either the members or CFs. If you do not specify a id, then the diagnostic log paths for all members or CFs are returned.

-dirtypages

Dumps the dirty pages from each bufferpool in the database. This option must be preceded by an active database by specifying the -database or -db option with the proper active database name.

bpID

Specify this option to dump dirty pages from the specified bufferpool.

count

Specify this option to dump the first count number of dirty pages in each bufferpool.

summary

Specify this option to dump recovery related information of each bufferpool.

temponly

Specify this option to dump temporary dirty pages from each bufferpool.

fixedonly

Specify this option to dump dirty pages that are fixed from each bufferpool.

See Sample output of the -dirtypages option.

-dump

Produces stack trace and binary dump files in the diagpath directory. Only available on UNIX operating systems.

• Specify with the all command parameter to produce stack trace files and binary dump files for all agents in the current database partition.
• Specify with the all parameter and an apphdl=appHandle parameter to return all EDUs associated with the specified appHandle application.
• Specify with an EDU ID of eduid and an apphdl=appHandle parameter to return information about the specified EDU if it is associated the appHandle application.
• Specify with an apphdl=appHandle parameter to return just the EDU of the coordinator agent for the appHandle application.
• Specify with the pid option to produce a stack trace file and binary dump file for a specific agent.

You can specify the following parameters with the parameters mentioned previously:

dumpdir=directory

Specifies a directory where the stack files of EDUs running in db2sysc processes are to be redirected. All other stack files are written in the DIAGPATH directory. An absolute path must be specified and the specified directory must exist. This option is available for UNIX and Linux® operating systems only.

timeout=timeout-value

Specifies the time in seconds during which the stack files are redirected to the directory specified.

-dynamic

Returns information about the execution of dynamic SQL. See the -dynamic usage notes.

anch=anchID

If an anchor identifier is specified, information is returned about that particular dynamic SQL.

file=filename

Sends the -dynamic output to a specified file.

-edus

Lists all EDUs in the instance. In the case of a sustained trap, specifying this option outputs the EDU Name indicating that the EDU is suspended.

interval=interval

Only available on UNIX operating systems. If an interval is specified, two snapshots of the EDUs are taken, interval seconds apart. Two new columns are then included in the list of EDUs: USR DELTA which displays the delta of CPU user time across the interval, and SYS DELTA which displays the delta of CPU system time across the interval. If an EDU is added part way through the interval it is included in the list, with the delta calculated from when it was added. If an EDU is deleted part way through the interval it is not included in the list at all.

top=n

Specifies n EDUs that are to be displayed, where n is an integer value. The EDUs that take up the max CPU time during interval specified are displayed first.

stacks

Dumps the stacks for the EDUs that are displayed.

See the -edus usage notes. See also Sample output of the db2pd -edus command.

-encryptioninfo

Displays the database encryption information.

file=filename

Sends the encryption information output to a specified file.

See sample output of the db2pd -encryptioninfo command.

-extentmovement

Displays information about the extent movement status of the database.

See the -extentmovement usage notes. See also Sample output of the db2pd -extentmovement command.

-everything

Runs all options for all databases on all database partition servers that are local to the server.

-fcm

Returns information about the fast communication manager.

• Specify this parameter with the -inst parameter, if you have chosen a database for which you want scope output.
• Specify this parameter with the hwm parameter, to retrieve high-watermark consumptions of FCM buffers and channels by applications since the start of the Db2 instance. The high-watermark consumption values of applications are retained even if they have disconnected from the database already.
• Specify this parameter with the numApps option, to limit the maximum number of applications that the db2pd command reports in the current and HWM consumption statistics.

See the -fcm usage notes.

-file filename

Specifies to write the output to the specified file.

-fmp

Returns information about the process in which the fenced routines are executed. See the -fmp usage notes.

-fmpexechistory | -fmpe

Displays the fenced routine history that attempted to be loaded and executed. Note that this parameter is available starting in Fix Pack 1.

pid=pid

Displays detailed thread information about a specific fenced process ID. If none is specified, detailed information for all processes is displayed. For thread-safe FMP processes, there will be one execution history list per thread, and threads are presented in three groups: Active, Pooled, Forced. For non thread-safe FMP processes, only one execution history list per process is displayed.

tid=tid

Displays historical details for a thread-safe routine using a specific thread ID. For non thread-safe routine, the thread ID value will be 1.

n=n

Use this option to specify the number of routine execution history that is to be displayed for each FMP process. The maximum value is 128. If not specified, only the last routine history is returned by default.

genquery

Generates a select query that will return the routine schema, module, name and specific name according to the routine unique ID.

See the -fmpexechistory | -fmpe usage notes.

-full

Specifies that all output is expanded to its maximum length. If not specified, output is truncated to save space on the display.

-fvp

Displays fenced vendor process information and allows the termination of a fenced vendor process in situations where it is not responding. This applies to backup, restore, prune history, load, load copy (roll forward) and Log Manager, where a vendor media device is being used.

Note: The -database database command parameter must be used in conjunction with this parameter in order to connect to the right memory set to gather the information. This has no effect on Windows operating systems.

agent_eduid

Displays the fenced vendor process information for a Db2 EDU ID of a backup, restore, prune history, load or load copy (roll forward) agent.

LAM1

Displays fenced vendor process information for logarchmeth1.

LAM2

Displays fenced vendor process information for logarchmeth2.

LAM3

Displays fenced vendor process information for the special case where the current log archive method configuration parameter is not set to VENDOR, and so a fenced vendor process needs to be created temporarily, during ROLLFORWARD DATABASE, to retrieve logs from a previous vendor archiving method.

term

On top of displaying fenced vendor process information, this option also terminates the fenced vendor process specified.

-global

Specifies that db2pd will also be run on remote hosts. If the -file parameter is specified, a single file consisting of all the individual files from remote host will be created on the computer from where you issued the db2pd command.

Note: This command parameter is available in Db2 Version 9.8 Fix Pack 3 and later fix packs. This parameter is deprecated in Db2 Version 9.7 Fix Pack 4 and later fix packs.

-dbp database_partition_number

Specifies that db2pd will be run on the remote host of the specified database partition. If no database partition is specified with the -global option, db2pd will run only on the host where member resides.

-gfw

Returns a list of event monitors that are currently active or were deactivated for some reason. It also returns statistics and information about the targets into which event monitors write data for each fast writer independent coordinator.

-ha

Reports high availability statistics.

-hadr

Reports high availability disaster recovery (HADR) information. .

See the -hadr usage notes.

-h | -help

Displays the online help information.

-host hostname

Specifies the host or hosts on which the command is issued. The command is issued for all members that reside on the host. If this option is not specified, the command is issued on the local host. If multiple hosts are specified, all host names must be valid for the command to complete. This option cannot be specified together with the -member option.

-inst

Returns all instance-scope information.

-interactive

Specifies to override the values specified for the DB2PDOPT environment variable when running the db2pd command.

-iperiodic

Returns information about db2iperiodic tasks.

-latches

Reports all latch holders and all latch waiters.

group

Just prints the list of holders followed by the list of waiters.

file=filename

Sends -latches output to filename.

See the -latches usage notes.

-load

Displays all load EDU information. This parameter can be combined with the -host or -member parameter to display host or member specific load EDU information. Requires an active database to be specified.

loadID="LOADID"

Displays all load EDUs working for the specific load operation specified by LOADID. If the LOADID specified does not exist, no information is displayed.

file=filename

Redirects the output, excluding stack files, to the specified file.

stacks

Dumps the stack traces for the load EDUs displayed in the diagpath directory. If this option is used with the loadID option, the stacks are dumped for the load EDUs working for the specified load operation. This option is available for UNIX and Linux operating systems only.

See the -load usage notes.

-locks

Returns information about the locks.

Specify a transaction handle to obtain information about the locks that are held by a specific transaction.

Specify with the showlocks command parameter to return detailed information about lock names. For row and block locks on partitioned tables and individual data partitions, showlocks displays the data partition identifier as part of the row with the lock information. . The showlocks parameter displays the table name and schema name of locks.

Specify the wait command parameter to return locks in a wait state and the owners of those locks.

See the -locks usage notes.

-logs

Returns information about the log files. See the -logs usage notes. See also Sample output of the db2pd -logs command.

This information can also be obtained by running the MON_GET_TRANSACTION_LOG table function.

-member member_number | member_range

Specifies the member or members on which the command is issued. If this option is not specified, the command is issued on the current member. Multiple members can be specified as a comma separated list of member_number (member1, member2), or using member_range, where member_range is a range of members (member1-member3), or using any combination of the first two methods. This option cannot be specified together with the -host option.

all

Specifies that the command is issued on all members, including members on remote hosts.

-membersubsetstatus

Dumps the state of member subsets.

detail

Displays the member subset details in the ascending order of FAILOVER_PRIORTIY

-memblocks

Returns information about the memory sets. Certain memory sets are returned based on the scope that the -memblocks parameter is used in:

• If this parameter is issued with the -inst and -alldbs parameters, information aboutthe dbms, fcm, fmp, appl, and db memory sets is returned. The following command returns information about the instance-scope and database-scope memory sets for all databases:

  db2pd -inst -alldbs -memblocks

• If this parameter is issued with the -inst and -db parameters, information about the dbms, fcm, fmp, appl, and db memory sets is returned for the specified database. The following command returns information about theinstance-scope and database-scope memory sets for the database sample:

  db2pd -inst -db sample -memblocks

• If this parameter is issued within a database scope (-db), information aboutthe appl and db memory sets is returned. The following command returns information about the database-scope memory sets for the database sample:

  db2pd -db sample -memblocks

• If this parameter is issued on its own with the db2pd command, it returns information about the instance-scope memory sets, which include the dbms, fcm, and fmp memory sets. This is the default behavior. The following command returns the same information as db2pd -inst -memblocks which returns information about the instance-scope memory sets:

  db2pd -memblocks

• If this parameter is issued with any of the following parameter options, the information returned is only that options memory set. The following command returns information only on the fmp memory set:

  db2pd -memblocks -fmp

dbms

Only report memory blocks in the database manager system memory set. This memory set is a part of instance-scope memory sets.

fcm

Only report memory blocks in the fast communication manager memory set. This memory set is a part of instance-scope memory sets.

fmp

Only report memory blocks in the fenced mode process memory set. This memory set is a part of instance-scope memory sets.

appl

Only report memory blocks in the application memory set. This memory set is a part of database-scope memory sets.

db

Only report memory blocks in the database memory set. This memory set is a part of database-scope memory sets.

all

Report memory blocks from all memory sets. This includes memory blocks from instance-scope (-inst) memory sets, and, on Windows operating systems only, the private memory set.

Note: The database scope (-db or -alldbs) must be specified to include memory blocks from database-scope memory sets (database and application memory sets).

top

Report the top memory consumers for each set.

blocks

Report the memory blocks for each set.

sort

Report the sorted memory blocks for each pool in each set.

PoolID

Report memory blocks from a specific pool.

pid=pid

Report memory blocks from a specific process id (for UNIX operating systems only).

private

Report memory blocks from the private memory set (for Windows operating systems only).

skipfreedwithpool | skip

Report memory blocks skipping those to be freed with the pool.

See the -memblocks usage notes.

-mempools

Returns information about the memory pools.

Specify this option with the -inst option to include all the instance-scope information in the returned information. See the -mempools usage notes.

-memsets

Returns information about the memory sets.

Specify this command parameter with the -inst command parameter to include all the instance-scope information in the returned information. See the -memsets usage notes.

-osinfo

Returns operating system information. If a disk path is specified, information about the disk will be printed. See the -osinfo usage notes.

-pages

Returns information about the buffer pool pages.

bpID

If bufferpool ID is specified, only pages from the specified bufferpool are returned.

summary

If this option is specified, only the summary information section will be displayed.

See the -pages usage notes. See also Sample output of the db2pd -pages command.

-q | -quit | q | quit

Quit. When the db2pd keyword alone is issued, db2pd runs in interactive mode. The quit command causes an exit from this mode back to the standard command prompt.

-quiesceinfo

Specifies the current quiesce status of the instance and database. This option is only available in Shared Data (SD) configurations.

-recovery

Returns information about recovery activity. See the -recovery usage notes.

-reopt

Returns information about cached SQL statements that were re-optimized using the REOPT ONCE option. See the -reopt usage notes.

-reorgs

Returns information about table and data partition reorganization. When the index parameter is added to the command, index reorganization information is returned along with the table and data partition reorganization information.

Note: Starting with Db2 Version 9.8 Fix Pack 3, the db2pd -reorgs index command reports the index reorg statistics for partitioned indexes in addition to the index reorg statistics for non-partitioned indexes reported since Db2 V9.8 Fix Pack 3.

Note: You cannot monitor the progress of index reorganization operations on a database if you specify the CLEANUP ONLY parameter of the REORG INDEXES command.

See the -reorgs usage notes. See also Sample output of the db2pd -reorgs index command.

-repeat num sec count

Specifies that the command is to be repeated after the specified number of seconds. If a value is not specified for the number of seconds, the command repeats every five seconds. You can also specify the number of times the output will be repeated. If you do not specify a value for count, the command is repeated until it is interrupted.

When the db2pd command with the -repeat parameter is issued on members that are on different hosts, the command is sent from the local host to the remote hosts. The command runs on each remote host until the count value specified is reached. The command runs to completion on one host before it begins on another host.

-runstats

Returns information about the status of the RUNSTATS utility on table and associated indexes. The status of table statistics collection is displayed first, followed by the status of index statistics collection. Sample output of the -runstats option:

db2pd -runstats

Table Runstats Information:

Retrieval Time: 08/13/2009 20:38:20
TbspaceID: 2        TableID: 4
Schema: SCHEMA    TableName: TABLE
Status: Completed     Access: Allow write
Sampling: No          Sampling Rate: -
Start Time: 08/13/2009 20:38:16   End Time: 08/13/2009 20:38:17
Total Duration: 00:00:01
Cur Count: 0                      Max Count: 0

Index Runstats Information:

Retrieval Time: 08/13/2009 20:38:20
TbspaceID: 2        TableID: 4
Schema: SCHEMA    TableName: TABLE
Status: Completed     Access: Allow write
Start Time: 08/13/2009 20:38:17   End Time: 08/13/2009 20:38:18
Total Duration: 00:00:01
Prev Index Duration [1]: 00:00:01
Prev Index Duration [2]:  -
Prev Index Duration [3]:  -
Cur Index Start: 08/13/2009 20:38:18
Cur Index: 2            Max Index: 2            Index ID: 2
Cur Count: 0                      Max Count: 0

-rustatus

Displays the fix pack update status of the system. See Sample output of the db2pd -rustatus command.

-scansharing

Returns scan sharing information about all tables that have table or block index scan sharing in the specified database.

obj=objectID pool=poolID

Returns scan sharing information about the specified table.

all

Returns scan sharing information for all tables. For each table, table or range scan sharing information is returned. In addition, for MDC tables, block index scan sharing information is returned.

index=

indexID

Returns scan sharing information for the specified table, and block index scan sharing information for the specified block index.

all

Returns block index scan sharing information for all block indexes.

See Sample output of the db2pd -scansharing command.

See the -scansharing usage notes.

-serverlist

Returns information about which members are available for use and the relative load of each of those members.

There are instances where no output is returned for one or more databases:

• No active databases exist
• The specified database is not active
• The specified database is active, but the server list has not yet been cached
• The db2pd command is run in an environment that is not a Db2 pureScale environment
• No remote client has connected to the database

See the -serverlist usage notes.

See Sample output of the db2pd -serverlist command.

-serviceclasses serviceclassID

Returns information about the service classes for a database. serviceclassID is an optional parameter to retrieve information for one specific service class. If serviceclassID is not specified, information for all service classes is retrieved.

See the -serviceclasses usage notes. See also Sample output of the db2pd -serviceclasses command.

-sort

Starting with Fix Pack 5, this option returns information about the application sort operation. If an application handle ID is specified, information is returned about the sort operation for the specified application.

See the -sort usage notes.

-stack all | apphdl=appHandle | eduid

In case of an engine hang, you can use the stack trace file to get information about the Db2 state. This command produces stack trace files in the diagpath directory. On UNIX and Linux operating systems, the naming convention of the files is pid.tid.node.stack.txt . On Windows operating systems, the EDU's dump information into the stack trace files with the naming convention pid.tid.stack.bin. Note that -stack all is the only option supported on the Windows operating system.

all

Specify this option to produce stack trace files for all processes in the current database partition.

Note: The all option can require a sufficient amount of memory for it to function properly.

apphdl=appHandle

Specify this option to produce a stack trace file for just the application handle equal to appHandle. This option is available for UNIX and Linux operating systems only.

eduid

Limits output to only EDUs with a specified ID. Formatted events and relevant data are dumped to the pid.tid/EDUID.node.trap.txt trap files in the db2dump directory. This option is available for UNIX and Linux operating systems only.

Event stack will be output in the following order:

Last event (on the top of event stack)

• Event type and short description
• Customer impact
• Object identifier
• ECF ID, probe
• Top event header
• Top event qualifiers (if any)
• Top event data (if present)

First event (on the bottom of event stack)

• Event type and short description
• Customer impact
• Object identifier
• ECF ID, probe
• Bottom event header
• Bottom event qualifiers (if any)
• Bottom event data (if present)

In the preceding list, ECF ID is ECF identifier (will be formatted as product, component, function) and probe is a line of code or some unique number (for a function).

Event flow (recorded event “history”) will be output in the following order:

First event record

• Event type and short description
• Customer impact
• Object identifier
• ECF ID, probe
• Event header
• Object data (if not a string or integer)

Last event record

• Event type and short description
• Customer impact
• Object identifier
• ECF ID, probe
• Event header
• Object data (if not a string or integer)

dumpdir=directory

Specifies a directory where the stack files of EDUs running in db2sysc processes are to be redirected. All other stack files are written in the DIAGPATH directory. An absolute path must be specified and the specified directory must exist. This option is available for UNIX and Linux operating systems only.

timeout=timeout-value

Specifies the time in seconds during which the stack files are redirected to the directory specified.

-static

Returns information about the execution of static SQL and packages. See the -static usage notes.

-statisticscache

Returns information about the statistics cache at the database level.

summary

Summarizes statistics cache. To dump the statistics cache summary for database sample, issue the following command:

db2pd -db sample -statisticscache summary

detail

Specify this option to dump detailed statistics information stored in the statistics cache for all tables with the latest statistics collected by real-time statistics gathering. To dump detailed statistics information stored in the statistics cache for all the databases, issue the following command:

db2pd –statisticscache detail -alldbs

find schema=schema object=object

Specify this option to dump the detailed statistics information for a specific table with schema as schema name and object as table name. To dump detailed statistics information for table USER1.T1 of database sample, issue the following command:

db2pd -db sample -statisticscache find schema=USER1 object=T1

See the -statisticscache usage notes.

-storagegroups

Returns information about the storage groups defined for the database.

Specify with the Storagegroup ID command parameter to display the information about a specific storage group and its paths.

See the -storagegroups usage notes. See also Sample output of the db2pd -storagegroups command.

-storagepaths

Returns information about the automatic storage paths for all storage groups defined for the database. Unlike the storagegroups parameter, this parameter does not accept storage group ID as input.

See the -storagepaths usage notes. See also Sample output of the db2pd -storagepaths command.

-subsetid

-sysplex

Returns information about the list of servers associated with the database alias indicated by the db parameter. If the -database command parameter is not specified, information is returned for all databases.

Specify this command parameter with the -inst command parameter, if you have chosen a database for which you want scope output.

See the -sysplex usage notes.

-tablespaces

Returns information about the table spaces.

Specify with the group command parameter to display the information about the containers of a table space grouped with the table space.

Specify with the Table_space_ID command parameter to display the information about a specific table space and its containers.

Specify with the trackmodstate command parameter to display the state of a table space with respect to the last or next backup. This parameter requires the trackmod configuration parameter to be set to Yes.

See the -tablespaces usage notes. See also Sample output of the db2pd -tablespaces command.

-tcbstats

Returns information about tables and indexes. The total number of updates on tables, the UDI and real-time statistics UDI counters (RTSUDI), are returned as well.

TbspaceID=table_space_ID

Specify this option to display the information about a specific table space.

TableID=table_ID

Specify this option to display the information about a specific table. The TbspaceID option is required when using the TableID option.

nocatalog

Specify this option to display table and index information relating to all non-catalog tables.

See the -tcbstats usage notes.

-temptable

By default, returns the following information about temporary tables:

• Number of Temp Tables The total number of temporary tables created and dropped since the database manager was started or since the last reset of the counters.
• Comp Eligible Temps Temporary tables that the data base manager has determined is eligible for compression based on these three properties: query type, minimum row size, and minimum expected table size.
• Compressed Temps The total number of temporary tables that were actually compressed. This implies that the table has enough data so that a compression dictionary is created for the temporary table.
• Total Stored Temp Bytes The total number of actual row data for temporary tables that is stored on disk. This can be from both compressed and non-compressed temporary tables.
• Total Bytes Saved The total bytes saved by compressing rows.
• Total Compressed Rows A cumulative count of the number of rows that saved at least one byte using compression.
• Total Temp Table Rows The total number of rows inserted into all the temporary tables, whether they are compressed or not. Not all rows inserted into a compressed temporary table are necessarily compressed.

reset

Specify this option to reset all counters to zero.

See the -temptable usage notes. See also Sample output of the db2pd -temptable command.

-thresholds thresholdID

Returns information about thresholds. thresholdID is optional, but specifying a threshold ID returns information about a specific threshold. If thresholdID is not specified, information for all enabled and disabled thresholds is retrieved.

See the -thresholds usage notes. See Sample output of the db2pd -thresholds command.

-totalmem

Returns information about total amount of memory allocated on a Db2 host, specifically:

• the amount of reserved restart light memory preallocated on the host
• the total memory consumption by the host's resident members and guest members

The -totalmem option only reports information about the current host being accessed.

-transactions

Returns information about active transactions. If a transaction handle is specified, information is returned about that transaction handle. If an application handle is specified, information is returned about the application handle of the transaction. See the -transactions usage notes.

-utilities

Reports utility information. Descriptions of each reported element can be found in the utilities section of the Database Monitoring Guide and Reference.

See the -utilities usage notes.

-v | -version

Displays the current version and service level of the installed Db2 database product.

-wlocks

Displays the owner and waiter information for each lock being waited on. In the Sample output of the db2pd -wlocks command, the lock status (Sts) value of G designates the owner of the lock, while a Sts value of W designates the waiter of that lock.

detail

Displays the table name, schema name, and application node of locks that are being waited on.

file=filename

Sends the -wlocks output to a specified file.

See the -wlocks usage notes.

-workactionsets workactionsetID

Returns information about all enabled work action sets and all enabled work actions in these sets.

group

Returns the same information grouped by work action set.

See the -workactionsets usage notes.

-workclasssets workclasssetID

Returns information about all work class sets that are referenced by an enabled work action set and all work classes in the work class sets.

group

Returns the same information grouped by work class set.

See Sample output of the db2pd -workclasssets command. See the -workclasssets usage notes.

-workloads workloadID

Returns the list of workload definitions, user privilege holders, and local partition workload statistics in memory at the time the command is run.

group

Returns the same information grouped by workload.

See Sample output of the db2pd -workloads command.

See the -workloads usage notes.

Usage notes

-activestatements parameter

-agents parameter

-apinfo parameter

-applications parameter

-bufferpools parameter

-catalogcache parameter

See Sample output of the db2pd -catalogcache command.

-dbcfg parameter

For the -dbcfg parameter, the current values of the database configuration parameters are returned.

-dbmcfg parameter

For the -dbmcfg parameter, current values of the database manager configuration parameters including the current effective code level (CECL) and current effective architecture level (CEAL) are returned.

-dynamic parameter

-edus parameter

See Sample output of the db2pd -edus command.

-extentmovement parameter

See Sample output of the db2pd -extentmovement command.

-fcm parameter

-fmp parameter

-fmpexechistory | -fmpe parameter

See Sample output of the db2pd -fmpexechistory command.

-hadr parameter

In a Db2 pureScale environment, the command returns HADR information about log streams being processed by the local member. On a standby, if the command is issued on the replay member, it returns HADR information about all log streams, otherwise, it returns a message indicating that the database is not active on that member and no HADR information. If you use the -allmembers option, it returns the concatenated output from all members. This is one way to tell which member is the replay member. The other way is to look at STANDBY_MEMBER field from primary's monitoring output. If it's issued from a member on the primary, the command returns information about the stream owned by the member and all streams being assisted by the member. To see all of the streams in the cluster, issue the command with the -allmembers option.

Only information relevant to the current settings is shown, so for example if reads on standby is not enabled, information about the replay-only window is not shown.

HADR_ROLE

The current HADR role of the local database. Possible values are:

• PRIMARY
• STANDBY

REPLAY_TYPE

The type of HADR replication of the database. The possible value is:

• PHYSICAL

HADR_SYNCMODE

The current HADR synchronization mode of the local database. Possible values are:

• ASYNC
• NEARSYNC
• SUPERASYNC
• SYNC

Note: The HADR_SYNCMODE value of the standby is shown as an empty string (a zero-length string) until the primary connects to the standby database.

STANDBY_ID

The identifier for all the standbys in the current setup. This value has meaning only when the command is issued on the primary. If you issue it on a standby, it always returns 0 because standbys are not visible to each other. The 1 identifier is always assigned to the standby if there is only one standby. If you have multiple standbys in your setup, 1 indicates the principal standby.

LOG_STREAM_ID

The identifier for the log stream that is being shipped from the primary database.

HADR_STATE

The current HADR state of the database. Possible values are:

• DISCONNECTED
• DISCONNECTED_PEER
• LOCAL_CATCHUP
• PEER
• REMOTE_CATCHUP
• REMOTE_CATCHUP_PENDING

HADR_FLAGS

A string of one or more of the following flags indicating HADR condition:

ASSISTED_REMOTE_CATCHUP

The stream is in assisted remote catchup.

ASSISTED_MEMBER_ACTIVE

During assisted remote catchup, the member on the primary that is being assisted is active. This is an abnormal condition because an active member is expected to connect to standby directly.

STANDBY_LOG_RETRIEVAL

The standby database is interacting with the log archive device to retrieve log files.

STANDBY_RECV_BLOCKED

The standby temporarily cannot receive more logs. Possible causes are:

• When log spooling is disabled, the log receive buffer is full (STANDBY_RECV_BUF_PERCENT is 100%).
• When log spooling is enabled, spooling has reached the spool limit (STANDBY_SPOOL_PERCENT is 100%).
• The standby log device is full (indicated by the STANDBY_LOG_DEVICE_FULL flag). This condition can happen when spooling is enabled or disabled.

In all of these cases, after replay makes progress, more space is released and log receive can resume. .

STANDBY_LOG_DEVICE_FULL

The standby log device is full. This condition blocks log receive until some more space is released as replay proceeds.

STANDBY_REPLAY_NOT_ON_PREFERRED

The current replay member on the standby is not the preferred replay member.

STANDBY_KEY_ROTATION_ERROR

The standby database encountered a master key rotation error. No logs are received until the error is corrected. The system shuts down if the error is not corrected within the timeout period (30 minutes).

PRIMARY_MEMBER_HOST

The local host, indicated by the hadr_local_host configuration parameter, of the member on the primary that is processing the log stream.

PRIMARY_INSTANCE

The instance name of the primary database processing the log stream.

PRIMARY_MEMBER

The member on the primary that is processing the log stream.

STANDBY_MEMBER_HOST

The local host, indicated by the hadr_local_host configuration parameter, of the standby member processing the log stream.

STANDBY_INSTANCE

The instance name of the standby database processing the log stream.

STANDBY_MEMBER

The standby member processing the log stream.

HADR_CONNECT_STATUS

The current HADR connection status of the database. Possible values are:

• CONGESTED
• CONNECTED
• DISCONNECTED

HADR_CONNECT_STATUS_TIME

The time when the current HADR connection status began. Depending on the HADR_CONNECT_STATUS value, the HADR_CONNECT_STATUS_TIME value indicates:

• Congestion start time
• Connection start time
• Disconnection time

HEARTBEAT_INTERVAL

The heartbeat interval in seconds, which is computed from various factors such as the values of the hadr_timeout and hadr_peer_window configuration parameters. The HEARTBEAT_INTERVAL element indicates how often the primary and standby exchange monitor information.

HEARTBEAT_MISSED

Number of heartbeat messages not received on time on this log stream. Messages start accumulating when a database is started on the local member. This number should be viewed relative to the HEARTBEAT_EXPECTED value. For example, 100 missed heartbeats when HEARTBEAT_EXPECTED is 1000 is a 10% miss rate. This miss rate indicates a network problem. However, 100 missed heartbeats when HEARTBEAT_EXPECTED is 10000 is a 1% miss rate and is unlikely to be a network issue. Take the HEARTBEAT_INTERVAL value into account when assessing the HEARTBEAT_EXPECTED value. A short HEARTBEAT_INTERVAL value can cause the HEARTBEAT_MISSED value to appear high even though it is safe.

HEARTBEAT_EXPECTED

Number of heartbeat messages expected on this log stream. These messages accumulate when a database is started on the local member. With the HEARTBEAT_MISSED value, you can determine the health of a network for a given time duration.

HADR_TIMEOUT

The time, in seconds, by which an HADR database must receive a message from its partner database. After this period of time, an HADR database server considers that the connection between the databases has failed and disconnects.

TIME_SINCE_LAST_RECV

The time, in seconds, that has elapsed since the last message was received, so the larger the number, the longer the delay in message delivery. When the TIME_SINCE_LAST_RECV value equals the HADR_TIMEOUT value, the connection between the databases is closed.

PEER_WAIT_LIMIT

The length of time, in seconds, that the primary database waits before breaking out of peer state if logging is blocked waiting for HADR log shipping to the standby. A value of 0 indicates no timeout.

LOG_HADR_WAIT_CUR

The length of time, in seconds, that the logger has been waiting on an HADR log shipping request. A value of 0 is returned if the logger is not waiting. When the wait time reaches the value that is returned in the PEER_WAIT_LIMIT field, HADR breaks out of peer state to unblock the primary database.

LOG_HADR_WAIT_RECENT_AVG

The average time, in seconds, for each log flush.

LOG_HADR_WAIT_ACCUMULATED

The accumulated time, in seconds, that the logger has spent waiting for HADR to ship logs.

LOG_HADR_WAIT_COUNT

The total count of HADR wait events in the logger. The count is incremented every time the logger initiates a wait on HADR log shipping, even if the wait returns immediately. As a result, this count is effectively the number of log flushes while the databases are in peer state.

SOCK_SEND_BUF_REQUESTED,ACTUAL

• The requested socket send buffer size (SOCK_SEND_BUF_REQUESTED), in bytes. A value of 0 indicates no request (the system default is used).
• The actual socket send buffer size (SOCK_SEND_BUF_ACTUAL), in bytes.

SOCK_RECV_BUF_REQUESTED,ACTUAL

• The requested socket receive buffer size (SOCK_RECV_BUF_REQUESTED), in bytes. A value of 0 indicates no request (the system default is used).
• The actual socket receive buffer size (SOCK_RECV_BUF_ACTUAL), in bytes.

PRIMARY_LOG_FILE,PAGE,POS

• The name of the current log file of the log stream on the primary database (PRIMARY_LOG_FILE).
• The page number in the current log file indicating the current log position on the primary HADR database. The page number is relative to its position in the log file. For example, page 0 is the beginning of the file (PRIMARY_LOG_PAGE).
• The current receive log position (byte offset) of the log stream on the primary database (PRIMARY_LOG_POS).

STANDBY_LOG_FILE,PAGE,POS

• The name of the log file corresponding to the standby receive log position on the log stream (STANDBY_LOG_FILE).
• The page number (relative to its position in the log file) corresponding to the standby receive log position (STANDBY_LOG_PAGE).
• The current log position of the standby HADR database (STANDBY_LOG_POS).

HADR_LOG_GAP

The running average, in bytes, of the gap between the PRIMARY_LOG_POS value and STANDBY_LOG_POS value.

STANDBY_REPLAY_LOG_FILE,PAGE,POS

• The name of the log file corresponding to the standby replay log position on the log stream (STANDBY_REPLAY_LOG_FILE).
• The page number in the standby replay log file corresponding to the standby replay log position (STANDBY_REPLAY_LOG_PAGE). The page number is relative to its position in the log file. For example, page 0 is the beginning of the file.
• The byte offset of the standby replay log position on the log stream (STANDBY_REPLAY_LOG_POS).

STANDBY_RECV_REPLAY_GAP

The average, in bytes, of the gap between the standby log receive position and the standby log replay position. If the value of this gap reaches the combined value of the standby’s receive buffer size and the standby’s spool limit, the standby stops receiving logs and blocks the primary if it is in peer state.

PRIMARY_LOG_TIME

The latest transaction timestamp of the log stream on the primary database.

STANDBY_LOG_TIME

The latest transaction timestamp of received logs on the log stream on the standby database.

STANDBY_REPLAY_LOG_TIME

The transaction timestamp of logs being replayed on the standby database.

STANDBY_RECV_BUF_SIZE

The standby receive buffer size, in pages.

STANDBY_RECV_BUF_PERCENT

The percentage of standby log receive buffer that is currently being used. Even if this value is 100, indicating that the receive buffer is full, the standby can continue to receive logs if you enabled log spooling.

STANDBY_SPOOL_LIMIT

The maximum number of pages to spool. A value of 0 indicates that log spooling is disabled; a value of -1 indicates that there is no limit. When the hadr_spool_limit configuration parameter is AUTOMATIC (the default in 11.1), this field returns the computed spool size in units of pages; that is, the actual maximum size of the spool.

STANDBY_SPOOL_PERCENT

The percentage of spool space used, relative to the configured spool limit. If the spool limit is 0 (spooling disabled) or -1 (unlimited spooling), NULL is returned. When STANDBY_SPOOL_PERCENT percent reaches 100%, the standby stops receiving logs, until some more space is released as replay proceeds. Note that spooling can stop before the limit is hit (before STANDBY_SPOOL_PERCENT reaches 100%), if the spool device (standby log path) is full.

STANDBY_ERROR_TIME

The most recent time when the standby database encountered a major error. Check the administration notification log and db2diag.log for error messages that have occurred since the last time you checked for errors. Check the logs fully, not just until the value reported by the STANDBY_ERROR_TIME value. There might be multiple errors. Log entries might include, but are not limited to, the following errors:

• Replay errors taking a table space to an abnormal state
• Load replay errors taking a table to an invalid state

The STANDBY_ERROR_TIME value is reset to NULL when a database changes its role from standby to primary or standard. It is not reset when a standby database is deactivated and reactivated.

PEER_WINDOW

The value of the hadr_peer_window database configuration parameter.

READS_ON_STANDBY_ENABLED

An indicator of whether the HADR reads on standby feature is enabled. Possible values are:

• Y
• N

STANDBY_REPLAY_ONLY_WINDOW_ACTIVE

An indicator of whether the replay-only window (caused by DDL or maintenance-operation replay) is in progress on the standby, meaning that readers are not allowed on the standby. Possible values are:

• Y
• N

PEER_WINDOW_END

The point in time until which the primary database stays in peer or disconnected peer state, as long as the primary database is active. The field is displayed only if you enabled a peer window.

STANDBY_REPLAY_DELAY

Indicates the value of the hadr_replay_delay database configuration parameter.

TAKEOVER_APP_REMAINING_PRIMARY

The current number of applications still to be forced off the primary during a non-forced takeover. This field is displayed only if there is a non-forced takeover in progress.

TAKEOVER_APP_REMAINING_STANDBY

The current number of applications still to be forced off the read-enabled standby during a takeover. This field is displayed only if there is a takeover in progress.

STANDBY_REPLAY_ONLY_WINDOW_START

The time at which the current replay-only window became active. This field is displayed only if there is an active replay-only window on the read-enabled standby.

STANDBY_REPLAY_ONLY_WINDOW_TRAN_COUNT

The total number of existing uncommitted DDL or maintenance transactions that have been executed so far in the current replay-only window. This field is displayed only if there is an active replay-only window on the read-enabled standby.

-latches parameter

-load parameter

-locks parameter

-logs parameter

See Sample output of the db2pd -logs command.

-memblocks parameter

For the -memblocks parameter, there are three sections of output: individual blocks for the memory set, sorted totals grouped by memory pool, and sorted totals for the memory set:

Memory blocks:

PoolID

The memory pool id that owns the memory block.

PoolName

The memory pool name that owns the memory block.

BlockAge

The block age of the memory block. This is an incremental counter assigned as blocks are allocated.

Size

The size of the memory block in bytes.

I

The type of allocation. Value 1 means block will be freed individually while value 0 means it will be freed with the pool.

LOC

Line of code that allocated the memory block.

File

Filename hash value from where the block was allocated.

Sorted totals reported for each memory pool:

PoolID

The memory pool id that owns the memory block.

PoolName

The memory pool name that owns the memory block.

TotalSize

The total size of blocks (in bytes) allocated from the same line of code and file.

TotalCount

The number of blocks allocated from the same line of code and file.

LOC

Line of code that allocated the memory block.

File

Filename hash value from where the block was allocated.

Sorted totals reported for each memory set:

PoolID

The memory pool id that owns the memory block.

PoolName

The memory pool name that owns the memory block.

TotalSize

The total size of blocks (in bytes) allocated from the same line of code and file.

%Bytes

The percentage bytes allocated from the same line of code and file.

TotalCount

The number of blocks allocated from the same line of code and file.

%Count

The percentage count allocated from the same line of code and file.

LOC

Line of code that allocated the memory block.

File

Filename hash value from where the block was allocated.

-mempools parameter

-memsets parameter

-osinfo parameter

-pages parameter

For the -pages parameter, the following information is returned for each page:

BPID

Bufferpool ID that contains the page.

TbspaceID

Table space ID that contains the page.

TbspacePgNum

Logical page number within the table space (DMS only).

ObjID

Object ID that contains the page.

ObjPgNum

Logical page number within the object.

ObjClass

Class of object contained in the page. Possible values are Perm, Temp, Reorg, Shadow, and EMP.

ObjType

Type of object contained in the page. Possible values are Data, Index, LongField, XMLData, SMP, LOB, LOBA, and BlockMap.

Dirty

Indicates if the page is dirty. Possible values are Y and N. In the summary information section of the pages output, the value indicates the number of dirty pages.

Permanent

In the summary information section of the pages output, the value indicates the number of PERMANENT pages.

Temporary

In the summary information section of the pages output, the value indicates the number of TEMPORARY pages.

Prefetched

Indicates if the page has been prefetched. Possible values are Y and N.

See Sample output of the db2pd -pages command.

-recovery parameter

-reopt parameter

-reorgs parameter