DB2 10.5 for Linux, UNIX, and Windows

db2pd - Monitor and troubleshoot DB2 database command

Retrieves information from the DB2® database system memory sets.

Authorization

One of the following authority levels is required:
  • The SYSADM authority level.
  • The SYSCTRL authority level.
  • The SYSMAINT authority level.
  • The SYSMON authority level.
When the SYSMON authority level is granted, the following options are not available:
  • dump
  • memblocks
  • stack
Note: On Windows, you must have administrator authority to use the db2pd command.

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

Read syntax diagramSkip visual syntax diagram
>>-db2pd--+------------------------------------------+---------->
          '- -activestatements--+------------------+-'   
                                +-apphdl=appHandle-+     
                                '-file=filename----'     

>--+----------------------------------------+------------------->
   '- -addnode--+-------------+--+--------+-'   
                '-oldviewapps-'  '-detail-'     

>--+----------------+------------------------------------------->
   +- -alldatabases-+   
   '- -alldbs-------'   

>--+--------------------------------------------------+--------->
   +- -alldbpartitionnums-----------------------------+   
   |                   .-,-------------------------.  |   
   |                   V                           |  |   
   +- -dbpartitionnum----database_partition_number-+--+   
   |  (1)                                             |   
   +------- -global-----------------------------------+   
   |         .-,--------.                             |   
   |         V          |                             |   
   +- -host----hostname-+-----------------------------+   
   |             .-,------------------------------.   |   
   |             V                                |   |   
   '- -member--+---member_number--|--member_range-+-+-'   
               '-all--------------------------------'     

>--+---------------------------------------------------------------------------+-->
   '- -agents--+-------------+--+--------------+--+---------------+--+-------+-'   
               '-db=database-'  +-AgentId------+  '-file=filename-'  '-event-'     
                                '-app=AppHandl-'                                   

>--+--------------------------------------------------------+--->
   |           .-all------.  .-all-----.                    |   
   '- -apinfo--+----------+--+---------+--+---------------+-'   
               '-AppHandl-'  '-MaxStmt-'  '-file=filename-'     

>--+------------------------------------------------------+----->
   '- -applications--+---------------+--+---------------+-'   
                     +-AppHandl------+  '-file=filename-'     
                     '-agent=AgentId-'                        

>--+--------------------------------------------+--------------->
   '- -bufferpools--+------+--+---------------+-'   
                    '-bpID-'  '-file=filename-'     

>--+-----------------------------------+------------------------>
   '- -catalogcache--+---------------+-'   
                     '-file=filename-'     

>--+-------------------------------------------------------+---->
   '- -cfinfo--+---+--+-----+--+-----+--+------+--+------+-'   
               '-#-'  '-gbp-'  '-sca-'  '-lock-'  '-list-'     

>--+-----------------------------+------------------------------>
   '- -cfpool--+---------------+-'   
               '-file=filename-'     

>--+-----------------------------------+------------------------>
   '-+- -cleaner---------------------+-'   
     '- -dirtypages--+-------------+-'     
                     +-bp=bpID-----+       
                     +-count=count-+       
                     +-summary-----+       
                     +-temponly----+       
                     '-fixedonly---'       

>--+------------------------------+----------------------------->
   '- -command--+---------------+-'   
                '-file=filename-'     

>--+----------------------------------+------------------------->
   |                 .-,------------. |   
   |                 V              | |   
   '-+- -database-+----databasename-+-'   
     +- -db-------+                       
     '- -d--------'                       

>--+----------------------------+------------------------------->
   '- -dbcfg--+---------------+-'   
              '-file=filename-'     

>--+-----------------------------+--+------------+-------------->
   '- -dbmcfg--+---------------+-'  '- -dbptnmem-'   
               '-file=filename-'                     

>--+--------------------------------+--------------------------->
   '- -diagpath--+----------------+-'   
                 +-CF--+----+-----+     
                 |     '-id-'     |     
                 '-Member--+----+-'     
                           '-id-'       

>--+-----------------------------------------------------------------------------------------+-->
   '- -dump--+---------------------------+--+----------------------------------------------+-'   
             +-all--+------------------+-+  '-dumpdir=directory--+-----------------------+-'     
             |      '-apphdl=AppHandle-' |                       '-timeout=timeout-value-'       
             +-apphdl=AppHandle----------+                                                       
             +-eduid-apphdl=AppHandle----+                                                       
             '-pid-----------------------'                                                       

>--+------------------------------+----------------------------->
   '- -dynamic--+---------------+-'   
                +-anch=anchID---+     
                '-file=filename-'     

>--+------------------------------------------------------+----->
   '- -edus--+------------------------------------------+-'   
             '-interval=interval--+-------+--+--------+-'     
                                  '-top=n-'  '-stacks-'       

>--+-------------------------------------+--+--------------+---->
   '- -encryptioninfo--+---------------+-'  '- -everything-'   
                       '-file=filename-'                       

>--+-----------------------------------+------------------------>
   '-extentmovement--+---------------+-'   
                     '-file=filename-'     

>--+------------------------------------------------+----------->
   '- -fcm--+-----+--+---------+--+---------------+-'   
            '-hwm-'  '-numApps-'  '-file=filename-'     

>--+------------------+--+-------+--+--------+------------------>
   '- -file--filename-'  '- -fmp-'  '- -full-'   

>--+--------------------------------------------------------------+-->
   '-+- -fmpexechistory-+--+-------------+--+-----+--+----------+-'   
     '- -fmpe-----------'  '-+-pid=pid-+-'  '-n=n-'  '-genquery-'     
                             '-tid=tid-'                              

>--+-----------------------------------------------------+------>
   '- -fvp--+-agent_eduid-+--+------+--+---------------+-'   
            +-LAM1--------+  '-term-'  '-file=filename-'     
            +-LAM2--------+                                  
            '-LAM3--------'                                  

>--+-------+--+------+--+---------------------------+----------->
   '- -gfw-'  '- -ha-'  '- -hadr--+---------------+-'   
                                  '-file=filename-'     

>--+--------+--+--------+--------------------------------------->
   '- -help-'  '- -inst-'   

>--+-----------------------------------------------------------------------+-->
   '- -iostat--+-----+--+-----+--+----------+--+-------+--+--------------+-'   
               '- -t-'  '- -l-'  '- -length-'  '-count-'  '-COMPUTERNAME-'     

>--+---------------+-------------------------------------------->
   '- -interactive-'   

>--+-----------------------------------------+------------------>
   '- -latches--+-------+--+---------------+-'   
                '-group-'  '-file=filename-'     

>--+------------------------------------------------------------+-->
   '- -load--+-----------------+--+---------------+--+--------+-'   
             '-loadID="LOADID"-'  '-file=filename-'  '-stacks-'     

>--+------------------------------------------------------------------+-->
   '- -locks--+---------+--+---------------+--+-----------+--+------+-'   
              '-TranHdl-'  '-file=filename-'  '-showlocks-'  '-wait-'     

>--+---------------------------+-------------------------------->
   '- -logs--+---------------+-'   
             '-file=filename-'     

   .-,-----------------.                     
   V                   |                     
>----+---------------+-+--+--------------+---------------------->
     '- -member--num-'    '- -allmembers-'   

>--+------------------------------------------------------------+-->
   '-membersubsetstatus--+-------------------+--+-------------+-'   
                         '-subsetid=subsetID-'  '-db=database-'     

>--+------------------------------------------------------------------------------------------------+-->
   |              .-,-----------.                                                                   |   
   |              V             |                                                                   |   
   '- -memblocks----+-all-----+-+--+------+--+-----+--+--------+--+------+--+--------+--+---------+-'   
                    +-dbms----+    '-skip-'  '-top-'  '-blocks-'  '-sort-'  '-PoolID-'  '-pid=pid-'     
                    +-fcm-----+                                                                         
                    +-fmp-----+                                                                         
                    +-appl----+                                                                         
                    +-db------+                                                                         
                    '-private-'                                                                         

>--+-------------------------------+---------------------------->
   '- -mempools--+---------------+-'   
                 '-file=filename-'     

>--+------------------------------+----------------------------->
   '- -memsets--+---------------+-'   
                '-file=filename-'     

>--+---------------------------------------+-------------------->
   '- -osinfo--+------+--+---------------+-'   
               '-disk-'  '-file=filename-'     

>--+---------------------------------------------------+-------->
   '- -pages--+------+--+---------+--+---------------+-'   
              '-bpID-'  '-summary-'  '-file=filename-'     

>--+--------+--+---------------+-------------------------------->
   +- -q----+  '- -quiesceinfo-'   
   +- -quit-+                      
   +-q------+                      
   '-quit---'                      

>--+-------------------------------+---------------------------->
   '- -recovery--+---------------+-'   
                 '-file=filename-'     

>--+----------------------------+------------------------------->
   '- -reopt--+---------------+-'   
              '-file=filename-'     

>--+----------------------------------------+------------------->
   '- -reorgs--+--------------------------+-'   
               '-index--+---------------+-'     
                        '-file=filename-'       

>--+----------------------------------+--+------------+--------->
   '- -repeat--+---------+--+-------+-'  '- -runstats-'   
               '-num sec-'  '-count-'                     

>--+------------+----------------------------------------------->
   '- -rustatus-'   

>--+---------------------------------------------------------------------------+-->
   '--- -scansharing----+----------------------------------------------------+-'   
                        '-obj=objectID--pool=poolID--+---------------------+-'     
                                                     +-all-----------------+       
                                                     '-index=--+-indexID-+-'       
                                                               '-all-----'         

>--+--------------------------------------------------------+--->
   '- -serverlist--+-------------------+--+---------------+-'   
                   '-subsetid=subsetID-'  '-file=filename-'     

>--+--------------------------------------+--------------------->
   '- -serviceclasses--+----------------+-'   
                       '-serviceclassID-'     

>--+------------------------------+----------------------------->
   '- -sort--+------------------+-'   
             '-apphdl=AppHandle-'     

>--+---------------------------------------------------------------------------------+-->
   '- -stack--+-all--------------+--+----------------------------------------------+-'   
              +-apphdl=AppHandle-+  '-dumpdir=directory--+-----------------------+-'     
              '-eduid------------'                       '-timeout=timeout-value-'       

>--+-----------------------------+------------------------------>
   '- -static--+---------------+-'   
               '-file=filename-'     

>--+-----------------------------------------------------------------------------------------------------+-->
   '- -statisticscache--+--------------------+--+---------------+--+-----------------------------------+-'   
                        +- -db--databasename-+  '-file=filename-'  +-summary---------------------------+     
                        '- -alldbs-----------'                     +-detail----------------------------+     
                                                                   '-find schema=schema--object=object-'     

>--+-----------------------------------------+------------------>
   '- -storagegroups--+--------------------+-'   
                      '- -storagegroupname-'     

>--+----------------+------------------------------------------->
   '- -storagepaths-'   

>--+-----------------------------------------------+------------>
   '- -sysplex--+-------------+--+---------------+-'   
                '-db=database-'  '-file=filename-'     

>--+------------------------------------------------------------------------------------+-->
   '- -tablespaces--+----------------+--+-------+--+---------------+--+---------------+-'   
                    '-Table_space_ID-'  '-group-'  '-trackmodstate-'  '-file=filename-'     

>--+-------------------------------------------------------------------------------------------------------------+-->
   '- -tcbstats--+-----------+--+-------+--+------------------------------------------------+--+---------------+-'   
                 '-nocatalog-'  +-all---+  '-TbspaceID=table_space_ID--+------------------+-'  '-file=filename-'     
                                '-index-'                              '-TableID=table_ID-'                          

>--+------------------------+----------------------------------->
   '- -temptable--+-------+-'   
                  '-reset-'     

>--+-------------------------------+--+------------+------------>
   '- -thresholds--+-------------+-'  '- -totalmem-'   
                   '-thresholdID-'                     

>--+-----------------------------------------------------+------>
   '- -transactions--+--------------+--+---------------+-'   
                     +-TranHdl------+  '-file=filename-'     
                     '-app=AppHandl-'                        

>--+--------------------------------+--+-----------+------------>
   '- -utilities--+---------------+-'  '- -version-'   
                  '-file=filename-'                    

>--+----------------------------------------------------------------+-->
   '- -vmstat--+-----+--+----------+--+---------+--+--------------+-'   
               '- -t-'  '- -length-'  '- -count-'  '-COMPUTERNAME-'     

>--+-----------------------------------------+------------------>
   '- -wlocks--+--------+--+---------------+-'   
               '-detail-'  '-file=filename-'     

>--+--------------------------------------------------+--------->
   '- -workactionsets--+-----------------+--+-------+-'   
                       '-workactionsetID-'  '-group-'     

>--+------------------------------------------------+----------->
   '- -workclasssets--+----------------+--+-------+-'   
                      '-workclasssetID-'  '-group-'     

>--+----------------------------------------+------------------><
   '- -workloads--+------------+--+-------+-'   
                  '-workloadID-'  '-group-'     

Notes:
  1. 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 aboutthe 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.

-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 2 sca will dump the SCA structure information from CF #2.
-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 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.
-everything
Runs all options for all databases on all database partition servers that are local to the server.
-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.

-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 affect 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.
-iostat
Displays disk statistics for Windows NT. This is similar to UNIX iostat.
t
Display a time stamp to each sample.
l
Collects logical disk information. If not specified, this defaults to the physical disk.
length
The time between sampling in seconds. If not specified, the time between sampling is 1 second.
count
The number of iterations to sample. If not specified, the command will keep running until user interrupts.
COMPUTERNAME
The name of the computer to sample from. If not specified, the local computer is used as default.
-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.
-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.
-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.
-vmstat
Displays memory and CPU statistics of Windows NT. This is similar to UNIX vmstat.
t
Display a time stamp to each sample
length
The time between sampling in seconds. If not specified, the time between sampling is 1 second.
count
The number of intervals to sample. If not specified, command will continue running until user interrupts.
COMPUTERNAME
The name of computer to sample from. If not specified, the local computer is used as default.
-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.

Examples

Use the db2pd command, from the command line, in the following way to obtain information about agents that are servicing client requests:
   db2pd -agents
Use the db2pd command, from the command line, in the following way to obtain information about agents that are servicing client requests. In this case, the DB2PDOPT environment variable is set with the -agents parameter before invoking the db2pd command. The command uses the information set in the environment variable when it executes.
   export DB2PDOPT="-agents"
   db2pd
Use the db2pd command, from the command line, in the following way to obtain information about agents that are servicing client requests. In this case, the -agents parameter is set in the file file.out before invoking the db2pd command. The -command parameter causes the command to use the information in the file.out file when it executes.
   echo "-agents" > file.out
   db2pd -command file.out
Use the db2pd command, from the command line, in the following way to obtain all database and instance-scope information:
   db2pd -inst -alldbs
Use the db2pd -fvp command, from the command line, in the following way to obtain fenced vendor process state information:
For Log Manager:
  • A database named SAMPLE has logarchmeth1 set to TSM. At any time issue:
    db2pd -db sample -fvp lam1

    The resulting output is as follows:

    -------------------------------------------------------------------------
    Fenced Vendor Process State Information:
    -------------------------------------------------------------------------
    
    Log Manager:
    -------------------------------------------------------------------------
    LOGARCHMETH1 available.
    
    Vendor EDU is available and running.
      startTime: 1155581841  20060814145721
      function: sqluvint

    This tells you that the fenced vendor process is running in the vendor function sqluvint since August 14, 2006 14:57. Now, if you feel that this has been running too long, or you have determined that this process has hung waiting for TSM resources, you can terminate the fenced vendor process by issuing:

    db2pd -db sample -fvp lam1 term

    The resulting output is as follows:

    -------------------------------------------------------------------------
    Fenced Vendor Process State Information:
    -------------------------------------------------------------------------
    
    Log Manager:
    -------------------------------------------------------------------------
    LOGARCHMETH1 available.
    
    Vendor EDU is available and running.
      startTime: 1155581841  20060814145721
      function: sqluvint
    This fenced vendor process has been sent a signal to terminate.

    This shows you the same information as the previous output, but also lets you know that the terminate request has been sent. After waiting a few moments, you should notice that the request has taken affect.

  • If the fenced vendor process is running, but not running in vendor code, you will see this for a regular display request:
    -------------------------------------------------------------------------
    Fenced Vendor Process State Information:
    -------------------------------------------------------------------------
    
    Log Manager:
    -------------------------------------------------------------------------
    LOGARCHMETH1 available.
    
    Vendor EDU is available and running.
    No vendor code being run.
For Backup:
Note: It should be noted that the FORCE APPLICATION command can be used as an alternative to what is described in the following section.
  • A database named SAMPLE is being backed up to TSM using 2 sessions. You need to find out the backup agent EDU ID, which can be found through db2pd -edus or the DB2 diagnostics log. Once found, one can issue:
    db2pd -db sample -fvp 149

    The resulting output is as follows:

    -------------------------------------------------------------------------
    Fenced Vendor Process State Information:
    -------------------------------------------------------------------------
    
    Backup:
    -------------------------------------------------------------------------
    Media Controller(s):
    -------------------------------------------------------------------------
       EDU ID: 504
    mediaSession: 1
     mediaSeqNum: 0
    Vendor EDU is available and running.
      startTime: 1155583315  20060814152155
      function: sqluvint
    
       EDU ID: 505
    mediaSession: 2
     mediaSeqNum: 0
    Vendor EDU is available and running.
    No vendor code being run.

    This says that DB2 Media Controller 0 (EDU ID: 504) is in vendor code, while DB2 Media Controller 1 (EDU ID: 505) has a fenced vendor process, but is not running vendor code. Now, if you feel that this has been running too long, or you have determined that this process has hung waiting for TSM resources, you can terminate the fenced vendor process by issuing:

    db2pd -db sample -fvp 149 term

    The resulting output is as follows:

    -------------------------------------------------------------------------
    Fenced Vendor Process State Information:
    -------------------------------------------------------------------------
    
    Backup:
    -------------------------------------------------------------------------
    Media Controller(s):
    -------------------------------------------------------------------------
       EDU ID: 504
    mediaSession: 1
     mediaSeqNum: 0
    Vendor EDU is available and running.
      startTime: 1155583315  20060814152155
      function: sqluvint
    This fenced vendor process has been sent a signal to terminate.
    
       EDU ID: 505
    mediaSession: 2
     mediaSeqNum: 0
    Vendor EDU is available and running.
    No vendor code being run.
    This fenced vendor process has been sent a signal to terminate.

    This tells you the same information as the previous output, but notes that both fenced vendor processes have been sent terminate requests and will be terminated shortly.

Usage notes

-activestatements parameter

For the -activestatements parameter, the following information is returned:
Address
Address of the current activity.
AppHandl
Application handle.
UOW-ID
UOW-ID at the start of execution.
StmtID
The activity ID of the statement within the UOW-ID.
AnchID
The anchor ID of the statement.
StmtUID
The unique ID of the statement within the anchor.
EffISO
Effective isolation level.
EffLockTOut
Effective lock timeout at start.
EffDegree
Effective SMP parallelism degree at start.
StartTime
The start time of when the statement was executed.
LastRefTime
Last application reference time.

-agents parameter

For the -agents parameter, the following information is returned:
AppHandl
The application handle, including the node and the index.
AgentPid
The process ID of the agent process.
Priority
The priority of the agent.
Type
The type of agent.
State
The state of the agent.
ClientPid
The process ID of the client process.
Userid
The user ID running the agent.
ClientNm
The name of the client process.
Rowsread
The number of rows that were read by the agent.
Rowswrtn
The number of rows that were written by the agent.
LkTmOt
The lock timeout setting for the agent.
LastApplID
The outbound application ID that the pooled agent serviced last.
LastPooled
The timestamp when the agent was pooled.
If the event option is specified with the -agents parameter, the following, additional information is returned. You use this information to determine whether an agent continues to process the same task or whether the agent moves onto new tasks over time.
AGENT_STATE_LAST_UPDATE_TIME(Tick Value)
The last time that the event, being processed by the agent, was changed. The event currently processed by the agent is identified by the EVENT_STATE, EVENT_TYPE, EVENT_OBJECT, and EVENT_OBJECT_NAME columns.
EVENT_STATE
The state of the event last processed by this agent. The possible values are EXECUTING and IDLE.
EVENT_TYPE
The type of event last processed by this agent. The possible values are ACQUIRE, PROCESS, and WAIT.
EVENT_OBJECT
The object of the event last processed by this agent. The possible values are COMP_DICT_BUILD, IMPLICIT_REBIND, INDEX_RECREATE, LOCK, LOCK_ESCALATION, QP_QUEUE, REMOTE_REQUEST, REQUEST, ROUTINE, and WLM_QUEUE.
EVENT_OBJECT_NAME
The event object name. If the value of EVENT_OBJECT is LOCK, the value of this column is the name of the lock that the agent is waiting on. If the value of EVENT_OBJECT is WLM_QUEUE, the value of the column is the name of the WLM threshold that the agent is queued on. Otherwise, the value is NULL.
The possible combinations of EVENT_STATE, EVENT_TYPE, EVENT_OBJECT and EVENT_OBJECT_NAME column values are listed in the following table:
Table 1. Possible combinations for EVENT_STATE, EVENT_TYPE, EVENT_OBJECT and EVENT_OBJECT_NAME column values
Event description EVENT_STATE value EVENT_TYPE value EVENT_OBJECT value EVENT_OBJECT_NAME value
Acquire lock IDLE ACQUIRE LOCK Lock name
Escalate lock EXECUTING PROCESS LOCK_ESCALATION NULL
Process request EXECUTING PROCESS REQUEST NULL
Wait for a new request IDLE WAIT REQUEST NULL
Wait for a request to be processed at a remote partition IDLE WAIT REMOTE_REQUEST NULL
Wait on a WLM threshold queue IDLE WAIT WLM_QUEUE Threshold name
Process a routine EXECUTING PROCESS ROUTINE NULL
Re-create an index EXECUTING PROCESS INDEX_RECREATE NULL
Build compression dictionary EXECUTING PROCESS COMP_DICT_BLD NULL
Implicit rebind EXECUTING PROCESS IMPLICIT_REBIND NULL

-apinfo parameter

For the -apinfo parameter, the following information is returned:
AppHandl
The application handle, including the node and the index.
Application PID
The process ID for the application.
Application Node Name
The name of the application node.
IP Address
The IP address from which the database connection was established.
Connection Start Time
The time stamp at which the application connection started.
Client User ID
The client user ID.
System Auth ID
This is the system authorization ID of the connection.
Coordinator EDU ID
The EDU ID of the coordinator agent for the application.
Coordinator Partition
The partition number of the coordinator agent for the application.
Number of Agents
The number of agents that are working on behalf of the application.
Locks timeout value
The lock timeout value for the application.
Locks Escalation
The locks escalation flag indicates whether the lock, used by the application, has been escalated.
Workload ID
Workload identifier.
Workload Occurrence ID
Workload occurrence identifier.
Trusted Context
The name of the trusted context associated with the connection if the connection is either an implicit trusted connection or an explicit trusted connection.
Connection Trust Type
The connection trust type. This is one of: non-trusted, implicit trusted, or explicit trusted connection.
Role Inherited
This is the role inherited through a trusted connection, if any.
Application Status
The status of the application.
Application Name
The name of the application.
Application ID
The application ID. This value is the same as the appl_id monitor element data. For detailed information about how to interpret this value, see the "appl_id - Application ID monitor element".
UOW-ID
The ID of the current UOW of the application.
Activity ID
The activity ID within the UOW.
Package Schema
The package schema.
Package Name
The package name.
Package Version
The package version.
Consistency Token
Identifies the version of the package that contains the SQL that is currently executing.
Section Number
The section number of the SQL statement.
SQL Type
The type of SQL: dynamic or static.
Isolation
The isolation mode set for the application.
Effective degree
The effective degree of parallelism for the activity.
Number of subagent(s)
The number of subagents that are performing the SQL statement.
Source ID
The internal identifier that is given to the source of the SQL statement.
Cursor ID
The cursor identifier for the SQL statement.
Nesting level
The level of nesting or recursion that is in effect when the statement was being run.
Invocation ID
Identifies one invocation of a routine from the other at the same nesting level within a unit of work.
Package cache ID
The internal package cache identifier for the SQL statement.
Anchor ID
The anchor identifier for the SQL statement.
Statement UID
The version of the package that contains the SQL that is currently executing.
Statement Type
The type of statement operation, such as: DML, DDL.
Statement
The SQL statement.
ClientUserID
Client userid for the transaction, which is the same as tpmon_client_userid (TP Monitor Client User ID monitor element).
ClientWrkstnName
Client workstation name for the transaction, which is the same as tpmon_client_wkstn (TP Monitor Client Workstation Name monitor element).
ClientApplName
Client application name driving the transaction, which is the same as tpmon_client_app (TP Monitor Client Application monitor element).
ClientAccntng
Accounting string of the client driving the transaction, which is the same as tpmon_acc_str (TP Monitor Client Accounting String monitor element).
Entry time
The time at which the activity entered the system.
Local start time
The time at which the activity began doing work.
Last reference time
The last time the activity was accessed by a request.
See Sample output of the db2pd -apinfo command.

-applications parameter

For the -applications parameter, the following information is returned:
ApplHandl
The application handle, including the node and the index.
NumAgents
The number of agents that are working on behalf of the application.
CoorPid
The process ID of the coordinator agent for the application.
Status
The status of the application.
Appid
The application ID. This value is the same as the appl_id monitor element data. For detailed information about how to interpret this value, see the documentation for the appl_id monitor element.
ClientIPAddress
The IP address from which the database connection was established.
EncryptionLvl
The data stream encryption used by the connection. This is one of NONE, LOW or HIGH. NONE implies that no data stream encryption is being used. LOW implies that the database server authentication type is set to DATA_ENCRYPT. HIGH implies that SSL is being used.
SystemAuthID
This is the system authorization ID of the connection.
ConnTrustType
The connection trust type. This is one of: non-trusted, implicit trusted connection, or explicit trusted connection.
TrustedContext
The name of the trusted context associated with the connection if the connection is either an implicit trusted connection or an explicit trusted connection.
RoleInherited
This is the role inherited through a trusted connection, if any.

-bufferpools parameter

For the -bufferpools parameter, the following information is returned:
First Active Pool ID
The ID of the first active buffer pool.
Max Bufferpool ID
The maximum ID of all active buffer pools.
Max Bufferpool ID on Disk
The maximum ID of all buffer pools defined on disk.
Num Bufferpools
The number of available buffer pools.
ID
The ID of the buffer pool.
Name
The name of the buffer pool.
PageSz
The size of the buffer pool pages.
PA-NumPgs
The number of pages in the page area of the buffer pool.
BA-NumPgs
The number of pages in the block area of the buffer pool. This value is 0 if the buffer pool is not enabled for block-based I/O.
BlkSize
The block size of a block in the block area of the buffer pool. This value is 0 if the buffer pool is not enabled for block-based I/O.
NumTbsp
The number of table spaces that are using the buffer pool.
PgsLeft
The number of pages left to remove in the buffer pool if its size is being decreased.
CurrentSz
The current size of the buffer pool in pages.
PostAlter
The size of the buffer pool in pages when the buffer pool is restarted.
SuspndTSCt
The number of table spaces mapped to the buffer pool that are currently I/O suspended. If 0 is returned for all buffer pools, the database I/O is not suspended.
Automatic
Shows the self-tuning automatic status. "True" means self-tuning for this buffer pool is enabled. "False" means self-tuning for this buffer pool is not enabled.
DatLRds
Buffer Pool Data Logical Reads. Indicates the number of data pages which have been requested from the buffer pool (logical) for regular and large table spaces.
DatPRds
Buffer Pool Data Physical Reads. Indicates the number of data pages read in from the table space containers (physical) for regular and large table spaces.
HitRatio
Hit ratio for data pages in the buffer pool using formula 1 - DatPRds / DatLRds.
TmpDatLRds
Buffer Pool Temporary Data Logical Reads. Indicates the number of data pages which have been requested from the buffer pool (logical) for temporary table spaces.
TmpDatPRds
Buffer Pool Temporary Data Physical Reads. Indicates the number of data pages read in from the table space containers (physical) for temporary table spaces.
HitRatio
Hit ratio for temporary data pages in the buffer pool using formula 1 - TmpDatPRds / TmpDatLRds.
IdxLRds
Buffer Pool Index Logical Reads. Indicates the number of index pages which have been requested from the buffer pool (logical) for regular and large table spaces.
IdxPRds
Buffer Pool Index Physical Reads. Indicates the number of index pages read in from the table space containers (physical) for regular and large table spaces.
HitRatio
Hit ratio for index pages in the buffer pool using formula 1 - IdxPRds / IdxLRds.
TmpIdxLRds
Buffer Pool Temporary Index Logical Reads. Indicates the number of index pages which have been requested from the buffer pool (logical) for temporary table spaces.
TmpIdxPRds
Buffer Pool Temporary Index Physical Reads. Indicates the number of index pages read in from the table space containers (physical) for temporary table spaces.
HitRatio
Hit ratio for temporary index pages in the buffer pool using formula 1 - TmpIdxPRds / TmpIdxLRds.
DataWrts
Buffer Pool Data Writes. Indicates the number of times a buffer pool data page was physically written to disk.
IdxWrts
Buffer Pool Index Writes. Indicates the number of times a buffer pool index page was physically written to disk.
DirRds
Direct Reads From Database. The number of read operations that do not use the buffer pool.
DirRdReqs
Direct Read Requests. The number of requests to perform a direct read of one or more sectors of data.
DirRdTime
Direct Read Time. The elapsed time (in milliseconds) required to perform the direct reads.
DirWrts
Direct Writes to Database. The number of write operations that do not use the buffer pool.
DirWrtReqs
Direct Write Requests. The number of requests to perform a direct write of one or more sectors of data.
DirWrtTime
Direct Write Time. The elapsed time (in milliseconds) required to perform the direct writes.
AsDatRds
Buffer Pool Asynchronous Data Reads. Indicates the number of data pages read in from the table space containers (physical) by asynchronous engine dispatchable units (EDUs) for all types of table spaces.
AsDatRdReq
Buffer Pool Asynchronous Read Requests. The number of asynchronous read requests.
AsIdxRds
Buffer Pool Asynchronous Index Reads. Indicates the number of index pages read in from the table space containers (physical) by asynchronous engine dispatchable units (EDUs) for all types of table spaces.
AsIdxRdReq
Buffer Pool Asynchronous Index Read Requests. The number of asynchronous read requests for index pages.
AsRdTime
Buffer Pool Asynchronous Read Time. Indicates the total amount of time spent reading in data and index pages from the table space containers (physical) by asynchronous engine dispatchable units (EDUs) for all types of table spaces. This value is given in milliseconds.
AsDatWrts
Buffer Pool Asynchronous Data Writes. The number of times a buffer pool data page was physically written to disk by either an asynchronous page cleaner, or a prefetcher. A prefetcher might have written dirty pages to disk to make space for the pages being prefetched.
AsIdxWrts
Buffer Pool Asynchronous Index Writes. The number of times a buffer pool index page was physically written to disk by either an asynchronous page cleaner, or a prefetcher. A prefetcher might have written dirty pages to disk to make space for the pages being prefetched.
AsWrtTime
Buffer Pool Asynchronous Write Time. The total elapsed time spent writing data or index pages from the buffer pool to disk by database manager page cleaners.
TotRdTime
Total Buffer Pool Physical Read Time. Indicates the total amount of time spent reading in data and index pages from the table space containers (physical) for all types of table spaces. This value is given in milliseconds.
TotWrtTime
Total Buffer Pool Physical Write Time. Provides the total amount of time spent physically writing data or index pages from the buffer pool to disk. Elapsed time is given in milliseconds.
VectIORds
Total Number of Pages Read by Vectored IO. The total number of pages read by vectored I/O into the page area of the buffer pool.
VectIOReq
Number of Vectored IO Requests. The number of vectored I/O requests. More specifically, the number of times the DB2 database product performs sequential prefetching of pages into the page area of the buffer pool.
BlockIORds
Total Number of Pages Read by Block IO. The total number of pages read by block I/O into the block area of the bufferpool.
BlockIOReq
Number of Block IO Requests. The number of block I/O requests. More specifically, the number of times the DB2 database product performs sequential prefetching of pages into the block area of the bufferpool.
PhyPgMaps
Number of Physical Page Maps. The number of physical page maps.
FilesClose
Database Files Closed. The total number of database files closed.
NoVictAvl
Buffer Pool No Victim Buffers. Number of times an agent did not have a preselected victim buffer available.
UnRdPFetch
Unread Prefetch Pages. Indicates the number of pages that the prefetcher read in that were never used.

-catalogcache parameter

For the -catalogcache parameter, the following information is returned:
Catalog Cache:
Configured Size
The number of bytes as specified by the catalogcache_sz database configuration parameter.
Current Size
The current number of bytes used in the catalog cache.
Maximum Size
The maximum amount of memory that is available to the cache (up to the maximum database global memory).
High Water Mark
The largest physical size reached during processing.
SYSTABLES:
Schema
The schema qualifier for the table.
Name
The name of the table.
Type
The type of the table.
TableID
The table identifier.
TbspaceID
The identifier of the table space where the table resides.
LastRefID
The last process identifier that referenced the table.
CatalogCache LoadingLock
The name of the catalog cache loading lock for the cache entry.
CatalogCache UsageLock
The name of the usage lock for the cache entry.
Sts
The status of the entry. The possible values are:
  • V (valid).
  • I (invalid).
  • S (soft invalid. Catalog cache entries become soft invalid when statistics have been updated by real-time statistics collection. These catalog cache entries may still be used by a database agent, but they are not valid for use by a new catalog cache request. Once the soft invalid entry is no longer in use, it will be removed. New catalog cache requests will use the valid entry.)
SYSRTNS:
RoutineID
The routine identifier.
Schema
The schema qualifier of the routine.
Name
The name of the routine.
LastRefID
The last process identifier that referenced the routine.
CatalogCache LoadingLock
The name of the catalog cache loading lock for the cache entry.
CatalogCache UsageLock
The name of the usage lock for the cache entry.
Sts
The status of the entry. The possible values are:
  • V (valid).
  • I (invalid).
SYSRTNS_PROCSCHEMAS:
RtnName
The name of the routine.
ParmCount
The number of parameters in the routine.
LastRefID
The last process identifier that referenced the PROCSCHEMAS entry.
CatalogCache LoadingLock
The name of the catalog cache loading lock for the cache entry.
CatalogCache UsageLock
The name of the usage lock for the cache entry.
Sts
The status of the entry. The possible values are:
  • V (valid).
  • I (invalid).
SYSDATATYPES:
TypID
The type identifier.
LastRefID
The last process identifier that referenced the type.
CatalogCache LoadingLock
The name of the catalog cache loading lock for the cache entry.
CatalogCache UsageLock
The name of the usage lock for the cache entry.
Sts
The status of the entry. The possible values are:
  • V (valid).
  • I (invalid).
SYSCODEPROPERTIES:
LastRefID
The last process identifier to reference the SYSCODEPROPERTIES entry.
CatalogCache LoadingLock
The name of the catalog cache loading lock for the cache entry.
CatalogCache UsageLock
The name of the usage lock for the cache entry.
Sts
The status of the entry. The possible values are:
  • V (valid).
  • I (invalid).
SYSNODEGROUPS:
PMapID
The distribution map identifier.
RBalID
The identifier if the distribution map that was used for the data redistribution.
CatalogCache LoadingLock
The name of the catalog cache loading lock for the cache entry.
CatalogCache UsageLock
The name of the usage lock for the cache entry.
Sts
The status of the entry. The possible values are:
  • V (valid).
  • I (invalid).
SYSDBAUTH:
AuthID
The authorization identifier (authid).
AuthType
The authorization type.
LastRefID
The last process identifier to reference the cache entry.
CatalogCache LoadingLock
The name of the catalog cache loading lock for the cache entry.
SYSRTNAUTH:
AuthID
The authorization identifier (authid).
AuthType
The authorization type.
Schema
The schema qualifier of the routine.
RoutineName
The name of the routine.
RtnType
The type of the routine.
CatalogCache LoadingLock
The name of the catalog cache loading lock for the cache entry.
SYSROLEAUTH:
AuthID
The authorization identifier (authid).
AuthType
The authorization type.
Roleid
The role identifier if the authorization identifier is a role.
LastRefID
The last process identifier to reference the cache entry.
CatalogCache LoadingLock
The name of the catalog cache loading lock for the cache entry.
TABLESPACES:
Schema
The schema qualifier for the table.
Name
The name of the table.
Type
The type of the table.
TableID
The table identifier.
TbspaceID
The identifier of the table space where the table resides.
LastRefID
The last process identifier that referenced the table.
CatalogCache LoadingLock
The name of the catalog cache loading lock for the cache entry.
CatalogCache UsageLock
The name of the usage lock for the cache entry.
Sts
The status of the entry. The possible values are:
  • V (valid).
  • I (invalid).
  • S (soft invalid. Catalog cache entries become soft invalid when statistics have been updated by real-time statistics collection. These catalog cache entries may still be used by a database agent, but they are not valid for use by a new catalog cache request. Once the soft invalid entry is no longer in use, it will be removed. New catalog cache requests will use the valid entry.)

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

For the -dynamic parameter, the following information is returned:
Dynamic Cache:
Current Memory Used
The number of bytes used by the package cache.
Total Heap Size
The number of bytes configured internally for the package cache.
Cache Overflow flag state
A flag to indicate whether the package cache is in an overflow state.
Number of references
The number of times the dynamic portion of the package cache has been referenced.
Number of Statement Inserts
The number of statement inserts into the package cache.
Number of Statement Deletes
The number of statement deletions from the package cache.
Number of Variation Inserts
The number of variation inserts into the package cache.
Number of statements
The number of statements in the package cache.
Dynamic SQL Statements:
AnchID
The hash anchor identifier.
StmtID
The statement identifier.
NumEnv
The number of environments that belong to the statement.
NumVar
The number of variations that belong to the statement.
NumRef
The number of times that the statement has been referenced.
NumExe
The number of times that the statement has been executed.
Text
The text of the SQL statement.
Dynamic SQL Environments:
AnchID
The hash anchor identifier.
StmtUID
The unique statement identifier.
EnvID
The environment identifier.
Iso
The isolation level of the environment.
QOpt
The query optimization level of the environment.
Blk
The blocking factor of the environment.
Dynamic SQL Variations:
AnchID
The hash anchor identifier.
StmtUID
The unique statement identifier.
EnvID
The environment identifier for this variation.
VarID
The variation identifier.
NumRef
The number of times this variation has been referenced.
Typ
The internal statement type value for the variation section.
Lockname
The variation lockname.
Val
The variation valid flag. The following are possible values:
Y
Object is valid.
N
Object is invalid.
X
Object is inoperative.
?
Object needs revalidation.
Insert Time
The time at which the variation was inserted into the package cache.
Sect Size
The length of section data.

-edus parameter

For the -edus parameter, the following information is returned:
EDU ID
The unique identifier for the engine dispatchable unit (EDU). Except on Linux operating systems, the EDU ID is mapped to the thread ID. On Linux operating system the EDU ID is a DB2 generated unique identifier.
TID
Thread identifier. Except on Linux operating systems, the thread ID is the unique identifier for the specific thread. On Linux operating systems, this is a DB2 generated unique identifier.
Kernel TID
A unique identifier for the operating system kernel thread in service.
EDU Name
DB2 specific name for the EDU.
USR
Total CPU user time consumed by the EDU.
SYS
Total CPU system time consumed by the EDU.
USR Delta
Indicates the delta of the CPU user time across a specified time interval.
SYS Delta
Indicates the delta of the CPU system time across a specified time interval.

See Sample output of the db2pd -edus command.

-extentmovement parameter

For the -extentmovement parameter, the following information is returned:
Extent Movement:
Address
The address of the extent being moved.
TbspName
The tablespace name of the extent being moved.
Current
The current extent being moved.
Last
The last extent being moved.
Moved
The number of extents that have been moved.
Left
The number of extents that are left to be moved.
TotalTime
The total amount of time it has taken to move the extents, measured in seconds.

See Sample output of the db2pd -extentmovement command.

-fcm parameter

For the -fcm parameter, the following information is returned:
FCM Usage Statistics:
Total Buffers
Total number of buffers, including all free and in-use ones.
Free Buffers
Number of free buffers.
Buffers LWM
Lowest number of free buffers.
Max Buffers
Maximum number of buffers that can be allocated based on the amount of virtual memory reserved when the instance was started.
Total Channels
Total number of channels, including all free and in-use ones.
Free Channels
Number of free channels.
Channels LWM
Lowest number of free channels.
Max Channels
Maximum number of channels that can be allocated based on the amount of virtual memory reserved when the instance was started.
Total Sessions
Total number of sessions, including all free and in-use ones.
Free Sessions
Number of free sessions.
Sessions LWM
Lowest number of free sessions.
Partition
The database partition server number.
Bufs Sent
The total number of FCM buffers that are sent from the database partition server where the db2pd command is running to the database partition server that is identified in the output.
Bufs Recv
The total number of FCM buffers that are received by the database partition server where the db2pd command is running from the database partition server that is identified in the output.
Status
The logical connection status between the database partition server where the db2pd command is running and the other database partition servers that are listed in the output. The possible values are:
  • Inactive: The database partition server is defined in the db2nodes.cfg file but is currently inactive (for example, the user has stopped the partition).
  • Active: The database partition server is active.
  • Undefined: The database partition server is not defined in the db2nodes.cfg file. This might indicate an error.
  • Unknown: The database partition server is in an unknown state. This indicates an error.
Buffers Current® Consumption
AppHandl
The application handle, including the node and the index.
TimeStamp
A unique identifier for the usage of an application handle.
Buffers In-use
The number of buffers currently being used by an application.
Channels Current Consumption
AppHandl
The application handle, including the node and the index.
TimeStamp
A unique identifier for the usage of an application handle.
Channels In-use
The number of channels currently being used by an application.
Buffers Consumption HWM
AppHandl
The application handle, including the node and the index.
TimeStamp
A unique identifier for the usage of an application handle.
Buffers Used
The high-watermark number of buffers used by an application since the start of the instance.
Channels Consumption HWM
AppHandl
The application handle, including the node and the index.
TimeStamp
A unique identifier for the usage of an application handle.
Channels Used
The high-watermark number of channels used by an application since the start of the instance.

-fmp parameter

For the -fmp parameter, the following information is returned:
  • Pool Size: Current number of FMP processes in the FMP pool.
  • Max Pool Size: Maximum number of FMP process in the FMP pool.
  • Keep FMP: Value of keepfenced database manager configuration parameter.
  • Initialized: FMP is initialized. Possible values are Yes and No.
  • Trusted Path: Path of trusted procedures
  • Fenced User: Fenced user ID
FMP Process:
  • FmpPid: Process ID of the FMP process.
  • Bit: Bit mode. Values are 32 bit or 64 bit.
  • Flags: State flags for the FMP process. Possible values are:
    • 0x00000000 - JVM initialized
    • 0x00000002 - Is threaded
    • 0x00000004 - Used to run federated wrappers
    • 0x00000008 - Used for Health Monitor
    • 0x00000010 - Marked for shutdown and will not accept new tasks
    • 0x00000020 - Marked for cleanup by db2sysc
    • 0x00000040 - Marked for agent cleanup
    • 0x00000100 - All ipcs for the process have been removed
    • 0x00000200 - .NET runtime initialized
    • 0x00000400 - JVM initialized for debugging
    • 0x00000800 - Termination flag
  • ActiveTh: Number of active threads running in the FMP process.
  • PooledTh: Number of pooled threads held by the FMP process.
  • Active: Active state of the FMP process. Values are Yes or No.
Active Threads:
  • FmpPid: FMP process ID that owns the active thread.
  • EduPid: EDU process ID that this thread is working.
  • ThreadId: Active thread ID.
Pooled Threads:
  • FmpPid: FMP process ID that owns the pooled thread.
  • ThreadId: Pooled thread ID.

-fmpexechistory | -fmpe parameter

For the -fmpexechistory | -fmpe parameter, the following information is returned:
FMP Process:
  • FmpPid - Process ID of the FMP process.
  • Bit - Bit mode. Values are 32 bit or 64 bit.
  • Flags - State flags for the FMP process. Possible values are:
    • 0x00000000 - JVM initialized
    • 0x00000002 - Is threaded
    • 0x00000004 - Used to run federated wrappers
    • 0x00000008 - Used for Health Monitor
    • 0x00000010 - Marked for shutdown and will not accept new tasks
    • 0x00000020 - Marked for cleanup by db2sysc
    • 0x00000040 - Marked for agent cleanup
    • 0x00000100 - All ipcs for the process have been removed
    • 0x00000200 - .NET runtime initialized
    • 0x00000400 - JVM initialized for debugging
    • 0x00000800 - Termination flag
  • ActiveThrd - Number of active threads running in the FMP process.
  • PooledThrd - Number of pooled threads held by the FMP process.
  • ForcedThrd - Number of forced threads generated by the FMP process.
  • Active - Active state of the FMP process. Values are Yes or No.
Active Threads:
  • EduPid - EDU process ID that this thread is working.
  • ThreadId - Active thread ID.
  • RoutineID - The routine identifier.
  • Timestamp - A unique identifier for the usage of an application handle.
Pooled Threads:
  • ThreadId - Pooled thread ID.
  • RoutineID - The routine identifier.
  • Timestamp - A unique identifier for the usage of an application handle.
Forced Threads:
  • ThreadId - Forced thread ID.
  • RoutineID - The routine identifier.
  • Timestamp - A unique identifier for the usage of an application handle.

See Sample output of the db2pd -fmpexechistory command.

-hadr parameter

For the -hadr parameter, information related to high availability disaster recovery is returned. The information returned by this command depends on where it is issued:
  • If it's issued from a standby, the command returns information about that standby and the primary only.
  • If it's issued from a primary, the command returns information about the primary and all of the standbys.

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 V10.5), 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 primary or standard to standby. 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.

-iostat parameter

For the -iostat parameter, the following information is returned:
Disk
Windows disk number.
Queue
Current disk queue length. This occurs in both read and write.
%disk
The percentage of time the disk was not idle within the interval
tps
Disk transfers per second
KBpsR
Kilobytes read per second
KBpsW
Kilobytes written per second.
ms/R
Average time in milliseconds to service a read
ms/W
Average time in milliseconds to service a write

-latches parameter

For the -latches parameter, the following information is returned:
Address
Address of the holding latch in the virtual address space.
Holder
The EDU ID of the EDU that is holding the latch.
Waiter
The EDU ID of the EDU waiting for the latch.
Filename
The source file name where the latch is obtained.
LOC
The line of code in the file indicated by the file name where the latch is obtained.
LatchType
The identity of the latch being held.

-load parameter

For the -load parameter, the following information is returned:
LoadID
The ID of a specific load operation.
EDU ID
The unique identifier for the engine dispatchable unit (EDU). Except on Linux operating systems, the EDU ID is mapped to the thread ID. On Linux operating system the EDU ID is a DB2 generated unique identifier.
EDU Name
DB2 specific name for the EDU.
TableName
The name of the table.
SchemaName
The schema that qualifies the table name.
AppHandl
The application handle, including the node and the index.
Application ID
The application ID. This values is the same as the appl_id monitor element data.
StartTime
The date and time when the load operation was originally invoked.
LoadPhase
The phase that the load operation is currently in.

-locks parameter

For the -locks parameter, the following information is returned:
TranHdl
The transaction handle that is requesting the lock.
Lockname
The name of the lock.
Type
The type of lock. The possible values are:
  • Row (row lock)
  • Pool (table space lock)
  • Partition (data partition lock)
  • Table (table lock)
  • AlterTab (internal alter table lock)
  • ObjectTab (internal object table lock)
  • OnlBackup (on line backup lock)
  • DMS Seq (DMS sequence lock)
  • Internal P (internal plan lock)
  • Internal V (internal variation lock)
  • Key Value (key value lock)
  • No Lock (no lock held)
  • Block Lock (MDC block lock)
  • LF Release (long field lock)
  • LFM File (long field lock)
  • LOB/LF 4K (LOB / long field buddy space lock)
  • APM Seq (internal sequence lock)
  • Tbsp Load (internal loading table space lock)
  • DJ UserMap (federated user mapping lock)
  • DF NickNm (federated nick name lock)
  • CatCache (internal catalog lock)
  • OnlReorg (on line reorg lock)
  • Buf Pool (buffer pool lock)
Mode
The lock mode. The possible values are:
  • IS (intent share)
  • IX (intent exclusive)
  • S (share)
  • SIX (share with intention exclusive)
  • X (exclusive)
  • IN (intent none)
  • Z (super exclusive)
  • U (update)
  • NS (scan share)
  • NW (next key weak exclusive)
Sts
The lock status. The possible values are:
  • G (granted)
  • C (converting)
  • W (waiting)
Owner
The transaction handle that owns the lock. In certain timing scenarios, when a transaction is waiting for a lock, the transaction holding the lock cannot be identified. In these cases, the Owner field will contain the transaction handle of the transaction requesting the lock.
Dur
The duration of the lock.
HoldCount
The number of holds placed on the lock. Locks with holds are not released when transactions are committed.
Att
The attributes of the lock. Possible values are:
  • 0x01 Wait for availability.
  • 0x02 Acquired by escalation.
  • 0x04 RR lock "in" block.
  • 0x08 Insert Lock.
  • 0x10 Lock by RR scan.
  • 0x20 Update/delete row lock.
  • 0x40 Allow new lock requests.
  • 0x80 A new lock requestor.
ReleaseFlg
The lock release flags. Possible values are:
  • 0x80000000 Locks by SQL compiler.
  • 0x40000000 Non-unique, untracked locks.
rrIID
The IID of the index through which the RR lock (0x10 attribute shown previously) was acquired. Possible values are:
  • 0 Not related to a single, specific index (or not an RR lock).
  • <>0 The specific index IID used to acquire the lock.
TableNm
The table name of the transaction that is holding the lock.
SchemaNm
The schema name of the transaction that is holding the lock.

-logs parameter

For the -logs parameter, the following information is returned:
Current Log Number
The number of the current active log.
Pages Written
The current page being written in the current log.
Cur Commit Disk Log Reads
The number of times the currently committed version of a row was retrieved via a log read from disk (versus log buffer).
Cur Commit Total Log Reads
The total number of times the currently committed version of a row was retrieved from the logs (log buffer and disk).
Method 1 Archive Status
The result of the most recent log archive attempt. Possible values are Success or Failure.
Method 1 Next Log to Archive
The next log file to be archived.
Method 1 First Failed
The first log file that was unsuccessfully archived.
Method 2 Archive Status
The result of the most recent log archive attempt. Possible values are Success or Failure.
Method 2 Next Log to Archive
The next log file to be archived.
Method 2 First Failed
The first log file that was unsuccessfully archived.
StartLSN
The starting log sequence number.
StartLSO
The first LSO of the log file.
State
0x00000020 indicates that the log has been archived.
Size
The size of the log's extent, in pages.
Pages
The number of pages in the log.
Filename
The file name of the log.
Log Chain ID
The identifier of the log chain number
Current LSN
The current log sequence number (LSN)
Current LSO
The current LSO.

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

For the -mempools parameter, the following information is returned (All sizes are specified in bytes):
MemSet
The memory set that owns the memory pool.
PoolName
The name of the memory pool.
Id
The memory pool identifier.
SecondId
The second memory pool identifier to distinguish between multiple memory pools of the same type.
Overhead
The internal usage information required for the pool structures.
LogSz
The current total of pool memory requests.
LogHWM
The logical size high water mark.
PhySz
The physical memory required for logical size.
PhyHWM
The largest physical size reached during processing.
CfgSize
The configured size of the memory pool.
Bnd
Specifies whether the memory pool has a fixed upper limit.
BlkCnt
The current number of allocated blocks in the memory pool.
CfgParm
The configuration parameter that declares the size of the pool being reported.

-memsets parameter

For the -memsets parameter, the following information is returned:
Name
The name of the memory set.
Address
The address of the memory set.
Id
The memory set identifier.
Size(Kb)
The size of the memory set in kilobytes.
Key
The memory set key (for UNIX operating systems only).
DBP
The database partition server that owns the memory set.
Type
The type of memory set.
Unrsv(Kb)
Memory not reserved for any particular pool. Any pool in the set can use this memory if needed.
Used(Kb)
Memory currently allocated to memory pools.
HWM(Kb)
Maximum memory ever allocated to memory pools.
Cmt(Kb)
All memory that has been committed by the DB2 database, and occupies physical RAM, paging space, or both.
Uncmt(Kb)
Memory not currently being used, and marked by the DB2 database to be uncommitted. Depending on the operating system, this memory could occupy physical RAM, paging space, or both.
CmtRt(Kb)
The largest contiguous area of committed memory that is available.
DcmtRt(Kb)
The largest contiguous area of uncommitted memory that is available.
HoldRt(Kb)
The largest contiguous area of committed memory that is available for volatile requests.
Sngl
The number of pre-allocated regions that are available for faster allocation.

-osinfo parameter

For the -osinfo parameter, the following information is returned:
CPU information: (On Windows, AIX®, HP-UX, Solaris and Linux operating systems)
TotalCPU
Total number of CPUs.
OnlineCPU
Number of CPUs online.
ConfigCPU
Number of CPUs configured.
Speed(MHz)
Speed, in MHz, of CPUs.
HMTDegree
Systems supporting hardware multithreading return a value showing the number of processors that will appear to be present on the operating system. On nonHMT systems, this value is always 1. On HMT systems, TOTAL reflects the number of logical CPUs. To get the number of physical CPUs, divide the total by THREADING DEGREE.
Timebase
Frequency, in Hz, of the timebase register increment. This is supported on Linux PPC only.
Cores/Socket
Number of cores per socket
Physical memory and swap in megabytes: (On Windows, AIX, HP-UX, Solaris and Linux operating systems)
TotalMemTotal
Size of memory in megabytes.
FreeMem
Amount of free memory in megabytes.
AvailMem
Amount of memory available to the product in megabytes.
TotalSwap
Total amount of swapspace in megabytes.
FreeSwap
Amount of swapspace free in megabytes.
Virtual memory in megabytes (On Windows, AIX, HP-UX, and Solaris operating systems)
Total
Total amount of virtual memory on the system in megabytes.
Reserved
Amount of reserved virtual memory in megabytes.
Available
Amount of virtual memory available in megabytes.
Free
Amount of virtual memory free in megabytes.
Operating system information (On Windows, AIX, HP-UX, Solaris and Linux operating systems)
OSName
Name of the operating system software.
NodeName
Name of the system.
Version
Version of the operating system.
Machine
Machine hardware identification.
Message queue information (On AIX, HP-UX, and Linux operating systems)
MsgSeg
System-wide total of SysV msg segments.
MsgMax
System-wide maximum size of a message.
MsgMap
System-wide number of entries in message map.
MsgMni
System-wide number of message queue identifiers for system.
MsgTql
System-wide number of message headers.
MsgMnb
Maximum number of bytes on a message queue.
MsgSsz
Message segment size.
Shared memory information (On AIX, HP-UX, and Linux operating systems)
ShmMax
System-wide maximum size of a shared memory segment in bytes.
ShmMin
System-wide minimum size of a shared memory segment in bytes.
ShmIds
System-wide number of shared memory identifiers.
ShmSeg
Process-wide maximum number of shared memory segments per process.
Semaphore information: (On AIX, HP-UX, and Linux operating systems)
SemMap
System-wide number of entries in semaphore map.
SemMni
System-wide maximum number of a semaphore identifiers.
SemMns
System-wide maximum number of semaphores on system.
SemMnu
System-wide maximum number of undo structures on system.
SemMsl
System-wide maximum number of semaphores per ID.
SemOpm
System-wide maximum number of operations per semop call.
SemUme
Process-wide maximum number of undo structures per process.
SemUsz
System-wide size of undo structure. Derived from semume.
SemVmx
System-wide maximum value of a semaphore.
SemAem
System-wide maximum adjust on exit value.
CPU load information (On Windows, AIX, HP-UX, Solaris, and Linux operating systems)
shortPeriod
The number of runable processes over the preceding 1 minute.
mediumPeriod
The number of runable processes over the preceding 5 minutes.
longPeriod
The number of runable processes over the preceding 15 minutes.
CPU Usage Information (percent) (On Windows, AIX, HP-UX, Solaris, and Linux operating systems)
Total
This shows the total percent of Usr + Sys CPU usage.
Usr
This shows the percent of CPU used by programs executing in user mode.
Sys
This shows the percent of CPU used by programs executing in system mode.
Wait
This shows the percent of time spent waiting for IO.
Idle
This shows the percent of time the CPU(s) is idle.
Note: These metrics are aggregated across all logical processors on the system. On the AIX operating system, the metrics are for the Workload Partition (WPAR) and Logical Partition (LPAR) where the DB2 server is running.
Disk information
BkSz(bytes)
File system block size in bytes.
Total(bytes)
Total number of bytes on the device in bytes.
Free(bytes)
Number of free bytes on the device in bytes.
Inodes
Total number of inodes.
FSID
File system ID.
DeviceType
Device type.
FSName
File system name.
MountPoint
Mount point of the file system.

-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

For the -recovery parameter, the following information is returned:
Database State
The state of the catalog partition in partitioned database environments if the database catalog partition fails. If the database catalog partition fails, the CATALOGNODEFAIL state is returned. Otherwise, no information is returned. This state can be displayed from any database partition.
Recovery Status
The internal recovery status.
Current Log
The current log being used by the recovery operation.
Current LSN
The current log sequence number.
Current LRI
The current LRI.
Current LSO
The current LSO.
Job Type
The type of recovery being performed. The possible values are:
  • 5: Crash recovery.
  • 6: Rollforward recovery on either the database or a table space.
Job ID
The job identifier.
Job Start Time
The time the recovery operation started.
Job Description
A description of the recovery activity. The possible values are:
  • Tablespace Rollforward Recovery
  • Database Rollforward Recovery
  • Crash Recovery
Invoker Type
How the recovery operation was invoked. The possible values are:
  • User
  • DB2
Total Phases
The number of phases required to complete the recovery operation.
Current phase
The current phase of the recovery operation.
Phase
The number of the current phase in the recovery operation.
Forward phase
The first phase of rollforward recovery. This phase is also known as the REDO phase.
Backward phase
The second phase of rollforward recovery. This phase is also known as the UNDO phase.
Metric
The units of work. The possible values are:
  • 1: Bytes.
  • 2: Extents.
  • 3: Rows.
  • 4: Pages.
  • 5: Indexes
TotWkUnits
The total number of units of work (UOW) to be done for this phase of the recovery operation.
TotCompUnits
The total number of UOWs that have been completed.

-reopt parameter

For the -reopt parameter, the following information is returned:
Dynamic SQL Statements
See -dynamic.
Dynamic SQL Environments
See the -dynamic.
Dynamic SQL Variations
See the -dynamic.
Reopt Values
Displays information about the variables that were used to reoptimize a given SQL statement. Information is not returned for variables that were not used. Valid values are:
AnchID
The hash anchor identifier.
StmtID
The statement identifier for this variation.
EnvID
The environment identifier for this variation.
VarID
The variation identifier.
OrderNum
Ordinal number of the variable that was used to reoptimize of the SQL statement
SQLZType
The variable type.
CodPg
The variable code page.
NulID
The flag indicating whether or not the value is null-terminated.
Len
The length in bytes of the variable value.
Data
The value used for the variable.

-reorgs parameter

For the -reorgs parameter, the following information is returned:
Index Reorg Stats:
Retrieval time
Retrieval time of this set of index reorg statistics information.
TabSpaceID
The table space identifier.
TableID
The table identifier.
Schema
Table schema.
TableName
The name of the table.
MaxPartition
Total number of partitions for the table being processed. For partition-level reorg, MaxPartition will always have a value of 1 since only a single partition is being reorganized. This field is only displayed for partitioned indexes.
PartitionID
The data partition identifier for the partition being processed. This field is only displayed for partitioned indexes.
Access
Access level, possible values are:
  • Allow none
  • Allow read
  • Allow write
Status
Current reorg status, one of:
  • In Progress (operation is in progress)
  • Completed (operation has completed successfully)
  • Stopped (operation has stopped due to error or interrupt)
Start time
Start time of this reorg session.
End time
End time of this reorg session.
Total duration
Total duration time of this reorg session.
Prev Index Duration
Reorg duration of the previous (completed) index.
Cur Index Start
Reorg start time of the current (in progress) index.
Cur Index
Sequence number of the current (in progress) index.
Max Index
Total number of indexes being monitored. This is not the same as total number of indexes on the table, because some system-generated indexes are not monitored.
Index ID
Index ID of the current (in progress) index.
Cur Phase
Sequence number of the current phase. Enclosed within the braces is the name of the current phase, one of:
  • Scan (the table is being scanned and sorted one data page at a time)
  • Build (the index is being built from the sorted input one row at a time)
  • Catchup (transactions that occurred while building the index are being replayed; only seen for index reorgs where access level is allow write)
Max Phase
Total number of phases for the current (in-progress) index; differs for different types of indexes.
CurCount
Units of work processed so far. Unit has a different meaning for each reorg phase, as follows:
  • Scan phase: number of data pages scanned
  • Build phase: number of rows processed
  • Catchup: number of transaction log records replayed
MaxCount
Total number of units for the current phase (see CurCount for explanation on units).
Total Row Count
Total number of rows processed. May or may not show up depending on the phase and index type.

See Sample output of the db2pd -reorgs index command.

Table Reorg Stats:
Address
A hexadecimal value.
TableName
The name of the table.
Start
The time that the table reorganization started.
End
The time that the table reorganization ended.
PhaseStart
The start time for a phase of table reorganization.
MaxPhase
The maximum number of reorganization phases that will occur during the reorganization. This value only applies to offline table reorganization.
Phase
The phase of the table reorganization. This value only applies to offline table reorganization. The possible values are:
  • Sort
  • Build
  • Replace
  • InxRecreat
CurCount
A unit of progress that indicates the amount of table reorganization that has been completed. The amount of progress represented by this value is relative to the value of MaxCount, which indicates the total amount of work required to reorganize the table.
MaxCount
A value that indicates the total amount of work required to reorganize the table. This value can be used in conjunction with CurCount to determine the progress of the table reorganization.
Status
The status of an online table reorganization. This value does not apply to offline table reorganizations. The possible values are:
  • Started
  • Paused
  • Stopped
  • Done
  • Truncat
Completion
The success indicator for the table reorganization. The possible values are:
  • 0: The table reorganization completed successfully.
  • -1: The table reorganization failed.
PartID
The data partition identifier. One row is returned for each data partition, showing the reorganization information.
MasterTbs
For partitioned tables, this is the logical table space identifier to which the partitioned table belongs. For non-partitioned tables, this value corresponds to the TbspaceID.
MasterTab
For partitioned tables, this is the logical table identifier of the partitioned table. For non-partitioned tables, this value corresponds to the TableID.
Type
The type of reorganization. The possible values are:
  • Online
  • Offline
IndexID
The identifier of the index that is being used to reorganize the table.
TempSpaceID
The table space in which the table is being reorganized.

-scansharing parameter

For the -scansharing parameter, the following fields are returned, specific to the headings:
Individual shared scan
  • Agent ID
  • Application ID
  • ScanMode (prewrap or postwrap)
  • IsScanWrappable
  • Scan speed
  • Time spent getting throttled
  • Relative location of scan in pages within group (for block index scans). Absolute location of scan in pages (for table and range scans)
  • Predicted speed category (SLOW or FAST)
  • Remaining pages to process (accurate for table and range scans). For block index scans, the optimizer estimate is returned instead.

See Sample output of the db2pd -scansharing command.

Sharing set
  • Table space ID
  • Table ID
  • Scan object (0 for table scans or ID of block index)
  • Number of groups
  • Sharing set footprint in pages
  • Table size in pages (for table scans and block index scans on nonpartitioned tables, and for range scans on partitioned tables; for block index scans on partitioned tables the value is unknown)
  • Fast scan speed (speed at which FAST scans are going)
  • Slow scan speed (speed at which SLOW scans are going)
Sharing group
  • Number of scans in the group
  • Group footprint (in number of pages)

-serverlist parameter

For the -serverlist parameter, the following information is returned:
Time
The time when the server list was cached
Database Name
The name of the database
Count
The number of entries in the server list
Hostname
The TCP/IP hostname of a member
Non-SSL Port
The non-SSL port that a member is listening on for client connections
SSL Port
The SSL TCP/IP port that a member is listening on for client connections
Priority
The relative load of a member, also known as the weight. A member (A) having a higher value compared with another member (B) indicates to the client that more work should be directed at member A.

-serviceclasses parameter

For the -serviceclasses parameter, the following fields are returned, specific to the headings:

Service class fields:
  • Service Class Name: Name of service class
  • Service Class ID: System generated ID of service class
  • Service Class Type: Type of service class: superclass or subclass
  • Service Class State (Effective and Catalog): State of service class: enabled or disabled
  • Effective Prefetch Priority and Catalog Prefetch Priority: Effective prefetch priority setting for service class that maps to priority recorded in SYSCAT.SERVICECLASSES
  • Effective Bufferpool Priority and Catalog Bufferpool Priority: Effective buffer pool priority setting for service class that maps to priority recorded in SYSCAT.SERVICECLASSES
  • Effective Outbound Correlator and Catalog Outbound Correlator: Effective outbound correlator setting for service class that maps to correlator recorded in SYSCAT.SERVICECLASSES)
  • CPU Shares: number of WLM dispatcher CPU shares configured for the service class
  • CPU Shares Type: WLM dispatcher CPU share type
  • CPU Limit: WLM dispatcher CPU limit configured for the service class
  • Last Statistics Reset Time: Timestamp of last statistics reset for service class
Service superclass fields:
  • Default Subclass ID: Service class ID of Default Subclass
  • Work Action Set ID: ID of work action set associated with service superclass
  • Collect Request Metrics: Setting of COLLECT REQUEST METRICS option for service class
  • Num Connections: Current number of coordinator and remote connections in service superclass
  • Num Coordinator Connections: Current number of coordinator connections in service superclass
  • Coordinator Connections HWM: High water mark for coordinator connections since last statistics reset
  • Associated Workload Occurrences (WLO): List of workload occurrences currently in service superclass
Service subclass fields:
  • Parent Superclass ID: Service class ID of parent superclass
  • Collect Activity Opt: Setting of COLLECT ACTIVITY DATA option for service subclass
  • Collect Aggr Activity Opt: Setting of COLLECT AGGREGATE ACTIVITY option for service subclass
  • Collect Aggr Request Opt: Setting of COLLECT AGGREGATE REQUEST option for service subclass
  • Act Lifetime Histogram Template ID: ID of Activity Lifetime Histogram Template
  • Act Queue Time Histogram Template ID: ID of Activity Queue Time Histogram Template
  • Act Execute Time Histogram Template ID: ID of Activity Execute Time Histogram Template
  • Act Estimated Cost Histogram Template ID: ID of Activity Estimated Cost Histogram Template
  • Act Interarrival Time Histogram Template ID: ID of Activity Interarrival Time Histogram Template
  • Request Execute Time Histogram Template ID: ID of Request Execute Time Histogram Template
  • Access Count: Current number of activities in service subclass
  • Activities HWM: High water mark for activities since last statistics reset, counting both activities that entered the system through this subclass and activities that you remap into this subclass by a REMAP ACTIVITY threshold action.
  • Activities Completed: Total number of activities completed since last statistics reset. If you remap an activity to a different subclass with a REMAP ACTIVITY action before it completes, then this activity counts only toward the total of the subclass it completes in.
  • Activities Rejected: Total number of activities rejected since last statistics reset
  • Activities Aborted: Total number of activities aborted since last statistics reset. If you remap an activity to a different subclass with a REMAP ACTIVITY action before it aborts, then this activity counts only toward the total of the subclass it aborts in.
  • Associated Agents: List of agent currently working in service subclass
  • Associated Non-agent threads: List of non-agent entities currently working in service subclass

See Sample output of the db2pd -serviceclasses command.

-sort parameter

For the -sort parameter, the following information is returned:
ApplHandl
The application handle, including the node and the index.
SortCB
The address of a sort control block
MaxRowSize
The sum of the maximum length of all columns of the row being sorted
EstNumRows
The Optimizer estimated number of rows that will be inserted into the sort
EstAvgRowSize
The Optimizer estimated average length of the rows being sorted
NumSMPSorts
The number of concurrent subagents processing this sort
NumSpills
The total number of times this sort has spilled to disk
KeySpec
A description of the type and length of each column being sorted
SortheapMem
The number of KB of sortheap memory reserved and allocated by this sort
NumSpilledRows
The total number of rows spilled to disk for this sort
NumBufferedRows
The total number of rows inserted into this sort since the last time it spilled

-static parameter

For the -static parameter, the following information is returned:
Static Cache:
Current Memory Used
The number of bytes used by the package cache.
Total Heap Size
The number of bytes internally configured for the package cache.
Cache Overflow flag state
A flag to indicate whether the package cache is in an overflow state.
Number of References
The number of references to packages in the package cache.
Number of Package Inserts
The number of package inserts into the package cache.
Number of Section Inserts
The number of static section inserts into the package cache.
Packages:
Schema
The qualifier of the package.
PkgName
The name of the package.
Version
The version identifier of the package.
UniqueID
The consistency token associated with the package.
NumSec
The number of sections that have been loaded.
UseCount
The usage count of the cached package.
NumRef
The number of times the cached package has been referenced.
Iso
The isolation level of the package.
QOpt
The query optimization of the package.
Blk
The blocking factor of the package.
Lockname
The lockname of the package.
Sections:
Schema
The qualifier of the package that the section belongs to.
PkgName
The package name that the section belongs to.
UniqueID
The consistency token associated with the package that the section belongs to.
SecNo
The section number.
NumRef
The number of times the cached section has been referenced.
UseCount
The usage count of the cached section.
StmtType
The internal statement type value for the cached section.
Cursor
The cursor name (if applicable).
W-Hld
Indicates whether the cursor is a WITH HOLD cursor.

-statisticscache parameter

For the -statisticscache parameter, the following information is returned:
Current Size
The current number of bytes used in the statistics cache.
Address
The address of the entry in the statistics cache.
Schema
The schema qualifier for the table.
Name
The name of the table.
LastRefID
The last process identifier that referenced the table.
LastStatsTime
The time for the latest statistics collection for the table.
Sts
The status of the entry. The possible values are:
  • V (valid).
  • I (invalid).

Additional information that can help IBM® Support to analyze and troubleshoot problems might also be returned.

For additional details about the returned information using the -statisticscache command parameter, see the topic "Catalog statistics tables".

-storagegroups parameter and storagepaths parameter

Both the -storagegroups parameter and the -storagepaths parameter return the following information:

Storage Group Configuration:
SGID
Storage group identifier.
Deflt
Indicates if the storage group is the current designated default storage group.
DataTag
An identifying tag used to uniquely identify and group data.
Name
Name of the storage group.
Storage Group Statistics:
SGID
Storage group identifier.
State
State of the storage group. One of the following values:
  • 0x0000000000000000 - SQLB_STORAGEGROUP_STATE_NORMAL
  • 0x0000000000000001 - SQLB_STORAGEGROUP_ALTER_PENDING
  • 0x0000000000000002 - SQLB_STORAGEGROUP_SKIP_ALTERS
  • 0x0000000000000004 - SQLB_STORAGEGROUP_KEEP_ON_DISK_PATHS
  • 0x0000000000000008 - SQLB_STORAGEGROUP_REDEFINE_CONTAINERS
  • 0x0000000000000010 - SQLB_STORAGEGROUP_CREATE_PENDING
  • 0x0000000000000020 - SQLB_STORAGEGROUP_DROP_PENDING
  • 0x0000000000000040 - SQLB_STORAGEGROUP_RENAME_PENDING
NumPaths
Number of storage paths defined in this storage group.
NumDropPen
Number of storage paths in the Drop Pending state.
Storage Group Paths:
SGID
Storage group identifier.
PathID
Storage path identifier.
PathState
Current state of the storage path: NotInUse, InUse, or DropPending.
PathName
Name of an automatic storage path defined for the database. If the path contains a database partition expression, it is included, in parentheses, after the expanded path.

See Sample output of the dp2pd -storagegroups command and db2pd -storagepaths command.

-sysplex parameter

For the -sysplex parameter, the following information is returned:
Alias
The database alias.
Location Name
The unique name of the database server.
Count
The number of entries found in the list of servers.
IP Address
The IP address of the server
Port
The IP port being used by the server.
Priority
The normalized Workload Manager (WLM) weight.
Connections
The number of active connections to this server.
Status
The status of the connection. The possible values are:
  • 0: Healthy.
  • 1: Unhealthy. The server is in the list but a connection cannot be established. This entry currently is not considered when establishing connections.
  • 2: Unhealthy. The server was previously unavailable, but currently it will be considered when establishing connections.
PRDID
The product identifier of the server as of the last connection.

-tablespaces parameter

For the -tablespaces parameter, the output is organized into four segments:

Table space Configuration:
Id
The table space ID.
Type
The type of table space. The possible values are:
  • SMS
  • DMS
Content
The type of content. The possible values are:
  • Regular
  • Large
  • SysTmp
  • UsrTmp
PageSz
The page size used for the table space.
ExtentSz
The size of an extent in pages.
Auto
Indicates whether the prefetch size is set to AUTOMATIC. The possible values are:
  • Yes
  • No
Prefetch
The number of pages read from the table space for each range prefetch request.
BufID
The ID of the buffer pool that this table space is mapped to.
BufIDDisk
The ID of the buffer pool that this table space will be mapped to at next startup.
FSC
File system caching mode: (For more information, see fs_caching - file system caching monitor element)
  • Yes
  • No
  • Def (default)
NumCntrs
The number of containers owned by a table space.
MaxStripe
The maximum stripe set currently defined in the table space (applicable to DMS table spaces only).
LastConsecPg
The last consecutive object table extent.
Name
The name of the table space.
Table space Statistics:
Id
The table space ID.
TotalPages
For DMS table spaces, the sum of the gross size of each of the table space's containers (reported in the total pages field of the container).

For SMS table spaces, this value reflects the number of pages in the filesystem owned by the table space.

UsablePgs
For DMS table spaces, the sum of the net size of each of the table space's containers (reported in the usable pages field of the container).

For SMS table spaces, this value reflects the number of pages in the filesystem owned by the table space.

UsedPgs
For DMS table spaces, the total number of pages currently in use in the table space.

For SMS table spaces, this value reflects the number of pages in the filesystem owned by the table space.

PndFreePgs
The number of pages that are not available for use but will be available if all the currently outstanding transactions commit.
FreePgs
For DMS table spaces, the number of pages available for use in the table space.

For SMS table spaces, this value is always 0.

HWM
The highest allocated page in the table space.
Max HWM
The maximum HWM for the table space since the instance was started.
State
  • 0x0000000 - NORMAL
  • 0x0000001 - QUIESCED: SHARE
  • 0x0000002 - QUIESCED: UPDATE
  • 0x0000004 - QUIESCED: EXCLUSIVE
  • 0x0000008 - LOAD PENDING
  • 0x0000010 - DELETE PENDING
  • 0x0000020 - BACKUP PENDING
  • 0x0000040 - ROLLFORWARD IN PROGRESS
  • 0x0000080 - ROLLFORWARD PENDING
  • 0x0000100 - RESTORE PENDING
  • 0x0000200 - DISABLE PENDING
  • 0x0000400 - REORG IN PROGRESS
  • 0x0000800 - BACKUP IN PROGRESS
  • 0x0001000 - STORAGE MUST BE DEFINED
  • 0x0002000 - RESTORE IN PROGRESS
  • 0x0004000 - OFFLINE
  • 0x0008000 - DROP PENDING
  • 0x0010000 - WRITE SUSPENDED
  • 0x0020000 - LOAD IN PROGRESS
  • 0x0200000 - STORAGE MAY BE DEFINED
  • 0x0400000 - STORAGE DEFINITION IS IN FINAL STATE
  • 0x0800000 - STORAGE DEFINITION CHANGED PRIOR TO ROLLFORWARD
  • 0x1000000 - DMS REBALANCER IS ACTIVE
  • 0x2000000 - DELETION IN PROGRESS
  • 0x4000000 - CREATION IN PROGRESS
MinRecTime
The minimum recovery time for the table space.
NQuiescers
The number of quiescers.
PathsDropped
For automatic storage table spaces, specifies whether one or more containers reside on a storage path that has been dropped. The possible values are:
  • Yes
  • No
TrackmodState
The modification status of a table space with respect to the last or next backup. The possible values are:
  • Clean - No modifications have occurred in the table space since the previous backup. If an incremental or delta backup is executed at this time, no data pages from this table space would be backed up.
  • Dirty - Table space contains data that needs to be picked up by the next backup.
  • InIncremental - Table space contains modifications that were copied into an incremental backup. This state is in a Dirty state relative to a full backup such that a future incremental backup needs to include some pages from this pool. This state is also in a Clean state such that a future delta backup does not need to include any pages from this pool.
  • ReadFull - The latest table space modification state change was caused by a dirty table space being read by a full backup that might not have completed successfully, or is currently in progress.
  • ReadIncremental - The latest table space modification state change was caused by a dirty table space being read by an incremental backup that might not have completed successfully, or is currently in progress.
  • n/a - The trackmod configuration parameter is set to No. Therefore, no table space modification status information is available.
Table space Autoresize Statistics:
Id
The table space ID.
AS
Indicates whether or not the table space is using automatic storage. The possible values are:
  • Yes
  • No
AR
Indicates whether or not the table space is enabled to be automatically resized. The possible values are:
  • Yes
  • No
InitSize
For automatic storage table spaces, the value of this parameter is the initial size of the table space in bytes.
IncSize
If the value of this parameter is -1, the database manager automatically determines an appropriate value. For automatically resized table spaces, if the value of the IIP field is No, the value of this parameter is the size, in bytes, that the table space will automatically be increased by (per database partition) when the table space is full and a request for space is made. If the value of the IIP field is Yes, the value of this parameter is a percentage.
IIP
For automatically resized table spaces, the value of this parameter indicates whether the increment value in the IncSize field is a percent or not. The possible values are:
  • Yes
  • No
MaxSize
For automatically resized table spaces, the value of this parameter specifies the maximum size, in bytes, to which the table space can automatically be increased (per database partition). A value of NONE indicates that there is no maximum size.
LastResize
The timestamp of the last successful automatic resize operation.
LRF
Last resize failed indicates whether the last automatic resizing operation was successful or not. The possible values are:
  • Yes
  • No

Table space Storage Statistics:

Id
The table space ID.
DataTag
An identifying tag used to uniquely identify and group data.
Rebalance
Indicates if a rebalance is active.
SGID
For automatic storage managed table spaces, indicates the storage group the table space is associated with.
SourceSGID
For automatic storage managed table spaces that are changing storage group association, indicates the source storage group the table space was associated with.
Table space Containers:
TspId
The ID of the table space that owns the container.
ContainNum
The number assigned to the container in the table space.
Type
The type of container. The possible values are:
  • Path
  • Disk
  • File
  • Striped Disk
  • Striped File
TotalPgs
The number of pages in the container.
UsablePgs
The number of usable pages in the container.
StripeSet
The stripe set where the container resides (applicable to DMS table spaces only).
Container
The name of the container.
PathID
For automatic storage table spaces, the identifier of the storage path on which the container resides.

See Sample output of the db2pd -tablespaces command.

-tcbstats parameter

For the -tcbstats parameter, the following information is returned:
TCB Table Information:
TbspaceID
The table space identifier.
TableID
The table identifier.
PartID
For partitioned tables, this is the data partition identifier. For non-partitioned table this will display 'n/a'.
MasterTbs
For partitioned tables, this is the logical table space identifier to which the partitioned table belongs. For non-partitioned tables, this value corresponds to the TbspaceID.
MasterTab
For partitioned tables, this is the logical table identifier of the partitioned table. For non-partitioned tables, this value corresponds to the TableID.
TableName
The name of the table.
SchemaNm
The schema that qualifies the table name.
ObjClass
The object class. The possible values are:
  • Perm (permanent).
  • Temp (temporary).
DataSize
The number of pages in the data object.
LfSize
The number of pages in the long field object.
LobSize
The number of pages in the large object.
XMLSize
The number of pages in the XML object.
TCB Table Stats:
TableName
The name of the table.
SchemaNm
The schema that qualifies the table name.
Scans
The number of scans that have been performed against the table.
UDI
The number of update, delete, and insert operations that have been performed against the table since the last time that the table statistics were updated through the background statistics collection process or manually using the RUNSTATS command.
RTSUDI
The number of update, delete, and insert operations that have been performed against the table since the last time that the table statistics were updated by real-time statistics gathering, background statistics collection process, or manual RUNSTATS.
PgReorgs
The number of page reorganizations performed.
NoChgUpdts
The number of updates that did not change any columns in the table.
Reads
The number of rows read from the table when the table switch was on for monitoring.
FscrUpdates
The number of updates to a free space control record.
Inserts
The number of insert operations performed on the table.
Updates
The number of update operations performed on the table.
Deletes
The number of delete operations performed on the table.
OvFlReads
The number of overflows read on the table when the table switch was on for monitoring.
OvFlCrtes
The number of new overflows that were created.
CCLogReads
The number of times the currently committed version of a row was retrieved for the table.
StoredBytes
This column corresponds to the “Total stored temp bytes” from the db2pd –temptable output.
BytesSaved
This column corresponds to the “Total bytes saved” value from the db2pd –temptable output.
PgDictsCreated
The total number of successfully created page-level dictionaries.
Note
The following data is only displayed when the -all or -index option is specified with the -tcbstats parameter.
TCB Index Information:
InxTbspace
The table space where the index resides.
ObjectID
The object identifier of the index.
PartID
For partitioned tables, the data partition identifier. For nonpartitioned tables, N/A is displayed.
TbspaceID
The table space identifier.
TableID
The table identifier.
MasterTbs
For partitioned tables, this is the logical table space identifier to which the partitioned table belongs. For non-partitioned tables, this value corresponds to the TbspaceID.
MasterTab
For partitioned tables, this is the logical table identifier of the partitioned table. For non-partitioned tables, this value corresponds to the TableID.
TableName
The name of the table.
SchemaNm
The schema that qualifies the table name.
IID
The index identifier.
IndexObjSize
The number of pages in the index object.The value reported in IndexObjSize has been deprecated. If you perform a reorganization to reclaim extents, IndexObjSize output does not accurately reflect the number of pages in the index object because the value still includes the pages that were released during the reorganization. You should use instead the INDEX_OBJECT_P_SIZE or INDEX_OBJECT_L_SIZE columns of the ADMIN_GET_INDEX_INFO table function to obtain accurate values.
TCB Index Stats:
TableName
The name of the table.
IID
The index identifier.
PartID
For partitioned tables, the data partition identifier. For nonpartitioned tables, N/A is displayed.
EmpPgDel
The number of empty leaf nodes that were deleted.
RootSplits
The number of key insert or update operations that caused the index tree depth to increase.
BndrySplits
The number of boundary leaf splits that result in an insert operation into either the lowest or the highest key.
PseuEmptPg
The number of leaf nodes that are marked as being pseudo empty.
EmPgMkdUsd
The number of pseudo empty pages that have been reused.
Scans
The number of scans against the index.
IxOnlyScns
The number of index-only scans that were performed on the index (scans that were satisfied by access to only an index), regardless of how many pages were read during the scan.
KeyUpdates
The number of updates to the key.
InclUpdats
The number of included column updates.
NonBndSpts
The number of non-boundary leaf splits.
PgAllocs
The number of allocated pages.
Merges
The number merges performed on index pages.
PseuDels
The number of keys that are marked as pseudo deleted.
DelClean
The number of pseudo deleted keys that have been deleted.
IntNodSpl
The number of intermediate level splits.

-temptable parameter

In order to calculate the cumulative compression ratio across all of the temporary tables, the following formula can be used:
     % Compression = ( Total Bytes Saved ) /
           ( Total Bytes Saved + Total Stored Temp Bytes )
Note:
  • The term Eligible indicates temporary tables that meet the compression criteria.
  • The term Compressed indicates temporary tables that finally have sufficient data inserted to be compressed.
hotel26:/home/billyp> db2pd -db billdb –temptable
System Temp Table Stats:
        Number of Temp Tables   : 0
                Comp Eligible Temps     : 0
                Compressed Temps        : 0
                Total Temp Bytes        : 0
                Total Bytes Saved       : 0
                Total Compressed Rows   : 0
                Total Temp Table Rows:  : 0

User Temp Table Stats:
        Number of Temp Tables   : 0
                Comp Eligible Temps     : 0
                Compressed Temps        : 0
                Total Stored Temp Bytes : 0
                Total Bytes Saved       : 0
                Total Compressed Rows   : 0
                Total Temp Table Rows   : 0
All of the counters can be reset to zero by using the hidden reset option.
hotel26:/home/billyp> db2pd -db bill -temptable reset
        Resetting counters to 0.

See Sample output of the db2pd -temptable command.

-thresholds parameter

For the -thresholds parameter, the following information is returned:

If the threshold is a queuing threshold, the queue section will also show:

-transactions parameter

For the -transactions parameter, the following information is returned:
ApplHandl
The application handle of the transaction.
TranHdl
The transaction handle of the transaction.
Locks
The number of locks held by the transaction.
State
The transaction state.
Tflag
The transaction flag. The possible values are:
  • 0x00000002. This value is only written to the coordinator node of a two-phase commit application, and it indicates that all subordinate nodes have sent a "prepare to commit" request.
  • 0x00000020. The transaction must change a capture source table (used for data replication only).
  • 0x00000040. Crash recovery considers the transaction to be in the prepare state.
  • 0x00010000. This value is only written to the coordinator partition in a partitioned database environment, and it indicates that the coordinator partition has not received a commit request from all subordinate partitions in a two-phase commit transaction.
  • 0x00040000. The rolling back of the transaction is pending.
  • 0x01000000. The transaction resulted in an update on a database partition server that is not the coordinator partition.
  • 0x04000000. Loosely coupled XA transactions are supported.
  • 0x08000000. Multiple branches are associated with this transaction and are using the loosely coupled XA protocol.
  • 0x10000000. A data definition language (DDL) statement has been issued, indicating that the loosely coupled XA protocol cannot be used by the branches participating in the transaction.
Tflag2
Transaction flag 2. The possible values are:
  • 0x00000004. The transaction has exceeded the limit specified by the num_log_span database configuration parameter.
  • 0x00000008. The transaction resulted because of the running of a DB2 utility.
  • 0x00000020. The transaction will cede its locks to an application with a higher priority (this value ordinarily occurs for jobs that the DB2 database system automatically starts for self tuning and self management).
  • 0x00000040. The transaction will not cede its row-level locks to an application with a higher priority (this value ordinarily occurs for jobs that the DB2 database system automatically starts for self-tuning and self-management)
Firstlsn
First LSN of the transaction.
Lastlsn
Last LSN of the transaction.
Firstlso
First LSO of the transaction.
Lastlso
Last LSO of the transaction.
SpaceReserved
The amount of log space that is reserved for the transaction.
LogSpace
The total log space that is required for the transaction, including the used space and the reserved space for compensation log records.
TID
Transaction ID.
AxRegCnt
The number of applications that are registered for a global transaction. For local transactions, the value is 1.
GXID
Global transaction ID. For local transactions, the value is 0.
ClientUserID
Client userid for the transaction, which is the same as tpmon_client_userid (TP Monitor Client User ID monitor element).
ClientWrkstnName
Client workstation name for the transaction, which is the same as tpmon_client_wkstn (TP Monitor Client Workstation Name monitor element).
ClientApplName
Client application name driving the transaction, which is the same as tpmon_client_app (TP Monitor Client Application monitor element).
ClientAccntng
Accounting string of the client driving the transaction, which is the same as tpmon_acc_str (TP Monitor Client Accounting String monitor element).
Total Application commits
The total number of application commits that have been made.
Total Application rollbacks
The total number of application rollbacks that have been made.

See Sample output of the db2pd -transactions command.

-utilities parameter

For the -utilities parameter, the following information is returned:
ID
Unique identifier corresponding to the utility invocation.
Type
Identifies the class of the utility.
State
Describes the state of the utility.
Invoker
Describes how a utility was invoked.
Priority
Specifies the amount of relative importance of a throttled utility with respect to its throttled peers. A priority of 0 implies that a utility is executing unthrottled. Non-zero priorities must fall in the range of 1-100, with 100 representing the highest priority and 1 representing the lowest.
StartTime
Specifies the date and time when the current utility was originally invoked.
DBName
Identifies the database operated on by the utility.
NumPhases
Specifies the number of phases a utility has.
CurPhases
Specifies the phase that is currently executing.
Description
A brief description of the work a utility is performing. This includes the load operation ID and the application ID.

-vmstat parameter

For the -vmstat parameter, the following information is returned:
Run:
r
Processor Queue Length at the time of sampling. This is a single queue for all processors and counts ready threads only.
Memory:
used
Committed Kilobytes in virtual memory
free
Available physical memory available in Kilobytes
pi
Pages read in from disk per second to resolve hard page faults
po
Pages written out per second to free up physical memory
System:
int/s
Interrupts per second during the interval. Includes activity from devices such as disk, network, and system clock
cs/s
Context Switches per second during the interval
sc/s
System Calls per second during the interval
CPU:
usr
Percentage of CPU time in user mode
sys
Percentage of CPU time in priveleged mode. This includes the interrupt time
int
Percentage of CPU time servicing interrupts.

-wlocks parameter

For the -wlocks parameter, the following information is returned:
ApplHandl
The application handle, including the node and the index.
TranHdl
The transaction handle that is requesting the lock.
LockName
The name of the lock.
Type
The type of lock.
Mode
The lock mode. The possible values are:
  • IS
  • IX
  • S
  • SIX
  • X
  • IN
  • Z
  • U
  • NS
  • NW
Conv
The lock mode to which the lock will be converted after the lock wait ends.
Sts
The lock status. The possible values are:
  • G (granted)
  • C (converting)
  • W (waiting)
CoorEDU
The EDU ID of the coordinator agent for the application.
AppName
The name of the application.
AuthID
The authorization identifier.
AppID
The application ID. This values is the same as the appl_id monitor element data.
AppNode
The application node of the agent.
TableNm
The table name of the agent that is waiting on the lock.
SchemaNm
The schema name of the agent that is waiting on the lock.

See Sample output of the db2pd -wlocks command.

-workactionsets parameter

For the -workactionsets parameter, the following information is returned:

-workclasssets parameter

For the -workclasssets parameter, the following information is returned:

-workloads parameter

For the -workloads parameter, the following information is returned, specific to the headings:

Workload definitions
  • Workload ID and name
  • Database access permission for workload occurrences
  • Maximum degree of parallelism
  • Number of concurrent workload occurrences
  • Workload thresholds
  • Associated service class
  • Statistics collection settings
  • Histogram template IDs
Usage privilege holders
  • Workload ID
  • Type of holder
  • Authorization ID
Local partition workload statistics
  • Workload ID and name
  • Workload occurrence statistics
  • Time since last statistics reset
  • Activity statistics

See Sample output of the db2pd -workloads command.

Sample output

-addnode
The following example is a sample of the output of the db2pd -addnode command:
-------------------------------------------------------------------------
Summary of add partition processing done for partition[50]
-------------------------------------------------------------------------
00:Creating database partitions                                   : True
01:Database partitions are created                                : True
08:Collecting storage information                                 : True
09:Storage information is collected                               : True
11:FCM Send & Receive daemons are blocked                         : True
12:FCM Send & Receive daemons are reactivated                     : True
13:db2start processing is  complete                               : True

Conflicting states or activities for add partition for partition[50]

-------------------------------------------------------------------------
  [14] Messages found for partition [50]
-------------------------------------------------------------------------
[Fri Oct 24 16:16:27 2008]:Addnode agent:Got automatic storage details
[Fri Oct 24 16:16:28 2008]:Addnode agent:Skeleton datbase is created
[Fri Oct 24 16:16:28 2008]:Addnode agent:Scanning for db alias=[PE      ] name=[PE      ]
[Fri Oct 24 16:16:28 2008]:Addnode agent:Found db alias=[PE      ] name=[PE      ]
[Fri Oct 24 16:16:28 2008]:Addnode agent:Instance directory already exists
[Fri Oct 24 16:16:28 2008]:Addnode agent:Node directory already exists
[Fri Oct 24 16:16:28 2008]:Addnode agent:Node directory is created
[Fri Oct 24 16:16:29 2008]:Addnode agent:Getting automatic storage details
[Fri Oct 24 16:16:29 2008]:Addnode agent:Got automatic storage details
[Fri Oct 24 16:16:30 2008]:Addnode agent:Skeleton datbase is created
[Fri Oct 24 16:16:30 2008]:Addnode agent:Database activation is not required
[Fri Oct 24 16:16:30 2008]:Addnode agent:Database activation is complete
[Fri Oct 24 16:16:30 2008]:Addnode agent:Online mode processing is complete
[Fri Oct 24 16:16:30 2008]:db2start is complete
oldviewapps
Returns information about which applications see the number of database partition servers in the instance before the add database partition server operation occurred.
The following example is a sample of the output of the db2pd -addnode oldviewsapps command:
-------------------------------------------------------------------------
Summary of add partition processing done for partition[0]
-------------------------------------------------------------------------

Conflicting states or activities for add partition for partition[0]
-------------------------------------------------------------------------

Applications with old view of instance for partition [0]
-------------------------------------------------------------------------
App.Handle(00000000,00000072) view has [3] nodes, instance has [4] nodes
App.Handle(00000000,00000065) view has [3] nodes, instance has [4] nodes
App.Handle(00000000,00000071) view has [3] nodes, instance has [4] nodes
App.Handle(00000000,00000005) view has [3] nodes, instance has [4] nodes
App.Handle(00000000,00000051) view has [3] nodes, instance has [4] nodes
App.Handle(00000000,00000070) view has [3] nodes, instance has [4] nodes
App.Handle(00000000,00000069) view has [3] nodes, instance has [4] nodes
App.Handle(00000000,00000068) view has [3] nodes, instance has [4] nodes
App.Handle(00000001,00000058) view has [3] nodes, instance has [4] nodes
App.Handle(00000000,00000067) view has [3] nodes, instance has [4] nodes
App.Handle(00000000,00000073) view has [3] nodes, instance has [4] nodes
detail
When used with the db2pd command, returns detailed information about the add database partition server operation, including the step in progress and events that are incompatible with the add database partition server operation. When used with the oldviewapps option, also returns information about which applications have a view of the instance that does not include recently added database partition servers.
The following example is a sample of the output of the db2pd -addnode detail command:
-------------------------------------------------------------------------
Add partition processing with detail for partition[50]
-------------------------------------------------------------------------
00:Creating database partitions                                   : True
01:Database partitions are created                                : True
02:Dropping database entries                                      : False
03:Dropping db entries are completed                              : False
04:Activating databases explicitly                                : False
05:Database explicit activation is completed                      : False
06:Updating database configuration                                : False
07:Database configuration is updated                              : False
08:Collecting storage information                                 : True
09:Storage information is collected                               : True
10:Add partition operation is complete                            : False
11:FCM Send & Receive daemons are blocked                         : True
12:FCM Send & Receive daemons are reactivated                     : True
13:db2start processing is  complete                               : True

Conflicting states or activities for add partition for partition[50]
-------------------------------------------------------------------------
restricted          :False
db2start            :False
db2stop             :False
instance quiesced   :False
database quiesced   :False
quiesce instance    :False
unquiesce instance  :False
quiesce db          :False
unquiesce db        :False
activate db         :False
deactivate db       :False
exclusive use of db :False
create db           :False
drop db             :False
create tablespace   :False
alter tablespace    :False
drop tablespace     :False
add partition       :False
backup database     :False
restore database    :False
snapshot restore    :False

[14] Messages found for partition [50]
-------------------------------------------------------------------------
[Fri Oct 24 16:16:27 2008]:Addnode agent:Got automatic storage details
[Fri Oct 24 16:16:28 2008]:Addnode agent:Skeleton datbase is created
[Fri Oct 24 16:16:28 2008]:Addnode agent:Scanning for db alias=[PE      ] name=[PE      ]
[Fri Oct 24 16:16:28 2008]:Addnode agent:Found db alias=[PE      ] name=[PE      ]
[Fri Oct 24 16:16:28 2008]:Addnode agent:Instance directory already exists
[Fri Oct 24 16:16:28 2008]:Addnode agent:Node directory already exists
[Fri Oct 24 16:16:28 2008]:Addnode agent:Node directory is created
[Fri Oct 24 16:16:29 2008]:Addnode agent:Getting automatic storage details
[Fri Oct 24 16:16:29 2008]:Addnode agent:Got automatic storage details
[Fri Oct 24 16:16:30 2008]:Addnode agent:Skeleton datbase is created
[Fri Oct 24 16:16:30 2008]:Addnode agent:Database activation is not required
[Fri Oct 24 16:16:30 2008]:Addnode agent:Database activation is complete
[Fri Oct 24 16:16:30 2008]:Addnode agent:Online mode processing is complete
[Fri Oct 24 16:16:30 2008]:db2start is complete

Total [00] Conflicting application handles for partition [50]

------------------------------------------------------------------------- 
Conflicting operations are shown as in the following example:
Total [01] Conflicting application handles for partition [20]
-------------------------------------------------------------------------
Agents for app_handle 00000000 00000052 : Activity occurrence:[1] time(s) ActivityName:[exclusive use of db]
The following example is a sample of the output of the db2pd -addnode oldviewapps detail command:
-------------------------------------------------------------------------
Add partition processing with detail for partition[0]
-------------------------------------------------------------------------
00:Creating database partitions                                   : False
01:Database partitions are created                                : False
02:Dropping database entries                                      : False
03:Dropping db entries are completed                              : False
04:Activating databases explicitly                                : False
05:Database explicit activation is completed                      : False
06:Updating database configuration                                : False
07:Database configuration is updated                              : False
08:Collecting storage information                                 : False
09:Storage information is collected                               : False
10:Add partition operation is complete                            : False
11:FCM Send & Receive daemons are blocked                         : False
12:FCM Send & Receive daemons are reactivated                     : False
13:db2start processing is  complete                               : False

Conflicting states or activities for add partition for partition[0]
-------------------------------------------------------------------------
restricted                    :False
db2start                      :False
db2stop                       :False
instance quiesced             :False
database quiesced             :False
quiesce instance              :False
unquiesce instance            :False
quiesce db                    :False
unquiesce db                  :False
activate db                   :False
deactivate db                 :False
exclusive use of db           :False
create db                     :False
drop db                       :False
create tablespace             :False
alter tablespace              :False
drop tablespace               :False
add partition                 :False
backup database               :False
restore database              :False
snapshot restore              :False
create/alter nodegroup        :False
drop nodegroup                :False
add storage                   :False
redistribute                  :False

Total [00] Conflicting application handles for partition [0]
-------------------------------------------------------------------------

Applications with old view of instance for partition [0]
-------------------------------------------------------------------------
App.Handle(00000000,00000072) view has [3] nodes, instance has[4] nodes
[Viewnodes:0:1:2:]
App.Handle(00000000,00000065) view has [3] nodes, instance has[4] nodes
[Viewnodes:0:1:2:]
App.Handle(00000000,00000071) view has [3] nodes, instance has[4] nodes
[Viewnodes:0:1:2:]
App.Handle(00000000,00000005) view has [3] nodes, instance has[4] nodes
[Viewnodes:0:1:2:]
App.Handle(00000000,00000051) view has [3] nodes, instance has[4] nodes
[Viewnodes:0:1:2:]
App.Handle(00000000,00000070) view has [3] nodes, instance has[4] nodes
[Viewnodes:0:1:2:]
App.Handle(00000000,00000069) view has [3] nodes, instance has[4] nodes
[Viewnodes:0:1:2:]
App.Handle(00000000,00000068) view has [3] nodes, instance has[4] nodes
[Viewnodes:0:1:2:]
App.Handle(00000001,00000058) view has [3] nodes, instance has[4] nodes
[Viewnodes:0:1:2:]
App.Handle(00000000,00000067) view has [3] nodes, instance has[4] nodes
[Viewnodes:0:1:2:]
App.Handle(00000000,00000073) view has [3] nodes, instance has[4] nodes
[Viewnodes:0:1:2:]
-apinfo
The following example is a sample of the output of the db2pd -apinfo command. If the MON_DEADLOCK database parameter is set to HISTORY and there is an active lock event monitor, then the db2pd -apinfo command displays the list of past activities of current UOW. The following example is a sample of the output displayed:
$ db2pd -apinfo AppHdl -db sample

Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:01:07 -- Date 2012-11-21-21.16.35.182584

snapapp Time:   11/21/2012 21:16:35

Application :
  Address :                0x0000000206490080
  AppHandl [nod-index] :   7        [000-00007]
  TranHdl :                3
  Application PID :        23044
  Application Node Name :  hotel49
  IP Address:              n/a
  Connection Start Time :  (1353550528)Wed Nov 21 21:15:28 2012
  Client User ID :         juntang
  System Auth ID :         JUNTANG
  Coordinator EDU ID :     18
  Coordinator Member :     0
  Number of Agents :       1
  Locks timeout value :    NotSet
  Locks Escalation :       No
  Workload ID :            1
  Workload Occurrence ID : 1
  Trusted Context :        n/a
  Connection Trust Type :  non trusted
  Role Inherited :         n/a
  Application Status :     UOW-Waiting  
  Application Name :       db2bp
  Application ID :         *LOCAL.juntang.121122021528
  ClientUserID :           n/a
  ClientWrkstnName :       n/a
  ClientApplName :         n/a
  ClientAccntng :          n/a
  CollectActData:          N
  CollectActPartition:     C
  SectionActuals:          N
  UOW start time :         11/21/2012 21:15:57.181341
  UOW stop time :          11/21/2012 21:15:30.354421

  Last executed statements :
    Package cache ID :                            0x0000027500000001
    Anchor ID :                                   629
    Statement UID :                               1
    SQL Type :                                    Dynamic
    Statement Type :                              DML, Select(blockable)
    Statement :                                   select * from org

  List of current activities :
    Activity ID :                                 2
    UOW-ID :                                      2
    Package schema :                              NULLID
    Package name :                                SQLC2K24
    Package Version :
    Consistency Token :                           AAAAAJHc
    Section number :                              1
    Statement number :                            1
    Isolation :                                   CS
    Effective degree :                            0
    Number of subagent(s) :                       1
    Sourece ID :                                  0
    Cursor ID :                                   0
    Nesting level :                               0
    Invocation ID :                               0
    Package cache ID :                            0x0000034300000001
    Anchor ID :                                   835
    Statement UID :                               1
    SQL Type :                                    Dynamic
    Statement Type :                              DML, Select (blockable)
    Statement :                                   select * from employee
    Entry time :                                  11/21/2012 21:16:07.179827
    Local start time :                            11/21/2012 21:16:07.179830
    Last reference time :                         11/21/2012 21:16:07.179827

  List of past activities of current UOW :
    Activity ID :                                 3
    UOW-ID :                                      2
    Package schema :                              NULLID
    Package name :                                SQLC2K24
    Package Version :
    Consistency Token :                           AAAAAJHc
    Section number :                              201
    Statement number :                            1
    Isolation :                                   CS
    Effective degree :                            0
    Number of subagent(s) :                       1
    Sourece ID :                                  0
    Cursor ID :                                   0
    Nesting level :                               0
    Invocation ID :                               0
    Package cache ID :                            0x0000027500000001
    Anchor ID :                                   629
    Statement UID :                               1
    SQL Type :                                    Dynamic
    Statement Type :                              DML, Select (blockable)
    Statement :                                   select * from org
    Entry time :                                  11/21/2012 21:16:19.068520
    Local start time :                            11/21/2012 21:16:19.068523
    Last reference time :                         11/21/2012 21:16:19.069663

    Activity ID :                                 1
    UOW-ID :                                      2
    Package schema :                              NULLID
    Package name :                                SQLC2K24
    Package Version :
    Consistency Token :                           AAAAAJHc
    Section number :                              201
    Statement number :                            1
    Isolation :                                   CS
    Effective degree :                            0
    Number of subagent(s) :                       1
    Sourece ID :                                  0
    Cursor ID :                                   0
    Nesting level :                               0
    Invocation ID :                               0
    Package cache ID :                            0x0000030100000001
    Anchor ID :                                   769
    Statement UID :                               1
    SQL Type :                                    Dynamic
    Statement Type :                              DML, Select (blockable)
    Statement :                                   select * from dept
    Entry time :                                  11/21/2012 21:15:57.270305
    Local start time :                            11/21/2012 21:15:57.270309
    Last reference time :                         11/21/2012 21:15:57.272555
-catalogcache
The following example is a sample of the SYSTABLES and TABLESPACES output of the db2pd -catalogcache command:
Catalog Cache:
Configured Size         819200
Current Size            537464
Maximum Size            4294901760
High Water Mark         589824

SYSTABLES:
Address            Schema   Name               Type TableID TbspaceID LastRefID  CatalogCacheLoadingLock    CatalogCacheUsageLock      Sts
0x00002B3AD9CB5C40 SYSCAT   TABLESPACES        V    0       0         165        010000003A2B0000405CCBD9C3 000005000C110000405CCBD9C3 V
0x00002B3AD9E97680 DBUSER1  SALES              T    16      2         164        010000003A2B00008076E9D9C3 000005000E1B00008076E9D9C3 S
0x00002B3AD9E5F920 DBUSER1  VSTAFAC2           V    0       0         164        010000003A2B000020F9E5D9C3 00000500001D000020F9E5D9C3 V
0x00002B3AD9BEDD60 DBUSER1  PRODUCT            T    4       4         164        010000003A2B000060DDBED9C3 00000500061D000060DDBED9C3 S
0x00002B3AD9E62920 SYSIBM   SYSPERIODS         T    164     0         164        010000003A2B00002029E6D9C3 00000500050800002129E6D9C3 V
0x00002B3AD9E6E1A0 DBUSER1  EMP_PHOTO          T    7       2         164        010000003A2B0000A0E1E6D9C3 00000500021B0000A0E1E6D9C3 V
0x00002B3AD9E5A500 SYSPUBLI HMON_COLLECTION    0    0       0         164        010000003A2B000000A5E5D9C3 00000000000000000000000000 I
0x00002B3AD9E11C60 SYSIBM   SYSTABLES          T    5       0         164        010000003A2B0000601CE1D9C3 0000050004000000611CE1D9C3 V

0x00002B3AD9E6D060 DBUSER1  EMP_RESUME         T    8       2         164        010000003A2B000060D0E6D9C3 00000500031B000060D0E6D9C3 V
0x00002B3AD9CB56A0 SYSTOOLS POLICY             T    4       5         164        010000003A2B0000A056CBD9C3 000005000D1D0000A056CBD9C3 V
0x00002B3AD9E66C60 DBUSER1  EMPLOYEE           T    6       2         164        010000003A2B0000606CE6D9C3 00000500001B0000606CE6D9C3 S
0x00002B3AD9CBE600 SYSCAT   TABLES             V    0       0         164        010000003A2B000000E6CBD9C3 000005000B11000000E6CBD9C3 V
0x00002B3AD9E642E0 DBUSER1  EMPPROJACT         T    11      2         164        010000003A2B0000E042E6D9C3 00000500071B0000E042E6D9C3 S
0x00002B3AD9DA26A0 DBUSER1  CUSTOMER           T    6       4         164        010000003A2B0000A026DAD9C3 00000500081D0000A026DAD9C3 S
0x00002B3AD9E996E0 DBUSER1  ACT                T    12      2         164        010000003A2B0000E096E9D9C3 000005000A1B0000E196E9D9C3 V


TABLESPACES:
Address            Name               TbspaceID LastRefID  CatalogCacheLoadingLock    CatalogCacheUsageLock      Sts
0x00002B3AD9BED6C0 SYSCATSPACE        0         164        110000003A2B0000C0D6BED9C3 0000210004000000C0D6BED9C3 V
0x00002B3AD9BE3080 TEMPSPACE1         1         31         110000003A2B00008030BED9C3 00002100050000008030BED9C3 V
0x00002B3AD9BF2F00 USERSPACE1         2         164        110000003A2B0000002FBFD9C3 0000210006000000002FBFD9C3 V
0x00002B3AD9E62EC0 IBMDB2SAMPLEXML    4         164        110000003A2B0000C02EE6D9C3 0000210008000000C02EE6D9C3 V
0x00002B3AD9BF2E00 SYSTOOLSPACE       5         164        110000003A2B0000002EBFD9C3 0000210009000000002EBFD9C3 V
-cleaner
The following is sample output of the -cleaner option:
db2pd -db sample -cleaner

Database Partition 0 - Database SAMPLE - Active - Up 0 days 00:06:34 - 
Date 08/09/2010 14:17:58

Page Cleaners:

Group ID    Clnr Idx   State Cycle      Task  Pgs Gthr   Pgs Ga'd   IO outstnd Max AIO    Pgs Thrsh  Pgs D Stl  Pgs Skppd
-1          0          Wait  0          None  0          0          0          32         0          0          0
-1          1          Wait  0          None  0          0          0          32         0          0          0
-1          2          Wait  0          None  0          0          0          32         0          0          0
-1          3          Wait  0          None  0          0          0          32         0          0          0
-1          4          Wait  0          None  0          0          0          32         0          0          0
...
Dirty lists for Bufferpool ID : 1
List ID    # Dirty    Clnr Idx   Trigger Target LSN       Pgs for Gap
0          4          0          None    0000000000000000 0
0          0          0          None    0000000000000000 0
1          8          1          None    0000000000000000 0
1          0          1          None    0000000000000000 0
2          2          2          None    0000000000000000 0
2          0          2          None    0000000000000000 0
3          1          3          None    0000000000000000 0
-dirtypages
The following is sample output of the -dirtypages option:
db2pd -db sample -dirtypages

Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:00:20 -- 
Date 08/09/2010 14:11:44


Bufferpool: 1
   Dirty pages %       : 34 / 1000 (3.40% dirty)
   Bufferpool minbuflsn: 000000000138800C

Oldest page info:
DirtyLst   TspID PPNum      ObjID OPNum      Typ UFlag fixcount   wgt CPC LSN              pgLtch
n/a        0     327        15    3          4   3     0          2   0   000000000138800C 0x07000000323508E8
   Dirty pages:
DirtyLst   TspID PPNum      ObjID OPNum      Typ UFlag fixcount   wgt CPC LSN              pgLtch
0          0     272        14    0          0   3     0          2   0   000000000138881C 0x070000003236C2E8 hX:0 sH:0 xW:0 rC:0
0          0     273        14    1          0   3     0          1   0   000000000138881C 0x070000003236B228 hX:0 sH:0 xW:0 rC:0
0          0     7541       18    9          1   3     0          2   0   000000000138E237 0x07000000323678A8 hX:0 sH:0 xW:0 rC:0
0          0     7540       18    8          1   3     0          2   0   000000000138E402 0x0700000032367A28 hX:0 sH:0 xW:0 rC:0
1          0     6945       15    5          1   3     0          2   0   0000000001388107 0x070000003236F3E8 hX:0 sH:0 xW:0 rC:0
1          0     300        14    4          1   3     0          2   0   000000000138889D 0x070000003236B6A8 hX:0 sH:0 xW:0 rC:0
...
Recovery information:
   lowtranlsn              : 000000000138E486
   minbuflsn               : 000000000138800C
   nextlsn                 : 000000000138E4B0
   LFH lowtranlsn          : 000000000138E486
   LFH minbuflsn           : 000000000138800C
   LFH nextlsn             : 000000000138800C
   Active Log bytes in use : 25764
   Current Softmax         : 4096000

DirtyLst - dirty list ID in this bufferpool
TspID - tablespace ID of this page
PPNum - pool page number
ObjID - object ID
OPNum - object page number
Typ - type of the object
UFlag - internal page flag
fixcount - number of active fixes on this page (in-use count)
wgt - weight of the page
CPC - clock
LSN - page LSN
pgLtch - page latch address, hX - held X, sH - # shard holders, xW - # of 
X waiters
Important: The softmax database configuration parameter is deprecated is deprecated in Version 10.5 and might be removed in a future release. For more information, see Some database configuration parameters are deprecated.
-edus
The following example is a sample of the output of the db2pd -edus command:
Database Partition 0 -- Active -- Up 0 days 01:14:05

List of all EDUs for database partition 0

db2sysc PID: 18485
db2wdog PID: 18483
db2acd  PID: 18504

EDU ID    TID            Kernel TID     EDU Name                               USR          SYS
====================================================================================================
24        47155322546496 12108          db2pfchr (TESTDB)                      0.010000     0.000000
23        47155326740800 12107          db2pclnr (TESTDB)                      0.000000     0.000000
22        47155330935104 12106          db2pclnr (TESTDB)                      0.000000     0.000000
21        47155335129408 12105          db2pclnr (TESTDB)                      0.000000     0.000000
20        47155339323712 12104          db2dlock (TESTDB)                      0.000000     0.000000
19        47155343518016 12103          db2lfr (TESTDB)                        0.000000     0.000000
18        47155347712320 12102          db2loggw (TESTDB)                      0.000000     0.000000
17        47155351906624 12101          db2loggr (TESTDB)                      0.080000     0.000000
16        47155356100928 27704          db2agent (TESTDB) (suspended)          0.930000     0.140000
15        47155360295232 18502          db2resync                              0.080000     0.000000
14        47155364489536 18500          db2ipccm                               0.030000     0.000000
13        47155368683840 18499          db2licc                                0.000000     0.000000
12        47155372878144 18498          db2thcln                               0.000000     0.000000
11        47155377072448 18497          db2alarm                               0.000000     0.000000
1         47155117025600 18493          db2sysc                                3.340000     0.070000
If you include an interval, such as db2pd -edus interval=10 then an additional two columns would be added to the right side of the output after the SYS column:
... USR DELTA        SYS DELTA
... ===============================
... 0.141799         0.045431
... 0.101154         0.045117
... 0.038113         0.020154
... 0.005668         0.007978
... 0.005139         0.004392
... 0.005003         0.004105
... 0.003913         0.004100
... 0.001785         0.001282
... 0.001083         0.001550
... 0.001005         0.000433
... 0.000181         0.000098
... 0.000095         0.000091
... 0.000000         0.000000
... 0.000000         0.000000
... 0.000000         0.000000
-encryptioninfo
The following example is a sample of the output of the db2pd -encryptioninfo command:
db2pd -db testdb -encryptioninfo

Database Member 0 -- Database TESTDB -- Active -- Up 0 days 00:00:16 -- Date 2014-10-03-13.14.20.282623

Encryption Info:
   Object Name:               TESTDB
   Object Type:               DATABASE
   Encyrption Key Info:
          Encryption Algorithm: AES
     Encryption Algorithm Mode: CBC
         Encryption Key Length: 256
              Master Key Label: DB2_SYSGEN_geoffrey_TESTDB_2014-10-01-10.05.01
 Master Key Rotation Timestamp: 2014-10-01-10.05.03.000000
   Master Key Rotation Appl ID: *LOCAL.geoffrey.141001140444
   Master Key Rotation Auth ID: GEOFFREY
     Previous Master Key Label: DB2_SYSGEN_geoffrey_TESTDB_2014-10-01-10.05.01
   KeyStore Info:
                 KeyStore Type: PKCS12
             KeyStore Location: /home/geoffrey/sqllib/keystore.p12
            KeyStore Host Name: hotel85.torolab.ibm.com
           KeyStore IP Address: 9.26.120.161
      KeyStore IP Address Type: IPV4
-extentmovement
The following example is a sample of the output of the db2pd -extentmovement command:
db2pd -extentmovement -db PDTEST

Database Member 0 -- Database PDTEST -- Active -- Up 0 days 00:04:33 -- Date 2012-10-26-11.19.52.056414

Extent Movement:
Address            TbspName Current Last Moved Left TotalTime            
0x00002AAB356D4BA0 DAVID    1168    1169 33    426  329636              
-fmpexechistory | -fmpe
The following example is a sample of the output of the db2pd -fmpexechistory command:
db2pd -fmpexechistory pid=761872 n=10   

Database Partition 0 -- Active -- Up 0 days 00:00:11

FMP Process:
FmpPid     Bit   Flags      ActiveThrd PooledThrd ForcedThrd Active 
761872     64    0x00000002 2          1          1          YES     

Active Threads:
EduPid: 123456	ThreadId: 987654
RoutineID  Timestamp
1          2009-05-06-17.12.30.000000    
2          2009-05-06-17.12.30.005000
1          2009-05-06-17.12.30.100000

EduPid: 234567	ThreadId: 987000
RoutineID  Timestamp
1          2009-05-06-17.12.31.000000
3          2009-05-06-17.12.30.000000

Pooled Threads:
ThreadId: 540021
RoutineID  Timestamp
4          2009-05-06-17.10.30.000000

Forced Threads:
ThreadId: 120021
RoutineID  Timestamp
10         2009-05-06-15.10.30.000000
The following example is a sample of the output of the db2pd -fmpexechistory command with genquery option:
db2pd -fmpExecHistory pid=761872 n=10 genquery

Database Partition 0 -- Active -- Up 0 days 00:00:11

WITH RTNHIST ( PID, TID, RTNID, RTNTIME) AS
             ( VALUES (761872, 987654, 1, TIMESTAMP('2009-07-13-16.17.10.818705')),
                      (761872, 987654, 2, TIMESTAMP('2009-07-13-16.17.11.818710')),... )
SELECT R.PID, R.TID, R.RTNTIME, ROUTINESCHEMA, ROUTINEMUDULENAME, ROUTINENAME, SPECIFICNAME, ROUTINEID 
	FROM syscat.routines, RTNHIST as R
	WHERE ROUTINEID = R.RTNID
	ORDER BY R.PID, R.TID, R.RTNTIME ; 
-iostat

The following example is a sample of the output of the db2pd -iostat command:

D:\>db2pd -iostat t l 2 5

Disk:       queue   %disk     tps   KBpsR   KBpsW     ms/R     ms/W  12:31:59
HarddiskVolume1      0  100.00    0.00       0       0     0.00     0.00
C:              0  100.00    0.00       0       0     0.00     0.00
D:              0  100.00    0.00       0       0     0.00     0.00
_Total          0  100.00    0.00       0       0     0.00     0.00

Disk:       queue   %disk     tps   KBpsR   KBpsW     ms/R     ms/W  12:32:01
HarddiskVolume1      0    0.00    0.00       0       0     0.00     0.00
C:              0    0.00    0.49       0      29     0.00     0.90
D:              0    0.00    0.00       0       0     0.00     0.00
_Total          0    0.00    0.49       0      29     0.00     0.90

Disk:       queue   %disk     tps   KBpsR   KBpsW     ms/R     ms/W  12:32:03
HarddiskVolume1      0    0.00    0.00       0       0     0.00     0.00
C:              0    0.00    1.47       0      10     0.00     0.50
D:              0    0.00    0.00       0       0     0.00     0.00
_Total          0    0.00    1.47       0      10     0.00     0.50

Disk:       queue   %disk     tps   KBpsR   KBpsW     ms/R     ms/W  12:32:05
HarddiskVolume1      0    0.00    0.00       0       0     0.00     0.00
C:              0    0.00    0.00       0       0     0.00     0.00
D:              0    0.00    0.00       0       0     0.00     0.00
_Total          0    0.00    0.00       0       0     0.00     0.00

Disk:       queue   %disk     tps   KBpsR   KBpsW     ms/R     ms/W  12:32:07
HarddiskVolume1      0    0.00    0.00       0       0     0.00     0.00
C:              0    0.00    0.00       0       0     0.00     0.00
D:              0    0.00    0.00       0       0     0.00     0.00
_Total          0    0.00    0.00       0       0     0.00     0.00
-logs
The following example is a sample of the output of the db2pd -logs command:
Logs:
Current Log Number            9
Pages Written                 2
Cur Commit Disk Log Reads     0
Cur Commit Total Log Reads    0
Method 1 Archive Status       n/a
Method 1 Next Log to Archive  9
Method 1 First Failure        n/a
Method 2 Archive Status       n/a
Method 2 Next Log to Archive  n/a
Method 2 First Failure        n/a
Log Chain ID                  0
Current LSO                   41372312
Current LSN                   0x0000000000092E88

Address            StartLSN          StartLSO           State       Size       Pages      Filename
0x00002AAF85D9A7D8 000000000008AE1D  41363249           0x00000000  4          4          S0000009.LOG
0x00002AAF85D9B038 0000000000000000  41379553           0x00000000  4          4          S0000010.LOG
0x00002AAF85D9B898 0000000000000000  41395857           0x00000000  4          4          S0000011.LOG
-pages
The following example is a sample of the output of the db2pd -pages command without specifying the summary parameter:
venus@baryon:/home/venus =>db2pd -pages -db pdtest

Database Partition 0 -- Database PDTEST -- Active -- Up 0 days 00:01:28

Bufferpool Pages:
First Active Pool ID      1
Max Bufferpool ID         1
Max Bufferpool ID on Disk 1
Num Bufferpools           5

Pages for all bufferpools:
Address            BPID TbspaceID TbspacePgNum ObjID ObjPgNum   ObjClass ObjType   Dirty Prefetched
0x0000002AC22ABAC0 1    0         92           10    0          EMP      Data      N     N
0x0000002AC22ABB80 1    0         2503         10    11         Perm     Index     N     N
0x0000002AC22ABC40 1    0         2501         10    9          Perm     Index     Y     N
0x0000002AC22ABD00 1    0         2494         10    2          Perm     Index     N     N
0x0000002AC22ABDC0 1    0         3437         5     17         Perm     Data      N     N
0x0000002AC22ABE80 1    0         2504         10    12         Perm     Index     Y     N
0x0000002AC22ABF40 1    0         2505         10    13         Perm     Index     N     N
0x0000002AC22AC000 1    0         2506         10    14         Perm     Index     N     N
0x0000002AC22AC0C0 1    0         28           5     0          EMP      LOB       N     N
0x0000002AC22AC180 1    0         2509         10    17         Perm     Index     N     N
0x0000002AC22AC240 1    0         2495         10    3          Perm     Index     Y     N
0x0000002AC22AC300 1    0         2498         10    6          Perm     Index     Y     N
0x0000002AC22AC3C0 1    2         128          4     0          Perm     Data      Y     N
0x0000002AC22AC480 1    0         2499         10    7          Perm     Index     N     N
0x0000002AC22AC540 1    0         99           10    3          Perm     Data      Y     N
0x0000002AC22AC600 1    0         96           10    0          Perm     Data      Y     N
0x0000002AC22AC6C0 1    0         110          5     2          Perm     Index     N     N
0x0000002AC22AC780 1    0         2500         10    8          Perm     Index     N     N
0x0000002AC22AC840 1    0         2740         5     16         Perm     Index     N     N
0x0000002AC22AC900 1    0         2507         10    15         Perm     Index     Y     N
Total number of pages: 20

Summary info for all bufferpools:
BPID TbspaceID  ObjID      Total      Dirty      Permanent  Temporary  Data       Index      LongField  XMLData    SMP        LOB        LOBA       BMP
1    0          5          4          0          3          0          1          2          0          0          0          1          0          0
1    0          10         15         7          14         0          3          12         0          0          0          0          0          0
1    2          4          1          1          1          0          1          0          0          0          0          0          0          0
Total number of pages: 20
The following example is a sample of the output of the db2pd -pages command specifying the summary parameter:
venus@baryon:/home/venus =>db2pd -pages summary -db pdtest

Database Partition 0 -- Database PDTEST -- Active -- Up 0 days 00:02:07

Bufferpool Pages:
First Active Pool ID      1
Max Bufferpool ID         1
Max Bufferpool ID on Disk 1
Num Bufferpools           5

Total number of pages: 20

Summary info for all bufferpools:
BPID TbspaceID  ObjID      Total      Dirty      Permanent  Temporary  Data       Index      LongField  XMLData    SMP        LOB        LOBA       BMP
1    0          5          4          0          3          0          1          2          0          0          0          1          0          0
1    0          10         15         7          14         0          3          12         0          0          0          0          0          0
1    2          4          1          1          1          0          1          0          0          0          0          0          0          0
Total number of pages: 20
-reorgs index
The following section is an example of output obtained using the -reorgs index parameter which reports the index reorg progress for a range-partitioned table with 2 partitions.
Note: The first output reports the Index Reorg Stats of the non-partitioned indexes. The following outputs report the Index Reorg Stats of the partitioned indexes on each partition; the index reorg statistics of only one partition is reported in each output.
Index Reorg Stats:
Retrieval Time: 02/08/2010 23:04:21
TbspaceID: -6       TableID: -32768
Schema: ZORAN    TableName: BIGRPT
Access: Allow none
Status: Completed
Start Time: 02/08/2010 23:03:55   End Time: 02/08/2010 23:04:04
Total Duration: 00:00:08
Prev Index Duration:  -
Cur Index Start: -
Cur Index: 0            Max Index: 2            Index ID: 0
Cur Phase: 0          ( -     )   Max Phase: 0
Cur Count: 0                      Max Count: 0
Total Row Count: 750000
Retrieval Time: 02/08/2010 23:04:21
TbspaceID: 2        TableID: 5
Schema: ZORAN    TableName: BIGRPT
PartitionID: 0      MaxPartition: 2
Access: Allow none
Status: Completed
Start Time: 02/08/2010 23:04:04   End Time: 02/08/2010 23:04:08
Total Duration: 00:00:04
Prev Index Duration:  -
Cur Index Start: -
Cur Index: 0            Max Index: 2            Index ID: 0
Cur Phase: 0          ( -     )   Max Phase: 0
Cur Count: 0                      Max Count: 0
Total Row Count: 375000
Retrieval Time: 02/08/2010 23:04:21
TbspaceID: 2        TableID: 6
Schema: ZORAN    TableName: BIGRPT
PartitionID: 1      MaxPartition: 2
Access: Allow none
Status: Completed
Start Time: 02/08/2010 23:04:08   End Time: 02/08/2010 23:04:12
Total Duration: 00:00:04
Prev Index Duration:  -
Cur Index Start: -
Cur Index: 0            Max Index: 2            Index ID: 0
Cur Phase: 0          ( -     )   Max Phase: 0
Cur Count: 0                      Max Count: 0
Total Row Count: 375000
-scansharing

The following section is an example of output using the -scansharing parameter. The output shows two sharing sets. The table scan set has two groups and the block index scan set has one group.



 Database Partition 0 -- Database SAMP -- Active -- Up 0 days 00:00:45

Scan Sets:
TbspaceID TableID ScanObject NumGroups Footprint             TableSize      FastScanRate SlowScanRate
2         3       0          2          11520                22752                2486        1000
    Group Information:
    FootPrint NumScannersInGroup
    8288                    3
         Scans In Group :
         AgentID ApplID              Mode Wrappable Fast/Slow Speed        ThrottleTime     Absolute Location       Remaining Pages
         9768    1173                0    0         1         2486                 0         32                           22751
         11332   1165                0    0         1         2486                 0         5056                         17727
         15466   1155                0    0         1         2486                 0         8288                         14495
    Group Information:
    FootPrint NumScannersInGroup
    3232                    2
         Scans In Group :
         AgentID ApplID              Mode Wrappable Fast/Slow Speed        ThrottleTime     Absolute Location       Remaining Pages
         15209   1150                0    0         1         2486                 0         14080                        8703
         12103   1148                0    0         1         2486                 0         17280                        5503
Scan Sets:
TbspaceID TableID ScanObject NumGroups Footprint             TableSize      FastScanRate SlowScanRate
2         3       1          1          9056                 22752                1000        1000
    Group Information:
    FootPrint NumScannersInGroup
    9056                    3
         Scans In Group :
         AgentID ApplID              Mode Wrappable Fast/Slow Speed        ThrottleTime     Relative Location       Estimated Remaining Pages
         6170    1209                0    0         1         1000                 0         896                          13535
         13645   1215                0    0         1         1000                 0         3552                         10879
         4371    1204                0    0         1         1000                 0         9920                         4511                 
-serverlist

The following are samples of the serverlist output

Sample serverlist output from db2pd -serverlist -db sample

Database Member 0 -- Active -- Up 0 days 00:10:43 -- Date 10/06/2010 12:22:39

Server List:
   Time:          Wed Oct  6 12:13:17
   Database Name: SAMPLE
   Count:         2

   Hostname                                 Non-SSL Port   SSL Port   Priority
   coralxib23.torolab.ibm.com               49712          0          34
   coralxib24.torolab.ibm.com               49712          0          65

Sample service subclass output from db2pd -serverlist -alldbs

Database Member 0 -- Active -- Up 0 days 00:06:15 -- Date 10/06/2010 12:18:11

Server List:
   Time:          Wed Oct  6 12:13:17
   Database Name: SAMPLE
   Count:         2

   Hostname                                 Non-SSL Port   SSL Port   Priority
   coralxib23.torolab.ibm.com               49712          0          34
   coralxib24.torolab.ibm.com               49712          0          65

Database Member 0 -- Active -- Up 0 days 00:06:15 -- Date 10/06/2010 12:18:11

Server List:
   Time:          Wed Oct  6 12:17:00
   Database Name: SAMPLE2
   Count:         2

   Hostname                                 Non-SSL Port   SSL Port   Priority
   coralxib23.torolab.ibm.com               49712          0          56
   coralxib24.torolab.ibm.com               49712          0          43
-serviceclasses

The following example is a sample of the service classes information output for one service superclass and its subclass.

Sample service superclass output:

Service Class Name        = SYSDEFAULTSYSTEMCLASS
Service Class ID          = 1
Service Class Type        = Service Superclass
Default Subclass ID       = 11
Effective Service Class State     = Enabled
Catalog Service Class State       = Enabled
Effective Prefetch Priority       = Medium
Catalog Prefetch Priority         = Default
Effective Bufferpool Priority     = Low
Catalog Bufferpool Priority       = Default
Effective Outbound Correlator     = None
Catalog Outbound Correlator       = None
CPU Shares                        = 1000
CPU Share Type                    = Soft
CPU Limit                         = None
Work Action Set ID        = N/A
Collect Activity Opt      = None
Collect Request Metrics   = Base

Num Connections               = 5
Last Statistics Reset Time     = 12/16/2008 15:27:42.000000
Num Coordinator Connections   = 5
Coordinator Connections HWM   = 5

Associated Workload Occurrences (WLO):
AppHandl [nod-index]  WL ID        WLO ID       UOW ID  WLO State
10       [000-00010]  0            0            1       UOWWAIT
11       [000-00011]  0            0            1       UOWWAIT
12       [000-00012]  0            0            1       UOWWAIT
13       [000-00013]  0            0            1       UOWWAIT
14       [000-00014]  0            0            1       UOWWAIT      

Sample service subclass output:

Service Class Name        = SYSDEFAULTSUBCLASS
Service Class ID          = 11
Service Class Type        = Service Subclass
Parent Superclass ID      = 1
Effective Service Class State     = Enabled
Catalog Service Class State       = Enabled
Effective Prefetch Priority       = Medium
Catalog Prefetch Priority         = Default
Effective Bufferpool Priority     = Low
Catalog Bufferpool Priority       = Default
Effective Outbound Correlator     = None
Catalog Outbound Correlator       = None
Collect Activity Opt      = None
Collect Request Metrics   = None
Collect Aggr Activity Opt = None
Collect Aggr Request Opt  = None
Act Lifetime Histogram Template ID          = 1
Act Queue Time Histogram Template ID        = 1
Act Execute Time Histogram Template ID      = 1
Act Estimated Cost Histogram Template ID    = 1
Act Interarrival Time Histogram Template ID = 1
Request Execute Time Histogram Template ID  = 1

Access Count              = 0
Last Stats Reset Time     = 12/16/2008 15:27:42.000000
Activities HWM            = 0
Activities Completed      = 0
Activities Rejected       = 0
Activities Aborted        = 0

Associated Agents:
EDU ID       AppHandl [nod-index]  WL ID        WLO ID       UOW ID      Activity ID
26           10       [000-00010]  0            0            0           0
29           11       [000-00011]  0            0            0           0
28           12       [000-00012]  0            0            0           0
27           13       [000-00013]  0            0            0           0
30           14       [000-00014]  0            0            0           0

Associated Non-agent threads:
PID          TID                   Thread Name
6834         2948590480            db2loggr
6834         2947541904            db2loggw
6834         2946493328            db2lfr
6834         2945444752            db2dlock
6834         2944396176            db2pclnr
6834         2943347600            db2pfchr
6834         2942299024            db2pfchr
6834         2941250448            db2pfchr      
-storagegroups and -storagepaths
The following section is an example of output using the -storagegroups parameter or the -storagepaths parameter.
  db2pd -db testdb -storagegroups

  Storage Group Configuration:
  Address            SGID  Deflt DataTag  Name
  0x00002BA9E6CFF4C0 0     Yes   0        IBMSTOGROUP
  0x00002BA9E6D0F4C0 1     No    1        SG_SSD
  0x00002BA9E6D1DAE0 2     No    5        SG_IBMSAN

  Storage Group Statistics:
  Address            SGID State        NumPaths  NumDropPen
  0x00002BA9E6CFF4C0 0    0x00000000   1         0
  0x00002BA9E6D0F4C0 1    0x00000000   2         0
  0x00002BA9E6D1DAE0 2    0x00000000   2         0

  Storage Group paths:
  Address            SGID PathID  PathState  PathName
  0x00002BA99CD23540 0    0       InUse      /filesystem1
  0x00002BA99CE13540 1    1024    InUse      /filesystem2
  0x00002BA99CF03540 1    1025    InUse      /filesystem3
  0x00002BA99D0F3540 2    2048    InUse      /filesystem4
  0x00002BA99D1E3540 2    2049    InUse      /filesystem5
-tablespaces
The following example is a sample of the output of the db2pd -tablespaces command showing information such as PathsDropped and PathID that is applicable to databases (some of the columns have been left out for readability):
Tablespace Configuration:
  ...

  Tablespace Statistics:
  Address            Id    ...  State      MinRecTime NQuiescers PathsDropped
  0x070000004108AB40 0     ...  0x00000000 0          0          Yes
  0x070000004108B520 1     ...  0x00000000 0          0          Yes
  0x0700000041078100 2     ...  0x00000000 0          0          Yes

  Tablespace Autoresize Statistics:
  ...

  Tablespace Storage Statistics:
  Address            Id  DataTag  Rebalance  SGID  SourceSGID
  0x00002BA9E6CFF4C0 0   0        No         0     -
  0x00002BA9E6D0F4C0 1   5        No         0     -
  0x00002BA9E6D1DAE0 2   1        Yes        1     0
  0x00002BA9E73696C0 3   5        No         0     -
  
  Containers:
  Address            TspId ... PathID StripeSet  Container
  0x070000004108B240 0     ... 0      0          /dataPath1/inst/NODE0000/TESTDB/T0000000/C0000000.CAT
  0x070000004108B398 0     ... 1      0          /dataPath2/inst/NODE0000/TESTDB/T0000000/C0000001.CAT
  0x070000004108BBC0 1     ... 0      0          /dataPath1/inst/NODE0000/TESTDB/T0000001/C0000000.TMP
  0x070000004108BD18 1     ... 1      0          /dataPath2/inst/NODE0000/TESTDB/T0000001/C0000001.TMP
  0x07000000410787A0 2     ... 0      0          /dataPath1/inst/NODE0000/TESTDB/T0000002/C0000000.LRG
  0x07000000410788F8 2     ... 1      0          /dataPath2/inst/NODE0000/TESTDB/T0000002/C0000001.LRG

If the table is managed by manual storage, the SGID will output a dash (-).

A new ‘Max HWM' column is added to the db2pd -tablespaces output to indicate the maximum HWM for a DMS table space since the instance was started. The ‘HWM' column in the output is the current HWM, which for a temporary DMS table space, represents the point-in-time value of the amount of disk space used. For SMS table spaces, the HWM and Max HWM will not have any value.

After a query has been issued, in-memory information about the temporary tables used in the last transaction will be available using db2pd. The following example shows the new column in bold. The value of the Max HWM will always be equal to, or greater than, the HWM.
hotel26:/home/billyp>  db2pd -db bill -tablespaces

Database Partition 0 -- Database BILL -- Active -- Up 0 days 00:02:15

Tablespace Configuration:
Address            Id    Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC RSE NumCntrs MaxStripe  LastConsecPg Name
0x00002B9DCA582720 0     DMS  Regular 4096   4        Yes  4        1     1         Off Yes 1        0          3            SYSCATSPACE
0x00002B9DCA583560 1     DMS  UsrTmp  4096   2        Yes  2        1     1         Off Yes 1        0          1            DMSUSRTEMP
0x00002B9DCA5863E0 2     DMS  Large   4096   32       Yes  32       1     1         Off Yes 1        0          31           USERSPACE1
0x00002B9DCA587220 3     DMS  SysTmp  4096   2        Yes  2        1     1         Off N/A 1        0          1            DMSSYSTEMP
0x00002B9DCA58A0A0 4     DMS  Large   4096   4        Yes  4        1     1         Off No  1        0          3            SYSTOOLSPACE

Tablespace Statistics:
Address            Id    TotalPgs   UsablePgs  UsedPgs    PndFreePgs FreePgs    HWM    Max HWM     State      MinRecTime NQuiescers
0x00002B9DCA582720 0     12544      12540      12308      0          232        12308  12308       0x00000000 0          0
0x00002B9DCA583560 1     20000      19998      3266       0          16732      3266   3266        0x00000000 0          0
0x00002B9DCA5863E0 2     7168       7136       3232       0          3904       7072   7072        0x00000000 0          0
0x00002B9DCA587220 3     20000      19998      1700       0          18298      1700   2000        0x00000000 0          0
0x00002B9DCA58A0A0 4     256        252        144        0          108        144    200         0x00000000 0          0          
-temptable
The system monitor elements could also be used to determine the effectiveness of temporary table compression by examining the amount of buffer pool reads and writes. The following example is a sample of the output of the db2pd -temptable command:
hotel26:/home/billyp> db2pd -db billdb -temptable
System Temp Table Stats:
        Number of Temp Tables   : 0
                Comp Eligible Temps     : 0
                Compressed Temps        : 0

                Total Stored Temp Bytes : 0
                Total Bytes Saved       : 0
                Total Compressed Rows   : 0
                Total Temp Table Rows   : 0

User Temp Table Stats:
        Number of Temp Tables   : 0
                Comp Eligible Temps     : 0
                Compressed Temps        : 0

                Total Stored Temp Bytes : 0
                Total Bytes Saved       : 0
                Total Compressed Rows   : 0
                Total Temp Table Rows   : 0

The same information is stored for system temporary tables as well as user temporary tables. However, all of the counters mentioned previously are cumulative, and are updated as temporary tables are dropped. As such, these counters represent only historical information.

-thresholds

The following example is a sample of the threshold information for a database threshold and its queue.

Threshold Name              = MAXDBACTIVITIES
Threshold ID                = 6
Domain                      = 10
Domain ID                   = 10
Predicate ID                = 90
Maximum Value               = 2
Enforcement                 = D
Queueing                    = Y
Queue Size                  = 0
Collect Flags               = V
Partition Flags             = C
Execute Flags               = C
Enabled                     = Y
Check Interval (seconds)    = -1
Remap Target Serv. Subclass = 0
Log Violation Evmon Record  = Y

Sample database threshold queue output:

Database Threshold Tickets:

Ticket information for threshold: TH1 with threshold ID 1
Activity ID  UOW ID      Classification  AppHandl [nod-index]
1            6           READ_DML        51       [000-00051]

Queue information for threshold: MAXDBACTIVITIES
Max Concurrency             = 2
Concurrency               = 2
Max Queue Size              = 0

Agents Currently Queued:
EDU ID       AppHandl [nod-index]         Agent Type  Activity ID  UOW ID
36           14994    [000-14994]  1            4            1            

The following example is a sample of the threshold information for a statement threshold:

db2pd -thresholds -db sample  db2

Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:01:28 -- Date 04/13/2011 09:57:09

Statement Thresholds:

Threshold Name              = T_THRESHOLD_FIRING_23
Threshold ID                = 1 
Domain                      = 60
Domain ID                   = 1
Predicate ID                = 30    
Maximum Value               = 60
Enforcement                 = D 
Queueing                    = N 
Queue Size                  = 0
Collect Flags               = V 
Partition Flags             = C
Execute Flags               = C 
Enabled                     = Y 
Check Interval (seconds)    = -1
Remap Target Serv. Subclass = 0
Log Violation Evmon Record  = Y
Statement Text              = CREATE TABLE T2 (X INT)

$ db2 "select thresholdname, domain from sysibm.systhresholds"

THRESHOLDNAME                                                                                              DOMAIN
-----------------------------------------------------------------------------------------------------------------
T_THRESHOLD_FIRING_23                                                                                        SQ

  1 record(s) selected.
-transactions
The following is a sample of the output of the db2pd -transactions command:
db2pd -transactions -db sample

Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 01:27:38 -- Date 2012-12-04-14.23.08.373888

Transactions:
Address            AppHandl [nod-index] TranHdl    Locks      State ...
0x00002AAAE633A480 7        [000-00007] 3          0          READ  ...
0x00002AAAE633C080 8        [000-00008] 4          0          READ  ...
0x00002AAAE633DC80 9        [000-00009] 5          0          READ  ...
0x00002AAAE633F880 10       [000-00010] 6          0          READ  ...
0x00002AAAE6341480 11       [000-00011] 7          0          READ  ...
0x00002AAAE6343080 12       [000-00012] 8          0          READ  ...
0x00002AAAE6344C80 13       [000-00013] 9          0          READ  ...
0x00002AAAE6346880 14       [000-00014] 10         0          READ  ...
0x00002AAAE6348480 15       [000-00015] 11         0          READ  ...
0x00002AAAE634A080 16       [000-00016] 12         0          READ  ...
0x00002AAAE634BC80 17       [000-00017] 13         0          READ  ...
Output from Transactions continued:

... Tflag      Tflag2     Firstlsn           Lastlsn            ...
... 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 ...
... 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 ...
... 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 ...
... 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 ...
... 0x00000000 0x00000020 0x0000000000000000 0x000000000003EB46 ...
... 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 ...
... 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 ...
... 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 ...
... 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 ...
... 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 ...
... 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 ...
Output from Transactions continued:

... Firstlso             Lastlso  SpaceReserved   LogSpace        TID            ...
... 0                    0        0               0               0x00000000031A ...
... 0                    0        0               0               0x000000000103 ...
... 0                    0        0               0               0x000000000318 ...
... 0                    0        0               0               0x000000000105 ...
... 0                    0        0               0               0x000000000221 ...
... 0                    0        0               0               0x000000000107 ...
... 0                    0        0               0               0x000000000108 ...
... 0                    0        0               0               0x000000000109 ...
... 0                    0        0               0               0x00000000010B ...
... 0                    0        0               0               0x00000000010C ...
... 0                    0        0               0               0x00000000010E ...
Output from Transactions continued:

... AxRegCnt  GXID   ClientUserID  ClientWrkstnName  ClientApplName             ClientAccntng
... 1         0      n/a           n/a               n/a                        n/a          
... 1         0      n/a           n/a               n/a                        n/a          
... 1         0      n/a           n/a               n/a                        n/a          
... 1         0      n/a           n/a               n/a                        n/a          
... 1         0      n/a           n/a               n/a                        n/a          
... 1         0      n/a           n/a               n/a                        n/a          
... 1         0      n/a           n/a               n/a                        n/a          
... 1         0      n/a           n/a               n/a                        n/a          
... 1         0      n/a           n/a               n/a                        n/a          
... 1         0      n/a           n/a               n/a                        n/a          
... 1         0      n/a           n/a               db2evml_DB2DETAILDEADLOCK  n/a          

Total Application commits   : 123
Total Application rollbacks : 139
-sort
The following example is a sample of the output of the db2pd -sort command:
db2pd -sort -db pdtest

Database Partition 0 -- Database PDTEST -- Active -- Up 0 days 00:05:29

AppHandl [nod-index]
13       [000-00013]
   SortCB             MaxRowSize           EstNumRows EstAvgRowSize NumSMPSorts NumSpills
   0x0000002AB7587300 919                  50716      644           1           1
   KeySpec
   VARCHAR:300,VARCHAR:400

      SMPSort# SortheapMem          NumBufferedRows NumSpilledRows
      0        16                   0               101

AppHandl [nod-index]
7        [000-00007]
   SortCB             MaxRowSize           EstNumRows EstAvgRowSize NumSMPSorts NumSpills
   0x0000002AB74FC540 919                  1000       644           1           1
   KeySpec
   VARCHAR:400,VARCHAR:200,VARCHAR:300

      SMPSort# SortheapMem          NumBufferedRows NumSpilledRows
      0        16                   0               101
-wlocks
The following example is a sample of the output of the db2pd -wlocks command:
db2pd -wlocks -db mydb2

Database Partition 0 -- Database MYDB2 -- Active -- Up 0 days 00:02:17

Locks being waited on :
AppHandl [nod-index] TranHdl   Lockname                   Type   Mode Conv Sts CoorEDU    AppName  AuthID   AppID
13       [000-00013] 7         0002000B000000000340000452 Row    ..X       G   352614     db2bp    VENUS    *LOCAL.venus.071117030309
15       [000-00015] 9         0002000B000000000340000452 Row    .NS       W   1176046    db2bp    VENUS    *LOCAL.venus.071117030358
12       [000-00012] 2         0002000B000000000340000452 Row    .NS       W   1052748    db2bp    VENUS    *LOCAL.venus.071117030231

12       [000-00012] 2         00020004000000000080001652 Row    ..X       G   1052748    db2bp    VENUS    *LOCAL.venus.071117030231
14       [000-00014] 8         00020004000000000080001652 Row    .NS       W   634900     db2bp    VENUS    *LOCAL.venus.071117030340
-workclasssets

The following example is a sample of the output for the basic work class information:

Work Class Sets:  
 Address            ClassSetID  ReferenceCounter
 0x00002BA89DDF5AE0 1           1  
 
 Work Classes:
 Address            ClassSetID  ClassID     ClassName  
 0x00002BA89DDF5BC0 1           1           WCDML      
     Attributes:
         Work Type: DML
         Timeron Cost From: 1  Timeron Cost To: 1000
  
 Address            ClassSetID  ClassID     ClassName  
 0x00002BA89DDF5C40 1           2           WCDDL   
         Work Type: DML
-workloads

The following example is a sample of the output for the default workloads SYSDEFAULTUSERWORKLOAD and SYSDEFAULTADMWORKLOAD:

Database Partition 0 -- Database SB -- Active -- Up 0 days 00:00:57

Workload Definitions:
Address                     = 0x00002B3E772ACB40
WorkloadID                  = 1
WorkloadName                = SYSDEFAULTUSERWORKLOAD
DBAccess                    = ALLOW
Maximum Degree              = 4
ConcWLOThresID              = 0
ConcWLOThresName            = ^H
MaxConcWLOs                 = 9223372036854775806
WLOActsThresName            = ^H
WLOActsThresID              = 0
MaxWLOActs                  = 9223372036854775806
ServiceClassID              = 13
Collect Activity Opt        = None
Collect Lock Timeout        = Without History
Collect Deadlock            = Without History
Collect Lock Wait           = None
Collect Aggr Activity Opt   = None
Collect Activity Metrics    = Base
Collect Unit of Work Data   = None
Act Lifetime Histogram Template ID          = 1
Act Queue Time Histogram Template ID        = 1
Act Execute Time Histogram Template ID      = 1
Act Estimated Cost Histogram Template ID    = 1
Act Interarrival Time Histogram Template ID = 1

Address                     = 0x00002B3E772ACD50
WorkloadID                  = 2
WorkloadName                = SYSDEFAULTADMWORKLOAD
DBAccess                    = ALLOW
Maximum Degree              = DEFAULT
ConcWLOThresID              = 0
ConcWLOThresName            = ^H
MaxConcWLOs                 = 9223372036854775806
WLOActsThresName            = ^H
WLOActsThresID              = 0
MaxWLOActs                  = 9223372036854775806
ServiceClassID              = 13
Collect Activity Opt        = None
Collect Lock Timeout        = Without History
Collect Deadlock            = Without History
Collect Lock Wait           = None
Collect Aggr Activity Opt   = None
Collect Activity Metrics    = Base
Collect Unit of Work Data   = None
Act Lifetime Histogram Template ID          = 1
Act Queue Time Histogram Template ID        = 1
Act Execute Time Histogram Template ID      = 1
Act Estimated Cost Histogram Template ID    = 1
Act Interarrival Time Histogram Template ID = 1


Usage Privilege Holders:
Address            WorkloadID  Type       AuthID
0x00002B3E772BCD60 1           GROUP      PUBLIC

Local Partition Workload Statistics:
Address                     = 0x00002B3E772DA0C0
WorkloadID                  = 1
WorkloadName                = SYSDEFAULTUSERWORKLOAD
NumWLO                      = 0
LastResetTime               = 10/07/2008 16:34:43.000000
WLO HWM                     = 0
WLOActHWM                   = 0
WLOCompleted                = 0
ActCompleted                = 0
ActAborted                  = 0
ActRejected                 = 0

Address                     = 0x00002B3E7730A0C0
WorkloadID                  = 2
WorkloadName                = SYSDEFAULTADMWORKLOAD
NumWLO                      = 0
LastResetTime               = 10/07/2008 16:34:43.000000
WLO HWM                     = 0
WLOActHWM                   = 0
WLOCompleted                = 0
ActCompleted                = 0
ActAborted                  = 0
ActRejected                 = 0
-rustatus
The following example is a sample of the output of the db2pd -rustatus command:
ROLLING UPDATE STATUS:  Disk Value                                         Memory Value

   Record Type        = INSTANCE
   ID                 = 0
   Code Level         = V:10 R:5 M:0 F:3 I:0 SB:0 (0x0A05000300000000)     Not Applicable
   Architecture Level = V:10 R:5 M:0 F:3 I:0 SB:0 (0x0A05000300000000)     Not Applicable
   State              = [NONE]
   Last updated       = 2013/04/18:02:58:58


   Record Type        = MEMBER
   ID                 = 0
   Code Level         = V:10 R:5 M:0 F:4 I:0 SB:0 (0x0A05000400000000)     V:10 R:5 M:0 F:4 I:0 SB:0 (0x0A05000400000000)
   CECL               = V:10 R:5 M:0 F:3 I:0 SB:0 (0x0A05000300000000)     V:10 R:5 M:0 F:3 I:0 SB:0 (0x0A05000300000000)
   Architecture Level = V:10 R:5 M:0 F:4 I:0 SB:0 (0x0A05000400000000)     V:10 R:5 M:0 F:4 I:0 SB:0 (0x0A05000400000000)
   CEAL               = V:10 R:5 M:0 F:3 I:0 SB:0 (0x0A05000300000000)     V:10 R:5 M:0 F:3 I:0 SB:0 (0x0A05000300000000)
   Section Level      = V:10 R:5 M:0 F:0 I:0 SB:0 (0x0A05000000000000)     V:10 R:5 M:0 F:0 I:0 SB:0 (0x0A05000000000000)
   Last updated       = 2013/04/18:07:59:48

mbserver53.domain.com: db2pd -ruStatus -localhost ... completed ok

   Record Type        = MEMBER
   ID                 = 1
   Code Level         = V:10 R:5 M:0 F:4 I:0 SB:0 (0x0A05000400000000)     V:10 R:5 M:0 F:4 I:0 SB:0 (0x0A05000400000000)
   CECL               = V:10 R:5 M:0 F:3 I:0 SB:0 (0x0A05000300000000)     V:10 R:5 M:0 F:3 I:0 SB:0 (0x0A05000300000000)
   Architecture Level = V:10 R:5 M:0 F:4 I:0 SB:0 (0x0A05000400000000)     V:10 R:5 M:0 F:4 I:0 SB:0 (0x0A05000400000000)
   CEAL               = V:10 R:5 M:0 F:3 I:0 SB:0 (0x0A05000300000000)     V:10 R:5 M:0 F:3 I:0 SB:0 (0x0A05000300000000)
   Section Level      = V:10 R:5 M:0 F:0 I:0 SB:0 (0x0A05000000000000)     V:10 R:5 M:0 F:0 I:0 SB:0 (0x0A05000000000000)
   Last updated       = 2013/04/18:09:24:18

mbserver55.domain.com: db2pd -ruStatus -localhost ... completed ok

   Record Type        = CF
   ID                 = 128
   Code Level         = V:10 R:5 M:0 F:4 I:0 SB:0 (0x0A05000400000000)     Not Applicable
   Architecture Level = V:10 R:5 M:0 F:4 I:0 SB:0 (0x0A05000400000000)     Not Applicable
   Last updated       = 2013/04/18:07:31:14

   Record Type        = CF
   ID                 = 129
   Code Level         = V:10 R:5 M:0 F:4 I:0 SB:0 (0x0A05000400000000)     Not Applicable
   Architecture Level = V:10 R:5 M:0 F:4 I:0 SB:0 (0x0A05000400000000)     Not Applicable
   Last updated       = 2013/04/18:07:25:55
-vmstat
The following example is a sample of the output of the db2pd -vmstat command:
D:\>db2pd -vmstat t 2 5

run            memory                system             CPU
--- --------------------------- ----------------- ---------------
  r     used     free   pi   po int/s  cs/s  sc/s usr sys idl int
  0  3377048  5752420    0    0  3899 15597 136475   0   0 100   0   11:43:49
  0  3508340  5622388    0    0  6232  2750  3964   0  11  89   0   11:43:51
  0  3622804  5507628    0    0  5589  1381  2332   0   7  93   0   11:43:53
  0  3705532  5425536    0    0  4052  1053  2908   0   5  95   0   11:43:55
  0  3836708  5294592    0    0  6338  1163  1289   0   9  91   0   11:43:57