原文：https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_9.7.0/com.ibm.db2.luw.admin.trb.doc/doc/c0054595.html
https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_9.8.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0011729.html#r0011729__pdlatch----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:
- SYSADM
- SYSCTRL
- SYSMAINT
- SYSMON
When the SYSMON authority level is granted, the following options are not available:
- dump
- memblocks
- stack
NoteOn 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
>>-db2pd--+------------------------------------------+---------->
'- -activestatements--+------------------+-'
+-apphdl=appHandle-+
'-file=filename----'
>--+----------------------------------------+------------------->
'- -addnode--+-------------+--+--------+-'
'-oldviewapps-' '-detail-'
>--+----------------+------------------------------------------->
+- -alldatabases-+
'- -alldbs-------'
>--+-------------------------------------------------+---------->
+- -alldbpartitionnums----------------------------+
| .-,-------------------------. |
| V | |
+- -dbpartitionnum----database_partition_number-+-+
'- -global----------------------------------------'
>--+----------------------------------------------------------------+-->
'- -agents--+-------------+--+--------------+--+---------------+-'
'-db=database-' +-AgentId------+ '-file=filename-'
'-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-'
>--+------------------------------+----------------------------->
'- -command--+---------------+-'
'-file=filename-'
>--+----------------------------------+------------------------->
| .-,------------. |
| V | |
'-+- -database-+----databasename-+-'
+- -db-------+
'- -d--------'
>--+----------------------------+------------------------------->
'- -dbcfg--+---------------+-'
'-file=filename-'
>--+-----------------------------+--+------------+-------------->
'- -dbmcfg--+---------------+-' '- -dbptnmem-'
'-file=filename-'
>--+---------------------------------------+-------------------->
'- -dump--+---------------------------+-'
+-all--+------------------+-+
| '-apphdl=AppHandle-' |
+-apphdl=AppHandle----------+
+-eduid-apphdl=AppHandle----+
'-pid-----------------------'
>--+------------------------------+----------------------------->
'- -dynamic--+---------------+-'
+-anch=anchID---+
'-file=filename-'
>--+-------------------------------+--+--------------+---------->
'- -edus--+-------------------+-' '- -everything-'
'-interval=interval-'
>--+------------------------------------------------+----------->
'- -fcm--+-----+--+---------+--+---------------+-'
'-hwm-' '-numApps-' '-file=filename-'
>--+------------------+--+-------+--+--------+------------------>
'- -file--filename-' '- -fmp-' '- -full-'
>--+-------------------------------------+---------------------->
'- -fixstack--existing_stack_filename-'
>--+----------------------------------------------------------------+-->
'-+- -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-' '- -interactive-'
>--+-----------------------------------------+------------------>
'- -latches--+-------+--+---------------+-'
'-group-' '-file=filename-'
>--+------------------------------------------------------------------+-->
'- -locks--+---------+--+---------------+--+-----------+--+------+-'
'-TranHdl-' '-file=filename-' '-showlocks-' '-wait-'
>--+---------------------------+-------------------------------->
'- -logs--+---------------+-'
'-file=filename-'
.-,-----------------.
V |
>----+---------------+-+--+--------------+---------------------->
'- -member--num-' '- -allmembers-'
>--+-----------------------------------------------------------------------------------------+-->
| .-,--------------. |
| V | |
'- -memblocks----+-all--------+-+--+-----+--+--------+--+------+--+--------+--+---------+-'
+-dbms-------+ '-top-' '-blocks-' '-sort-' '-PoolID-' '-pid=pid-'
+-fcm--------+
+-fmp--------+
+-appctl--id-+
'-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-'
>--+---------------------------------------------------------------------------+-->
'--- -scansharing----+----------------------------------------------------+-'
'-obj=objectID--pool=poolID--+---------------------+-'
+-all-----------------+
'-index=--+-indexID-+-'
'-all-----'
>--+---------------------------------+-------------------------->
'- -serverlist--+---------------+-'
'-file=filename-'
>--+--------------------------------------+--------------------->
'- -serviceclasses--+----------------+-'
'-serviceclassID-'
>--+------------------------------+----------------------------->
'- -sort--+------------------+-'
'-apphdl=AppHandle-'
>--+-------------------------------+---------------------------->
'- -stack--+-all--------------+-'
+-apphdl=AppHandle-+
'-eduid------------'
>--+-----------------------------+------------------------------>
'- -static--+---------------+-'
'-file=filename-'
>--+-----------------------------------------------------------------------------------------------------+-->
'- -statisticscache--+--------------------+--+---------------+--+-----------------------------------+-'
+- -db--databasename-+ '-file=filename-' +-summary---------------------------+
'- -alldbs-----------' +-detail----------------------------+
'-find schema=schema--object=object-'
>--+----------------+------------------------------------------->
'- -storagepaths-'
>--+-----------------------------------------------+------------>
'- -sysplex--+-------------+--+---------------+-'
'-db=database-' '-file=filename-'
>--+----------------------------------------------------------------+-->
'- -tablespaces--+---------------+--+-------+--+---------------+-'
'-Tablespace ID-' '-group-' '-file=filename-'
>--+------------------------------------------------------------------------------------------------------------+-->
'- -tcbstats--+-----------+--+-------+--+-----------------------------------------------+--+---------------+-'
'-nocatalog-' +-all---+ '-TbspaceID=tablespace_ID--+------------------+-' '-file=filename-'
'-index-' '-TableID=table_ID-'
>--+------------------------+----------------------------------->
'- -temptable--+-------+-'
'-reset-'
>--+-------------------------------+--+------------+------------>
'- -thresholds--+-------------+-' '- -totalmem-'
'-thresholdID-'
>--+-----------------------------------------------------+------>
'- -transactions--+--------------+--+---------------+-'
+-TranHdl------+ '-file=filename-'
'-app=AppHandl-'
>--+--------------------------------+--+-----------+------------>
'- -utilities--+---------------+-' '- -version-'
'-file=filename-'
>--+-----------------------------+------------------------------>
'- -wlocks--+---------------+-'
'-file=filename-'
>--+--------------------------------------------------+--------->
'- -workactionsets--+-----------------+--+-------+-'
'-workactionsetID-' '-group-'
>--+------------------------------------------------+----------->
'- -workclasssets--+----------------+--+-------+-'
'-workclasssetID-' '-group-'
>--+----------------------------------------+------------------><
'- -workloads--+------------+--+-------+-'
'-workloadID-' '-group-'
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 on the add database partition server operation. This parameter only returns information when issued on the database partition server that is being added. The progress information is persistent on the new database partition server until it is restarted. If issued on an existing database partition server, this parameter returns no information.
See Sample output of the db2pd -addnode command.
- -alldatabases | -alldbs
- Specifies that the command attaches to all memory sets of all the databases.
- -alldbpartitionnums
- Specifies that this command is to run on all active database partition servers on the local host. This parameter reports only information from database partition servers on the same physical machine that db2pd is being run on.
- -allmembers
- Specifies that this command is to run on all active members for a DB2 pureScale® environment. db2pd will only report information from database members on the same physical machine that db2pd is being run on.
- -agents
- Returns information about agents.
If an agent ID is specified, information is returned about the agent. If an application ID is specified, information is returned about all the agents that are performing work for the application. Specify this command parameter with the -inst parameter, if you have chosen a database that you want scope output for. 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.
NoteTo 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 tableThe 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. For example,
db2pd -catalogcache -db sample
See Sample output of the db2pd -catalogcache command.
Definitions for the returned information can be found here: -catalogcache. The output for SYSTABLES can have multiple entries for the same table (see DEPT in the above output). 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-cainfo 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.
- -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.
- -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 appHandleapplication.
- 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.
- -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.
See the -edus usage notes. See also Sample output of the db2pd -edus command.
- -everything
- Runs all options for all databases on all database partition servers that are local to the server.
- -fcm
- Returns information about the fast communication manager.
- Specify this parameter with the -inst parameter, if you have chosen a database for which you want scope output.
- Specify this parameter with the hwm parameter, to retrieve high-watermark consumptions of FCM buffers and channels by applications since the start of the DB2 instance. The high-watermark consumption values of applications are retained even if they have disconnected from the database already.
- Specify this parameter with the numApps option, to limit the maximum number of applications that the db2pd command reports in the current and HWM consumption statistics.
- -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.
- -fixstack existing_stack_filename
- Reads an existing stack file and generates a new file in the same location, with the same file name, but with an additional .fmt file extension. The new .fmt file generated will have improved symbol details on some of the frames in the stack trace if the library where the symbol is defined is available when running this command.
NoteOnly applicable on Linux operating systems.
- -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.
NoteThe -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.
- 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.
NoteThis has no affect on Windows operating systems.
- -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.
- -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. Descriptions of each reported element can be found in the high availability disaster recovery section of the Database Monitoring Guide and Reference.
See the -hadr usage notes.
- -h | -help
- Displays the online help information.
- -inst
- Returns all instance-scope information.
- -interactive
- Specifies to override the values specified for the DB2PDOPT environment variable when running the db2pd command.
- -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.
- -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.
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.
- -memblocks
- Returns information about the memory pools.
- dbms
- Only report blocks in the dbms memory set.
- fcm
- Only report blocks in the fast communication manager memory set.
- fmp
- Only report blocks in the fenced mode procedure memory set.
- appctl id
- Only report blocks in the application control set.
- all
- Report blocks from all 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).
- -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.
NoteStarting 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.NoteYou 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.
- -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 - -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.
- -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 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.
NoteThe 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)
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)
- -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
- -storagepaths
- Returns information about the automatic storage paths defined for the database.
See the -storagepaths usage notes. See also Sample output of the db2pd -storagepaths command.
- -sysplex
- Returns information about the list of servers associated with the database alias indicated by the db parameter. If the -databasecommand 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 Tablespace ID command parameter to display the information about a specific table space and its containers.
See the -tablespaces usage notes. See alsoSample 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=tablespace_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.
For temporary table compression, the -tcbstats output will include two new columns in the TCB Table Stats section of the output.- StoredBytes: This corresponds to the "Total stored temp bytes" from the db2pd -temptable output.
- BytesSaved: This corresponds to the Total bytes saved value from the db2pd -temptable output.
- -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
- -transactions
- Returns information about active transactions. If a transaction handle is specified, information is returned about that transaction handle. If an application handle is specified, information is returned about the application handle of the transaction. See the -transactions usage notes.
- -utilities
- Reports utility information. Descriptions of each reported element can be found in the utilities section of the Database Monitoring Guide and Reference.
See the -utilities usage notes.
- -v | -version
- Displays the current version and service level of the installed DB2 database product.
- -wlocks
- Displays the owner and waiter information for each lock being waited on. In the Sample output of the db2pd -wlocks command, the lock status (Sts) value of G designates the owner of the lock, while a Sts value of W designates the waiter of that lock.
- file=filename
- Sends the -wlocks output to a specified file.
- -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.
- -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.
- -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.
Examples
Use the db2pd command, from the command line, in the following way to obtain information about agents that are servicing client requests:
db2pd -agentsUse 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"
db2pdUse 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.outUse the db2pd command, from the command line, in the following way to obtain all database and instance-scope information:
db2pd -inst -alldbsUse 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 above, 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:
NoteIt should be noted that the FORCE APPLICATION command can be used as an alternative to what is described below.
- 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 above, but notes that both fenced vendor processes have been sent terminate requests and will be terminated shortly.
Usage notes
The following sections describe the output produced by the different db2pd parameters.
- -activestatements
- -agents
- -apinfo
- -applications
- -bufferpools
- -catalogcache
- -dbcfg
- -dbmcfg
- -dynamic
- -edus
- -fcm
- -fmp
- -fmpexechistory | -fmpe
- -hadr
- -latches
- -locks
- -logs
- -memblocks
- -mempools
- -memsets
- -osinfo
- -pages
- -recovery
- -reopt
- -reorgs
- -scansharing
- -serverlist
- -serviceclasses
- -sort
- -static
- -statisticscache
- -storagepaths
- -sysplex
- -tablespaces
- -tcbstats
- -temptable
- -thresholds
- -transactions
- -utilities
- -wlocks
- -workactionsets
- -workclasssets
- -workloads
-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.
-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.
- 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.
- 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).
-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 microseconds.
- 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 microseconds.
- 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 microseconds.
- 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.
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 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.
- StmtID
- The 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.
- StmtID
- The statement identifier for this variation.
- 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.
-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 -catalogcache command.
-hadr parameter
For the -hadr parameter, the following information is returned:
- Role
- The current HADR role of the database. For the list of possible values, refer to the hadr_role monitor element.
- State
- The current HADR state of the database. For the list of possible values, refer to the hadr_state monitor element.
- SyncMode
- The current HADR synchronization mode of the database. For the list of possible values, refer to the hadr_syncmode monitor element.
- HeartBeatsMissed
- The number of missed heartbeats on the HADR connection. If the database is in HADR primary or standby role, this element indicates the health of the HADR connection. For more information, refer to the hadr_heartbeat monitor element.
- LogGapRunAvg
- The running average of the gap between the primary log sequence number (LSN) and the standby log LSN. The gap is measured in number of bytes. For more information, refer to the hadr_log_gap monitor element.
- ConnectStatus
- The current HADR connection status of the database. For the list of possible values, refer to the hadr_connect_status monitor element.
- ConnectTime
- The time when the current HADR connection status began. For more information, refer to the hadr_connect_time monitor element.
- Timeout
- The number of seconds without any communication from its partner after which an HADR database server will consider that the connection between them has failed. For more information, refer to the hadr_timeout monitor element.
- LocalHost
- The local HADR host name or IP address. For more information, refer to the hadr_local_host monitor element.
- LocalService
- The local HADR TCP service name or port number. For more information, refer to the hadr_local_service monitor element.
- RemoteHost
- The remote HADR host name or IP address. For more information, refer to the hadr_remote_host monitor element.
- RemoteService
- The remote HADR TCP service name or port number. For more information, refer to the hadr_remote_service monitor element.
- RemoteInstance
- The remote HADR instance name. For more information, refer to the hadr_remote_instance monitor element.
- PrimaryFile
- The name of the current log file on the primary HADR database. For more information, refer to the hadr_primary_log_filemonitor element.
- PrimaryPg
- The page number in the current log file indicating the current log position on the primary HADR database. For more information, refer to the hadr_primary_log_page monitor element.
- PrimaryLSN
- The current log position of the primary HADR database. Log sequence number (LSN) is a byte offset in the database's log stream. For more information, refer to the hadr_primary_log_lsn monitor element.
- StandByFile
- The name of the current log file on the standby HADR database. For more information, refer to the hadr_standby_log_filemonitor element.
- StandByPg
- The page number in the current log file indicating the current log position on the standby HADR database. For more information, refer to the hadr_standby_log_page monitor element.
- StandByLSN
- The current log position of the standby HADR database. For more information, refer to the hadr_standby_log_lsn monitor element.
- StandByRcvBufUsed
- The percentage of standby log receiving buffer used. This property is reported only on the HADR standby database. A high value might indicate that the HADR standby database is not processing logs fast enough. If this value frequently reaches 100, performance on the primary database might be negatively impacted. On the other hand, if the value is consistently low, the standby log receiving buffer is under utilized; consider reducing the buffer size. Refer to the DB2_HADR_BUF_SIZE registry variable for tuning the standby log receiving buffer size.
- PeerWindowEnd
- The point in time which the primary database promises to stay in peer or disconnected peer state, as long as the primary database is active.
- PeerWindow
- Indicates the value of the HADR_PEER_WINDOW database configuration parameter.
- ReplayOnlyWindowStatus
- Indicates whether DDL or maintenance-operation replay is in progress on the standby. Normally, the value is "Inactive", but when the replay-only window is active, the value is "Active".
- ReplayOnlyWindowStartTime
- Indicates the time at which the current replay-only window (if there is one) became active.
- MaintenanceTxCount
- Indicates the total number of existing uncommitted DDL or maintenance transactions executed so far in the current replay-only window (if there is one).
-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.
-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
- Pool
- Partition
- Table
- AlterTab
- ObjectTab
- OnlBackup
- DMS Seq
- Internal P
- Internal V
- Key Value
- No Lock
- Block Lock
- LOG Release
- LF Release
- LFM File
- LOB/LF 4K
- APM Seq
- Tbsp Load
- Table Part
- DJ UserMap
- DF NickNm
- CatCache
- OnlReorg
- Buf Pool
- Mode
- The lock mode. The possible values are:
- IS
- IX
- S
- SIX
- X
- IN
- Z
- U
- NS
- NW
- Sts
- The lock status. The possible values are:
- G (granted)
- C (converting)
- W (waiting)
- Owner
- The transaction handle that owns 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 above) 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.
-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.
- Overhead
- The internal overhead required for the pool structures.
- LogSz
- The current total of pool memory requests.
- LogUpBnd
- The current logical size upper bound.
- LogHWM
- The logical size high water mark.
- PhySz
- The physical memory required for logical size.
- PhyUpBnd
- The current physical size upper bound.
- PhyHWM
- The largest physical size reached during processing.
- Bnd
- The internal bounding strategy.
- 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.
- Cmt(Kb)
- All memory that has been committed by the DB2 database, and occupies physical RAM, paging space, or both.
- HWM(Kb)
- Maximum memory ever allocated to memory pools.
- 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.
-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.
- 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 MDC_BMP.
- 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
- In Version 9.8 Fix Pack 4 and later fix packs, 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
-
- Tablespace 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.
See Sample output of the db2pd -serverlist command.
-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 Agent Priority and Catalog Agent Priority: Effective agent priority setting for service class that maps to priority recorded in SYSCAT.SERVICECLASSES
- 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)
- 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 towards 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 towards 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).
For additional details about the returned information using the -statisticscache command parameter, see the topic "Catalog statistics tables".
-storagepaths parameter
For the -storagepaths parameter, the following information is returned:
- Number of Storage Paths
- The number of automatic storage paths defined for the database.
- PathName
- The 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.
- PathID
- The storage path identifier.
- PathState
- Current state of the storage path: NotInUse, InUse, or DropPending.
See Sample output of the 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, indicates whether buffered I/O was specified by the user at CREATE TABLESPACE or ALTER TABLESPACE time. The possible values are:
- Yes
- No
- 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
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 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 RUNSTATScommand.
- 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.
- RowsComp
- The total number of rows that were compressed.
- RowsUNcomp
- The total number of rows that were uncompressed.
- 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.
- 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.
- 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. Scanning the index requires probing to find the start key for the index scan.
- 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 : 0All 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:
- Threshold Name: Threshold name
- Threshold ID: Threshold identifier
- Domain: Threshold domain
- Domain ID: Threshold domain identifier
- Predicate ID: Threshold predicate identifier
- Maximum Value: Threshold maximum value
- Enforcement: Threshold enforcement scope
- Queuing: Threshold is a queueing threshold
- Queue Size: Threshold queue size setting
- Collect Flags: Setting of COLLECT ACTIVITY DATA option for threshold
- Partition Flags: Partitions where COLLECT ACTIVITY option setting applies
- Execute Flags: Threshold action setting
- Enabled: State of threshold, enabled or disabled
- Check Interval (seconds): Frequency setting for threshold condition checks
- Remap Target Serv. Subclass: Target service subclass setting for remapping threshold action
- Log Violation Evmon Record: THRESHOLD VIOLATIONS event monitor log setting
If the threshold is a queuing threshold, the queue section will also show:
- Queue information for threshold: Threshold Name
- Max Concurrency: Maximum concurrency setting
- Concurrency: Actual concurrency value
- Max Queue Size: Maximum threshold queue size setting
- Agents Currently Queued: At the catalog node, the list of all agents waiting in the threshold queue (shown only when agents are queued)
See Sample output of the db2pd -thresholds command.
-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).
-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.
-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 identifer.
- AppID
- The application ID. This values is the same as the appl_id monitor element data.
See Sample output of the db2pd -wlocks command.
-workactionsets parameter
For the -workactionsets parameter, the following information is returned:
- Address
- Work action set ID
- Work action set name
- Associated work class set ID
- Type of object work action set is associated (database or service class)
- ID of the object (service class or database) work action set is associated with
- All the work actions within the work action set:
- address
- action ID
- action type
- reference object ID (threshold ID or service class ID or null depending on the action type)
-workclasssets parameter
For the -workclasssets parameter, the following information is returned:
- address
- work class ID
- reference counter (number of different work action sets that reference this work class set)
- All the work classes within the work class set (shown in their evaluation order):
- address
- class ID
- class name
- class type
- schema name
- from value
- to value
- range units
-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
- The 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 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 (nodes) in the instance before the add database partition server operation occurred.
The following 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 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 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 is a sample of the output of the db2pd -apinfo command:
db2pd -apinfo 12 -db mydb10 Database Partition 0 -- Database MYDB10 -- Active -- Up 0 days 00:03:28 Application : Address : 0x0780000000D76EE0 AppHandl [nod-index] : 12 [000-00012] Application PID : 1384708 Application Node Name : boson IP Address: n/a Connection Start Time : (1195265036)Fri Nov 16 21:03:56 2007 Client User ID : venus System Auth ID : VENUS Coordinator EDU ID : 1801 Coordinator Partition : 0 Number of Agents : 1 Locks timeout value : 4294967294 seconds 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 : Lock-wait Application Name : db2bp Application ID : *LOCAL.venus.071117020356 ClientUserID : n/a ClientWrkstnName : n/a ClientApplName : n/a ClientAccntng : n/a List of active statements : *UOW-ID : 8 Activity ID : 2 Package Schema : NULLID Package Name : SQLC2G13 Package Version : Section Number : 201 SQL Type : Dynamic Isolation : CS Statement Type : DML, Select (blockable) Statement : select * from t2 List of inactive statements of current UOW : UOW-ID : 8 Activity ID : 1 Package Schema : NULLID Package Name : SQLC2G13 Package Version : Section Number : 203 SQL Type : Dynamic Isolation : CS Statement Type : DML, Insert/Update/Delete Statement : insert into t1 values 1 - -catalogcache
- The following is a sample of the SYSTABLES output of the db2pd -catalogcache command:
Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:05:34 Catalog Cache: Configured Size 1064960 Current Size 78272 Maximum Size 4294901760 High Water Mark 131072 SYSTABLES: Address Schema Name Type TableID TbspaceID LastRefID CatalogCacheLoadingLock CatalogCacheUsageLock Sts 0x07800000232FF820 SYSIBM SYSTABLES T 5 0 19288214 0001000007800000232FF82043 0000000500001804232FF82043 V 0x07800000232FD360 SYSCAT TABLES V 0 0 19288214 0001000007800000232FD36043 00000005000CC907232FD36043 V 0x07800000232FFB60 KEON014 EMPLOYEE 0 0 0 19288214 0001000007800000232FFB6043 000000050013AE07232FFB6043 I 0x07800000232FC500 SYSTOOLS POLICY 0 0 0 19288214 0001000007800000232FC50043 00000000000000000000000000 I 0x07800000232FCF40 KEON014 DEPT T 4 2 19288214 0001000007800000232FCF4043 000000050013AE06232FCF0343 V 0x07800000238FCF40 KEON014 DEPT T 4 2 19288214 0001000007800000238FCF4043 000000050013AE06238FCF0143 S 0x07800000234433A0 KEON014 SALARY 0 0 0 19288214 0001000007800000234433A043 000000050013AF00234433A043 I - -edus
- The following 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 - -fmpexechistory | -fmpe
- The following 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 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 ; - -logs
-
The following 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 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: 20The following is a sample of the output of thedb2pd -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 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.
NoteThe 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
-
Following 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 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 Agent Priority = 0 Catalog Agent Priority = Default Effective Prefetch Priority = Medium Catalog Prefetch Priority = Default Effective Bufferpool Priority = Low Catalog Bufferpool Priority = Default Effective Outbound Correlator = None Catalog Outbound Correlator = 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 Agent Priority = 0 Catalog Agent Priority = Default 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 - -storagepaths
- -tablespaces
-
The following is a sample of the output of the db2pd -tablespaces command showing information such as PathsDropped and PathID that is applicable to automatic storage 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: ... 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.LRGA 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 example below 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 NumCntrs MaxStripe LastConsecPg Name 0x00002B9DCA582720 0 DMS Regular 4096 4 Yes 4 1 1 Off 1 0 3 SYSCATSPACE 0x00002B9DCA583560 1 DMS UsrTmp 4096 2 Yes 2 1 1 Off 1 0 1 DMSUSRTEMP 0x00002B9DCA5863E0 2 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 USERSPACE1 0x00002B9DCA587220 3 DMS SysTmp 4096 2 Yes 2 1 1 Off 1 0 1 DMSSYSTEMP 0x00002B9DCA58A0A0 4 DMS Large 4096 4 Yes 4 1 1 Off 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 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 above are cumulative, and are updated as temporary tables are dropped. As such, these counters represent only historical information.
- -thresholds
-
The following is a sample of the threshold information output for a database threshold and its queue.
Sample threshold output:
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 - -sort
- The following 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 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 - -workloads
-
The following 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 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 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