Retrieves information from the DB2® database system memory sets.
Authorization
One of the following authority
levels is required:
- The SYSADM authority level.
- The SYSCTRL authority level.
- The SYSMAINT authority level.
- The SYSMON authority level.
When the SYSMON authorization level is
granted, the following options are not available:
Note: On Windows, you must have administrator
authority to use the db2pd command.
Required connection
There is no minimum
connection requirement. However, if a database scope (-db)
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-+--+
| (1) |
+------- -global-----------------------------------+
| .-,--------. |
| V | |
+- -host----hostname-+-----------------------------+
| .-,------------------------------. |
| V | |
'- -member--+---member_number--|--member_range-+-+-'
'-all--------------------------------'
>--+---------------------------------------------------------------------------+-->
'- -agents--+-------------+--+--------------+--+---------------+--+-------+-'
'-db=database-' +-AgentId------+ '-file=filename-' '-event-'
'-app=AppHandl-'
>--+--------------------------------------------------------+--->
| .-all------. .-all-----. |
'- -apinfo--+----------+--+---------+--+---------------+-'
'-AppHandl-' '-MaxStmt-' '-file=filename-'
>--+------------------------------------------------------+----->
'- -applications--+---------------+--+---------------+-'
+-AppHandl------+ '-file=filename-'
'-agent=AgentId-'
>--+--------------------------------------------+--------------->
'- -bufferpools--+------+--+---------------+-'
'-bpID-' '-file=filename-'
>--+-----------------------------------+------------------------>
'- -catalogcache--+---------------+-'
'-file=filename-'
>--+-----------------------------------+------------------------>
'-+- -cleaner---------------------+-'
'- -dirtypages--+-------------+-'
+-bp=bpID-----+
+-count=count-+
+-summary-----+
+-temponly----+
'-fixedonly---'
>--+---------------------+-------------------------------------->
'- -command--filename-'
>--+----------------------------------+------------------------->
| .-,------------. |
| V | |
'-+- -database-+----databasename-+-'
+- -db-------+
'- -d--------'
>--+----------------------------+------------------------------->
'- -dbcfg--+---------------+-'
'-file=filename-'
>--+-----------------------------+--+------------+-------------->
'- -dbmcfg--+---------------+-' '- -dbptnmem-'
'-file=filename-'
>--+-----------------------------------------------------------------------------------------+-->
'- -dump--+---------------------------+--+----------------------------------------------+-'
+-all--+------------------+-+ '-dumpdir=directory--+-----------------------+-'
| '-apphdl=AppHandle-' | '-timeout=timeout-value-'
+-apphdl=AppHandle----------+
+-eduid-apphdl=AppHandle----+
'-pid-----------------------'
>--+------------------------------+----------------------------->
'- -dynamic--+---------------+-'
+-anch=anchID---+
'-file=filename-'
>--+------------------------------------------------------+----->
'- -edus--+------------------------------------------+-'
'-interval=interval--+-------+--+--------+-'
'-top=n-' '-stacks-'
>--+--------------+--------------------------------------------->
'- -everything-'
>--+------------------------------------------------+----------->
'- -fcm--+-----+--+---------+--+---------------+-'
'-hwm-' '-numApps-' '-file=filename-'
>--+------------------+--+-------+--+--------+------------------>
'- -file--filename-' '- -fmp-' '- -full-'
>--+--------------------------------------------------------------+-->
'-+- -fmpexechistory-+--+-------------+--+-----+--+----------+-'
'- -fmpe-----------' '-+-pid=pid-+-' '-n=n-' '-genquery-'
'-tid=tid-'
>--+-----------------------------------------------------+------>
'- -fvp--+-agent_eduid-+--+------+--+---------------+-'
+-LAM1--------+ '-term-' '-file=filename-'
+-LAM2--------+
'-LAM3--------'
>--+-------+--+------+--+---------------------------+----------->
'- -gfw-' '- -ha-' '- -hadr--+---------------+-'
'-file=filename-'
>--+--------+--+--------+--+---------------+-------------------->
'- -help-' '- -inst-' '- -interactive-'
>--+-----------------------------------------+------------------>
'- -latches--+-------+--+---------------+-'
'-group-' '-file=filename-'
>--+------------------------------------------------------------+-->
'- -load--+-----------------+--+---------------+--+--------+-'
'-loadID="LOADID"-' '-file=filename-' '-stacks-'
>--+------------------------------------------------------------------+-->
'- -locks--+---------+--+---------------+--+-----------+--+------+-'
'-TranHdl-' '-file=filename-' '-showlocks-' '-wait-'
>--+---------------------------+-------------------------------->
'- -logs--+---------------+-'
'-file=filename-'
>--+--------------------------------------------------------------------------------------+-->
| .-,-----------. |
| V | |
'- -memblocks----+-all-----+-+--+-----+--+--------+--+------+--+--------+--+---------+-'
+-dbms----+ '-top-' '-blocks-' '-sort-' '-PoolID-' '-pid=pid-'
+-fcm-----+
+-fmp-----+
+-appl----+
+-db------+
'-private-'
>--+-------------------------------+---------------------------->
'- -mempools--+---------------+-'
'-file=filename-'
>--+------------------------------+----------------------------->
'- -memsets--+---------------+-'
'-file=filename-'
>--+---------------------------------------+-------------------->
'- -osinfo--+------+--+---------------+-'
'-disk-' '-file=filename-'
>--+---------------------------------------------------+-------->
'- -pages--+------+--+---------+--+---------------+-'
'-bpID-' '-summary-' '-file=filename-'
>--+--------+--+-------------------------------+---------------->
+- -q----+ '- -recovery--+---------------+-'
+- -quit-+ '-file=filename-'
+-q------+
'-quit---'
>--+----------------------------+------------------------------->
'- -reopt--+---------------+-'
'-file=filename-'
>--+----------------------------------------+------------------->
'- -reorgs--+--------------------------+-'
'-index--+---------------+-'
'-file=filename-'
>--+----------------------------------+--+------------+--------->
'- -repeat--+---------+--+-------+-' '- -runstats-'
'-num sec-' '-count-'
>--+---------------------------------------------------------------------------+-->
'--- -scansharing----+----------------------------------------------------+-'
'-obj=objectID--pool=poolID--+---------------------+-'
+-all-----------------+
'-index=--+-indexID-+-'
'-all-----'
>--+--------------------------------------+--------------------->
'- -serviceclasses--+----------------+-'
'-serviceclassID-'
>--+------------------------------+----------------------------->
'- -sort--+------------------+-'
'-apphdl=AppHandle-'
>--+---------------------------------------------------------------------------------+-->
'- -stack--+-all--------------+--+----------------------------------------------+-'
+-apphdl=AppHandle-+ '-dumpdir=directory--+-----------------------+-'
'-eduid------------' '-timeout=timeout-value-'
>--+-----------------------------+------------------------------>
'- -static--+---------------+-'
'-file=filename-'
>--+-----------------------------------------------------------------------------------------------------+-->
'- -statisticscache--+--------------------+--+---------------+--+-----------------------------------+-'
+- -db--databasename-+ '-file=filename-' +-summary---------------------------+
'- -alldbs-----------' +-detail----------------------------+
'-find schema=schema--object=object-'
>--+----------------+------------------------------------------->
'- -storagepaths-'
>--+-----------------------------------------------+------------>
'- -sysplex--+-------------+--+---------------+-'
'-db=database-' '-file=filename-'
>--+-----------------------------------------------------------------------------------+-->
'- -tablespaces--+---------------+--+-------+--+---------------+--+---------------+-'
'-Tablespace ID-' '-group-' '-trackmodstate-' '-file=filename-'
>--+------------------------------------------------------------------------------------------------------------+-->
'- -tcbstats--+-----------+--+-------+--+-----------------------------------------------+--+---------------+-'
'-nocatalog-' +-all---+ '-TbspaceID=tablespace_ID--+------------------+-' '-file=filename-'
'-index-' '-TableID=table_ID-'
>--+------------------------+----------------------------------->
'- -temptable--+-------+-'
'-reset-'
>--+-------------------------------+---------------------------->
'- -thresholds--+-------------+-'
'-thresholdID-'
>--+-----------------------------------------------------+------>
'- -transactions--+--------------+--+---------------+-'
+-TranHdl------+ '-file=filename-'
'-app=AppHandl-'
>--+--------------------------------+--+-----------+------------>
'- -utilities--+---------------+-' '- -version-'
'-file=filename-'
>--+-----------------------------+------------------------------>
'- -wlocks--+---------------+-'
'-file=filename-'
>--+--------------------------------------------------+--------->
'- -workactionsets--+-----------------+--+-------+-'
'-workactionsetID-' '-group-'
>--+------------------------------------------------+----------->
'- -workclasssets--+----------------+--+-------+-'
'-workclasssetID-' '-group-'
>--+----------------------------------------+------------------><
'- -workloads--+------------+--+-------+-'
'-workloadID-' '-group-'
Notes:
- The -global parameter has been deprecated.
You can use the -member all parameter options
to obtain information globally.
Command parameters
- -activestatements
- Returns
information about activities that are currently being processed for
connected applications. Examples of such applications include dynamic
SQL statements, static SQL statements and loads.
- apphdl=appHandle
- If an application handle is specified, information is returned
about that particular application.
- file=filename
- Sends the -activestatements output to a specified
file.
See
the -activestatements usage
notes.
- -addnode
- Returns progress information 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.
- -agents
- Returns information about agents.
If an agent ID is specified,
information is returned about the agent. If an application ID is specified,
information is returned about all the agents that are performing work
for the application. Specify this command parameter with the -inst parameter,
if you have chosen a database that you want scope output for.
- event
- This option returns metrics for the event being processed by the
agent. The metrics returned include the last time that the event was
changed, the state of the event, the type of event, the object of
the event, and the event object name.
See the agents usage
notes.
- -apinfo
- Displays the detailed information about applications including
the execution of dynamic SQL statements of the current unit of work
(UOW), if it is applicable.
- AppHandl
- If an application handle is specified, information is returned
about that particular application. The default is to display information
for all applications running at that partition.
- MaxStmt
- If a number of maximum statements is specified, the information
for the most recent of SQL statements, equalling the maximum number
specified, is returned. The default is to display information for
all the executed SQL statements.
- file=filename
- Sends the -apinfo output to a specified file.
See Sample
output of the db2pd -apinfo command.
Note: To
capture the past history of a unit of work (UOW) including the SQL
statement text for the applications, activate a deadlock event monitor
using the statement history clause. For example, use one of the following
statements:
create event monitor testit for deadlocks with details history write to file path global
create event monitor testit for deadlocks with details history write to table
The
CREATE EVENT MONITOR statement has additional options, such as the
ability to specify the name of the table space and table into which
data will be written. For details, see the CREATE EVENT MONITOR statement
description. The event monitor with statement history capability affects
all applications and increases the monitor heap usage by the DB2 database manager.
See
the -apinfo usage
notes.
- -applications
- Returns information about applications.
If an application ID
is specified, information is returned about that application.
If
an agent ID is specified, information is returned about the agent
that is working on behalf of the application. See the -applications usage
notes.
- -bufferpools
- Returns information about the buffer pools. If a bufferpool ID
is specified, information is returned about the bufferpool. See the -bufferpools usage
notes.
- -catalogcache
- Returns information about the catalog cache, which maintains an
in-memory version of statistics. 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.
- -cleaner
- Dumps page cleaner related information from a database. This option
must be preceded by an active database by specifying the -database or -db option
with the proper active database name.
See Sample output of the -cleaner option.
- -command filename
- Specifies to read and execute the db2pd command
options that are specified in the file.
- -database | -db | -d databasename
- Specifies that the command attaches to the database
memory sets of the specified database. Specify the database name,
not the alias name.
- -dbcfg
- Returns the settings of the database configuration parameters.
See the -dbcfg usage
notes.
- -dbmcfg
- Returns the settings of the database manager configuration parameters.
Specify this option with the -inst command
parameter, if you have chosen a database for which you want scope
output. See the -dbmcfg usage
notes.
- -dbpartitionnum database_partition_number
- Specifies
that the command is to run on the specified local or remote database
partition server.
- -dbptnmem
- Lists database partition memory statistics.
- -dirtypages
- Dumps the dirty pages from each bufferpool in the database. This
option must be preceded by an active database by specifying the -database or -db option
with the proper active database name.
- bpID
- Specify this option to dump dirty pages from the specified bufferpool.
- count
- Specify this option to dump the first count number
of dirty pages in each bufferpool.
- summary
- Specify this option to dump recovery related information of each
bufferpool.
- temponly
- Specify this option to dump temporary dirty pages from each bufferpool.
- fixedonly
- Specify this option to dump dirty pages that are fixed from each
bufferpool.
See Sample
output of the -dirtypages option.
- -dump
- Produces stack trace and binary dump files in the diagpath directory.
Only available on UNIX operating
systems.
- Specify with the all command parameter to
produce stack trace files and binary dump files for all agents in
the current database partition.
- Specify with the all parameter
and an apphdl=appHandle parameter
to return all EDUs associated with the specified appHandle application.
- Specify with an EDU ID of eduid and
an apphdl=appHandle parameter
to return information about the specified EDU if it is associated
the appHandle application.
- Specify with an apphdl=appHandle parameter
to return just the EDU of the coordinator agent for the appHandle application.
- Specify with the pid option to produce a stack
trace file and binary dump file for a specific agent.
You
can specify the following parameters with the above parameters:
- dumpdir=directory
- Specifies
a directory where the stack files of EDUs running in db2sysc processes
are to be redirected. All other stack files are written in the DIAGPATH directory.
An absolute path must be specified and the specified directory must
exist. This option is available for UNIX and Linux operating systems only.
- timeout=timeout-value
- Specifies the time in seconds during which the stack files are
redirected to the directory specified.
- -dynamic
- Returns information about the execution of dynamic SQL. See the -dynamic usage
notes.
- anch=anchID
- If an anchor identifier is specified, information is returned
about that particular dynamic SQL.
- file=filename
- Sends the -dynamic output to a specified file.
- -edus
- Lists all EDUs in the instance. In the case
of a sustained trap, specifying this option outputs the EDU
Name indicating that the EDU is suspended.
- interval=interval
- Only available on UNIX operating
systems. If an interval is specified, two snapshots of the EDUs are
taken, interval seconds apart. Two new columns
are then included in the list of EDUs: USR DELTA which
displays the delta of CPU user time across the interval,
and SYS DELTA which displays the delta
of CPU system time across the interval. If an EDU
is added part way through the interval it is included
in the list, with the delta calculated from when it was added. If
an EDU is deleted part way through the interval it
is not included in the list at all.
- top=n
- Specifies n EDUs that are to be displayed,
where n is an integer value. The EDUs that take
up the max CPU time during interval specified are displayed first.
- stacks
- Dumps the stacks for the EDUs that are displayed.
See
the -edus usage
notes. See also Sample output of
the db2pd -edus command.
- -everything
- Runs all options for all databases on all database partition servers
that are local to the server.
- -fcm
- Returns information about the fast communication manager.
- Specify this parameter with the -inst parameter,
if you have chosen a database for which you want scope output.
- Specify this parameter with the hwm parameter,
to retrieve high-watermark consumptions of FCM buffers and channels
by applications since the start of the DB2 instance.
The high-watermark consumption values of applications are retained
even if they have disconnected from the database already.
- Specify this parameter with the numApps option,
to limit the maximum number of applications that the db2pd command
reports in the current and HWM consumption statistics.
See the -fcm usage
notes.
- -file filename
- Specifies to write the output to the specified file.
- -fmp
- Returns information about the process during which the fenced
routines are executed. See the -fmp usage
notes.
- -fmpexechistory | -fmpe
- Displays the fenced routine history that attempted to be loaded
and executed. Note that this parameter is available starting in Fix
Pack 1.
- pid=pid
- Displays detailed thread information about a specific fenced process
ID. If none is specified, detailed information for all processes is
displayed. For thread-safe FMP processes, there will be one execution
history list per thread, and threads are presented in three groups:
Active, Pooled, Forced. For non thread-safe FMP processes, only one
execution history list per process is displayed.
- tid=tid
- Displays historical details for a thread-safe routine using a
specific thread ID. For non thread-safe routine, the thread ID value
will be 1.
- n=n
- Use this option to specify the number of routine execution history
that is to be displayed for each FMP process. The maximum value is
128. If not specified, only the last routine history is returned by
default.
- genquery
- Generates a select query that will return the routine schema,
module, name and specific name according to the routine unique ID.
See the -fmpexechistory
| -fmpe usage notes.
- -full
- Specifies that all output is expanded to its maximum length. If
not specified, output is truncated to save space on the display.
- -fvp
- Displays fenced vendor process information and allows the termination
of a fenced vendor process in situations where it is not responding.
This applies to backup, restore, prune history, load, load copy (roll
forward) and Log Manager, where a vendor media device is being used.
Note: The -database database command
parameter must be used in conjunction with this parameter in order
to connect to the right memory set to gather the information. This
has no affect on Windows operating
systems.
- agent_eduid
- Displays the fenced vendor process information for a DB2 EDU ID of a backup, restore, prune history,
load or load copy (roll forward) agent.
- LAM1
- Displays fenced vendor process information for logarchmeth1.
- LAM2
- Displays fenced vendor process information for logarchmeth2.
- LAM3
- Displays fenced vendor process information for the special case
where the current log archive method configuration parameter is not
set to VENDOR, and so a fenced vendor process needs
to be created temporarily, during ROLLFORWARD DATABASE,
to retrieve logs from a previous vendor archiving method.
- term
- On top of displaying fenced vendor process information, this option
also terminates the fenced vendor process specified.
- -global
- Specifies that db2pd will also be run on remote
hosts. If the -file parameter is specified, a
single file consisting of all the individual files from remote host
will be created on the computer from where you issued the db2pd command.
Note: This
command parameter is available in DB2 Version
9.7 Fix Pack 2 and later fix packs. This parameter is deprecated in DB2 Version 9.7 Fix Pack 4 and later
fix packs.
- -dbp database_partition_number
- Specifies that db2pd will be run on the remote
host of the specified database partition. If no database partition
is specified with the -global option, db2pd will
run on all remote hosts.
- -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.
- -host hostname
- Specifies the host or hosts on which the command is issued. The
command is issued for all members that reside on the host. If this
option is not specified, the command is issued on the local host.
If multiple hosts are specified, all host names must be valid for
the command to complete. This option cannot be specified together
with the -member option.
- -inst
- Returns all instance-scope information.
- -interactive
- Specifies to override the values specified for the DB2PDOPT environment
variable when running the db2pd command.
- -latches
- Reports all latch holders and all latch waiters.
- group
- Just prints the list of holders followed by the list of waiters.
- file=filename
- Sends -latches output to filename.
See
the -latches usage
notes.
- -load
- Displays all load EDU information. This parameter can be combined
with the -host or -member parameter
to display host or member specific load EDU information. Requires
an active database to be specified.
- loadID="LOADID"
- Displays all load EDUs working for the specific load operation
specified by LOADID. If the LOADID specified
does not exist, no information is displayed.
- file=filename
- Redirects the output, excluding stack files, to the specified
file.
- stacks
- Dumps the stack traces for the load EDUs displayed in the diagpath directory.
If this option is used with the loadID option,
the stacks are dumped for the load EDUs working for the specified
load operation. This option is available for UNIX and Linux operating
systems only.
See the -load usage
notes.
- -locks
- Returns information about the locks.
Specify a transaction
handle to obtain information about the locks that are held by a specific
transaction.
Specify with the showlocks command
parameter to return detailed information about lock names. For
row and block locks on partitioned tables and individual data partitions, showlocks displays
the data partition identifier as part of the row with the lock information.
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.
- -member member_number | member_range
- Specifies the member or members on which the command is issued.
If this option is not specified, the command is issued on the current
member. Multiple members can be specified as a comma separated list
of member_number (member1, member2), or using member_range,
where member_range is a range of members (member1-member3),
or using any combination of the first two methods. This option cannot
be specified together with the -host option.
- all
- Specifies that the command is issued on all members, including
members on remote hosts.
- -memblocks
- Returns information about the memory sets. Certain memory sets
are returned based on the scope that the -memblocks parameter
is used in:
- If this parameter is issued with the -inst and -alldbs parameters,
information on the dbms, fcm, fmp, appl,
and db memory sets is returned. The following
command returns information on instance-scope and database-scope memory
sets for all databases:
db2pd -inst -alldbs -memblocks
- If this parameter is issued with the -inst and -db parameters,
information on the dbms, fcm, fmp, appl,
and db memory sets is returned for the specified
database. The following command returns information on instance-scope
and database-scope memory sets for the database sample:
db2pd -inst -db sample -memblocks
- If this parameter is issued within a database scope (-db),
information on the appl and db memory
sets is returned. The following command returns information on database-scope
memory sets for the database sample:
db2pd -db sample -memblocks
- If this parameter is issued on its own with the db2pd command,
it returns information on instance-scope memory sets, which include
the dbms, fcm, and fmp memory
sets. This is the default behavior. The following command returns
the same information as db2pd -inst -memblocks which
returns information on instance-scope memory sets:
db2pd -memblocks
- If this parameter is issued with any of the following parameter
options, the information returned is only that options memory set.
The following command returns information only on the fmp memory
set:
db2pd -memblocks -fmp
- dbms
- Only report memory blocks in the database manager system memory
set. This memory set is a part of instance-scope memory sets.
- fcm
- Only report memory blocks in the fast communication manager memory
set. This memory set is a part of instance-scope memory sets.
- fmp
- Only report memory blocks in the fenced mode process memory set.
This memory set is a part of instance-scope memory sets.
- appl
- Only report memory blocks in the application memory set. This
memory set is a part of database-scope memory sets.
- db
- Only report memory blocks in the database memory set. This memory
set is a part of database-scope memory sets.
- all
- Report memory blocks from all memory sets. This includes memory
blocks from instance-scope (-inst) memory sets,
and, on Windows operating
systems only, the private memory set.
Note: The database scope (-db or -alldbs)
must be specified to include memory blocks from database-scope memory
sets (database and application memory sets).
- top
- Report the top memory consumers for each set.
- blocks
- Report the memory blocks for each set.
- sort
- Report the sorted memory blocks for each pool in each set.
- PoolID
- Report memory blocks from a specific pool.
- pid=pid
- Report memory blocks from a specific process id (for UNIX operating systems only).
- private
- Report memory blocks from the private memory set (for Windows operating systems only).
See the -memblocks usage
notes.
- -mempools
- Returns information about the memory pools.
Specify this option
with the -inst option to include all the instance-scope
information in the returned information. See the -mempools usage
notes.
- -memsets
- Returns information about the memory sets.
Specify this command
parameter with the -inst command parameter to
include all the instance-scope information in the returned information.
See the -memsets usage
notes.
- -osinfo
- Returns operating system information. If a disk path is specified,
information about the disk will be printed. See the -osinfo usage
notes.
- -pages
- Returns information about the buffer pool pages.
- bpID
- If bufferpool ID is specified, only pages from the specified bufferpool
are returned.
- summary
- If this option is specified, only the summary information section
will be displayed.
See the -pages usage
notes. See also Sample output of
the db2pd -pages command.
- -q | -quit | q | quit
- Quit. When the db2pd keyword alone is issued, db2pd runs
in interactive mode. The quit command causes
an exit from this mode back to the standard command prompt.
- -recovery
- Returns information about recovery activity. See the -recovery usage
notes.
- -reopt
- Returns information about cached SQL statements that were re-optimized
using the REOPT ONCE option. See the -reopt usage
notes.
- -reorgs
- Returns information about table and data
partition reorganization. When the index parameter
is added to the command, index reorganization information is returned
along with the table and data partition reorganization information.
Note: Starting with DB2 Version
9.7 Fix Pack 2, 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.7 Fix Pack 1.
Note: You cannot
monitor the progress of index reorganization operations on a database
if you specify the CLEANUP ONLY parameter of
the REORG INDEXES command.
See the -reorgs usage
notes. See also Sample
output of the db2pd -reorgs index command.
- -repeat num sec count
- Specifies that the command is to be repeated after the specified
number of seconds. If a value is not specified for the number of
seconds, the command repeats every five seconds. You can also specify
the number of times the output will be repeated. If you do not specify
a value for count, the command is repeated until
it is interrupted.
When
the db2pd command with the -repeat parameter
is issued on members that are on different hosts, the command is sent
from the local host to the remote hosts. The command runs on each
remote host until the count value specified is
reached. The command runs to completion on one host before it begins
on another host.
- -runstats
- Returns information about the status of the RUNSTATS utility on
table and associated indexes. The status of table statistics collection
is displayed first, followed by the status of index statistics collection.
Sample output of the -runstats option:
db2pd -runstats
Table Runstats Information:
Retrieval Time: 08/13/2009 20:38:20
TbspaceID: 2 TableID: 4
Schema: SCHEMA TableName: TABLE
Status: Completed Access: Allow write
Sampling: No Sampling Rate: -
Start Time: 08/13/2009 20:38:16 End Time: 08/13/2009 20:38:17
Total Duration: 00:00:01
Cur Count: 0 Max Count: 0
Index Runstats Information:
Retrieval Time: 08/13/2009 20:38:20
TbspaceID: 2 TableID: 4
Schema: SCHEMA TableName: TABLE
Status: Completed Access: Allow write
Start Time: 08/13/2009 20:38:17 End Time: 08/13/2009 20:38:18
Total Duration: 00:00:01
Prev Index Duration [1]: 00:00:01
Prev Index Duration [2]: -
Prev Index Duration [3]: -
Cur Index Start: 08/13/2009 20:38:18
Cur Index: 2 Max Index: 2 Index ID: 2
Cur Count: 0 Max Count: 0
- -scansharing
- Returns scan sharing information about all tables that have table
or block index scan sharing in the specified database.
- obj=objectID pool=poolID
- Returns scan sharing information about the specified table.
- all
- Returns scan sharing information for all tables. For each table,
table or range scan sharing information is returned. In addition,
for MDC tables, block index scan sharing information is returned.
- index=
- indexID
- Returns scan sharing information for the specified table, and
block index scan sharing information for the specified block index.
- all
- Returns block
index scan sharing information for all block indexes.
See Sample
output of the db2pd -scansharing command.
See the -scansharing usage
notes.
- -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.
Note: The all option can require a sufficient amount
of memory for it to function properly.
- apphdl=appHandle
- Specify
this option to produce a stack trace file for just the application
handle equal to appHandle. This option is available
for UNIX and Linux operating systems only.
- eduid
- Limits
output to only EDUs with a specified ID. Formatted events and relevant
data are dumped to the pid.tid/EDUID.node.trap.txt trap
files in the db2dump directory. This
option is available for UNIX and Linux operating systems only.
Event
stack will be output in the following order:
Last event (on
the top of event stack)
- Event type and short description
- Customer impact
- Object identifier
- ECF ID, probe
- Top event header
- Top event qualifiers (if any)
- Top event data (if present)
First event (on the bottom of event stack)
- Event type and short description
- Customer impact
- Object identifier
- ECF ID, probe
- Bottom event header
- Bottom event qualifiers (if any)
- Bottom event data (if present)
In the above, ECF ID is ECF identifier (will be formatted as
product,
component,
function)
and probe is a line of code or some unique number (for a function).
Event
flow (recorded event "history") will be output in the following order:
First
event record
- Event type and short description
- Customer impact
- Object identifier
- ECF ID, probe
- Event header
- Object data (if not a string or integer)
Last event record
- Event type and short description
- Customer impact
- Object identifier
- ECF ID, probe
- Event header
- Object data (if not a string or integer)
- dumpdir=directory
- Specifies
a directory where the stack files of EDUs running in db2sysc processes
are to be redirected. All other stack files are written in the DIAGPATH directory.
An absolute path must be specified and the specified directory must
exist. This option is available for UNIX and Linux operating systems only.
- timeout=timeout-value
- Specifies the time in seconds during which the stack files are
redirected to the directory specified.
- -static
- Returns information about the execution of static SQL and packages.
See the -static usage
notes.
- -statisticscache
- Returns information about the statistics cache at the database
level.
- summary
- Summarizes statistics cache. To dump the statistics cache summary
for database sample, issue the following command:
db2pd -db sample -statisticscache summary
- detail
- Specify this option to dump detailed statistics information stored
in the statistics cache for all tables with the latest statistics
collected by real-time statistics gathering. To dump detailed statistics
information stored in the statistics cache for all the databases,
issue the following command:
db2pd -statisticscache detail -alldbs
- find schema=schema object=object
- Specify this option to dump the detailed statistics information
for a specific table with schema as schema name and object as table
name. To dump detailed statistics information for table USER1.T1 of
database sample, issue the following command:
db2pd -db sample -statisticscache find schema=USER1 object=T1
See the -statisticscache usage
notes.
- -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 -database command parameter is not specified,
information is returned for all databases.
Specify this command
parameter with the -inst command parameter, if
you have chosen a database for which you want scope output.
See
the -sysplex usage
notes.
- -tablespaces
- Returns information about the table spaces.
Specify with the group command
parameter to display the information about the containers of a table
space grouped with the table space.
Specify with the Tablespace
ID command parameter to display the information about a
specific table space and its containers.
Specify
with the trackmodstate command parameter to display
the state of a tablespace with respect to the last or next backup.
This parameter requires the trackmod configuration
parameter to be set to Yes.
See the -tablespaces usage
notes. See also Sample
output of the db2pd -tablespaces command.
- -tcbstats
- Returns information about tables and indexes. The total number
of updates on tables, the UDI and real-time statistics UDI counters
(RTSUDI), are returned as well.
- TbspaceID=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.
See the
-tcbstats usage
notes.
- -temptable
- By default, returns the following information about temporary
tables:
- Number of Temp Tables The total number
of temporary tables created and dropped since the database manager
was started or since the last reset of the counters.
- Comp Eligible Temps Temporary tables
that the data base manager has determined is eligible for compression
based on these three properties: query type, minimum
row size, and minimum expected table size.
- Compressed Temps The total number
of temporary tables that were actually compressed. This implies that
the table has enough data so that a compression dictionary is created
for the temporary table.
- Total Stored Temp Bytes The total
number of actual row data for temporary tables that is stored on disk.
This can be from both compressed and non-compressed temporary tables.
- Total Bytes Saved The total bytes
saved by compressing rows.
- Total Compressed Rows A cumulative
count of the number of rows that saved at least one byte using compression.
- Total Temp Table Rows The total number
of rows inserted into all the temporary tables, whether they are compressed
or not. Not all rows inserted into a compressed temporary table are
necessarily compressed.
- reset
- Specify this option to reset all counters to zero.
See the -temptable usage
notes. See also Sample output of
the db2pd -temptable command.
- -thresholds thresholdID
- Returns information about thresholds. thresholdID is
optional, but specifying a threshold ID returns information about
a specific threshold. If thresholdID is not specified,
information for all enabled and disabled thresholds is retrieved.
See
the -thresholds usage
notes. See Sample output of
the db2pd -thresholds command.
- -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.
See the -wlocks usage
notes.
- -workactionsets workactionsetID
- Returns information about all enabled work action sets and all
enabled work actions in these sets.
- group
- Returns the same information grouped by work action
set.
See the -workactionsets usage
notes.
- -workclasssets workclasssetID
- Returns information about all work class sets that are referenced
by an enabled work action set and all work classes in the work class
sets.
- group
- Returns the same information grouped by work class
set.
See the -workclasssets usage
notes.
- -workloads workloadID
- Returns the list of workload definitions, user privilege holders,
and local partition workload statistics in memory at the time the
command is run.
- group
- Returns the same information grouped by workload.
See Sample output of the db2pd
-workloads command.
See the -workloads usage
notes.
Examples
Use the
db2pd command,
from the command line, in the following way to obtain information
about agents that are servicing client requests:
db2pd -agents
Use
the
db2pd command, from the command line, in the
following way to obtain information about agents that are servicing
client requests. In this case, the
DB2PDOPT environment
variable is set with the
-agents parameter before
invoking the
db2pd command. The command uses
the information set in the environment variable when it executes.
export DB2PDOPT="-agents"
db2pd
Use the
db2pd command,
from the command line, in the following way to obtain information
about agents that are servicing client requests. In this case, the
-agents parameter
is set in the file
file.out before invoking the
db2pd command.
The
-command parameter causes the command to
use the information in the
file.out file when
it executes.
echo "-agents" > file.out
db2pd -command file.out
Use the
db2pd command,
from the command line, in the following way to obtain all database
and instance-scope information:
db2pd -inst -alldbs
Use
the
db2pd -fvp command, from the command line,
in the following way to obtain fenced vendor process state information:
- For Log Manager:
- A database named SAMPLE has logarchmeth1 set
to TSM. At any time issue:
db2pd -db sample -fvp lam1
The
resulting output is as follows:
-------------------------------------------------------------------------
Fenced Vendor Process State Information:
-------------------------------------------------------------------------
Log Manager:
-------------------------------------------------------------------------
LOGARCHMETH1 available.
Vendor EDU is available and running.
startTime: 1155581841 20060814145721
function: sqluvint
This tells you that the fenced
vendor process is running in the vendor function sqluvint since
August 14, 2006 14:57. Now, if you feel that this has been running
too long, or you have determined that this process has hung waiting
for TSM resources, you can terminate the fenced vendor process by
issuing:
db2pd -db sample -fvp lam1 term
The
resulting output is as follows:
-------------------------------------------------------------------------
Fenced Vendor Process State Information:
-------------------------------------------------------------------------
Log Manager:
-------------------------------------------------------------------------
LOGARCHMETH1 available.
Vendor EDU is available and running.
startTime: 1155581841 20060814145721
function: sqluvint
This fenced vendor process has been sent a signal to terminate.
This
shows you the same information as 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:
Note: It 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
parameter
For the
-activestatements parameter,
the following information is returned:
- Address
- Address of the current activity.
- AppHandl
- Application handle.
- UOW-ID
- UOW-ID at the start of execution.
- StmtID
- The activity ID of the statement within the UOW-ID.
- AnchID
- The anchor ID of the statement.
- StmtUID
- The unique ID of the statement within the anchor.
- EffISO
- Effective isolation level.
- EffLockTOut
- Effective lock timeout at start.
- EffDegree
- Effective SMP parallelism degree at start.
- StartTime
- The start time of when the statement was executed.
- LastRefTime
- Last application reference time.
-agents parameter
For the
-agents parameter,
the following information is returned:
- AppHandl
- The application handle, including the node and the index.
- AgentPid
- The process ID of the agent process.
- Priority
- The priority of the agent.
- Type
- The type of agent.
- State
- The state of the agent.
- ClientPid
- The process ID of the client process.
- Userid
- The user ID running the agent.
- ClientNm
- The name of the client process.
- Rowsread
- The number of rows that were read by the agent.
- Rowswrtn
- The number of rows that were written by the agent.
- LkTmOt
- The lock timeout setting for the agent.
- LastApplID
- The outbound application ID that the pooled agent serviced last.
- LastPooled
- The timestamp when the agent was pooled.
If the
event option is specified
with the
-agents parameter, the following, additional
information is returned. You use this information to determine whether
an agent continues to process the same task or whether the agent moves
onto new tasks over time.
- AGENT_STATE_LAST_UPDATE_TIME(Tick Value)
- The last time that the event, being processed by the agent, was
changed. The event currently processed by the agent is identified
by the EVENT_STATE, EVENT_TYPE, EVENT_OBJECT, and EVENT_OBJECT_NAME
columns.
- EVENT_STATE
- The state of the event last processed by this agent. The possible
values are EXECUTING and IDLE.
- EVENT_TYPE
- The type of event last processed by this agent. The possible values
are ACQUIRE, PROCESS, and WAIT.
- EVENT_OBJECT
- The object of the event last processed by this agent. The possible
values are COMP_DICT_BUILD, IMPLICIT_REBIND, INDEX_RECREATE, LOCK,
LOCK_ESCALATION, QP_QUEUE, REMOTE_REQUEST, REQUEST, ROUTINE, and WLM_QUEUE.
- EVENT_OBJECT_NAME
- The event object name. If the value of EVENT_OBJECT is LOCK, the
value of this column is the name of the lock that the agent is waiting
on. If the value of EVENT_OBJECT is WLM_QUEUE, the value of the column
is the name of the WLM threshold that the agent is queued on. Otherwise,
the value is NULL.
The possible combinations of EVENT_STATE, EVENT_TYPE, EVENT_OBJECT
and EVENT_OBJECT_NAME column values are listed in the following table:
Table 1. Possible combinations for EVENT_STATE, EVENT_TYPE,
EVENT_OBJECT and EVENT_OBJECT_NAME column valuesEvent description |
EVENT_STATE value |
EVENT_TYPE value |
EVENT_OBJECT value |
EVENT_OBJECT_NAME value |
Acquire lock |
IDLE |
ACQUIRE |
LOCK |
Lock name |
Escalate lock |
EXECUTING |
PROCESS |
LOCK_ESCALATION |
NULL |
Process request |
EXECUTING |
PROCESS |
REQUEST |
NULL |
Wait for a new request |
IDLE |
WAIT |
REQUEST |
NULL |
Wait for a request to be processed at a remote
partition |
IDLE |
WAIT |
REMOTE_REQUEST |
NULL |
Wait on a Query Patroller queue |
IDLE |
WAIT |
QP_QUEUE |
NULL |
Wait on a WLM threshold queue |
IDLE |
WAIT |
WLM_QUEUE |
Threshold name |
Process a routine |
EXECUTING |
PROCESS |
ROUTINE |
NULL |
Recreate an index |
EXECUTING |
PROCESS |
INDEX_RECREATE |
NULL |
Build compression dictionary |
EXECUTING |
PROCESS |
COMP_DICT_BLD |
NULL |
Implicit rebind |
EXECUTING |
PROCESS |
IMPLICIT_REBIND |
NULL |
-apinfo parameter
For the
-apinfo parameter,
the following information is returned:
- AppHandl
- The application handle, including the node and the index.
- Application PID
- The process ID for the application.
- Application Node Name
- The name of the application node.
- IP Address
- The IP address from which the database connection was established.
- Connection Start Time
- The time stamp at which the application connection started.
- Client User ID
- The client user ID.
- System Auth ID
- This is the system authorization ID of the connection.
- Coordinator EDU ID
- The EDU ID of the coordinator agent for the application.
- Coordinator Partition
- The partition number of the coordinator agent for the application.
- Number of Agents
- The number of agents that are working on behalf of the application.
- Locks timeout value
- The lock timeout value for the application.
- Locks Escalation
- The locks escalation flag indicates whether the lock, used by
the application, has been escalated.
- Workload ID
- Workload identifier.
- Workload Occurrence ID
- Workload occurrence identifier.
- Trusted Context
- The name of the trusted context associated with the connection
if the connection is either an implicit trusted connection or an explicit
trusted connection.
- Connection Trust Type
- The connection trust type. This is one of: non-trusted, implicit
trusted, or explicit trusted connection.
- Role Inherited
- This is the role inherited through a trusted connection, if any.
- Application Status
- The status of the application.
- Application Name
- The name of the application.
- Application ID
- The application ID. This value is the same as the appl_id monitor
element data. For detailed information about how to interpret this
value, see the "appl_id - Application ID monitor element".
- UOW-ID
- The ID of the current UOW of the application.
- Activity ID
- The activity ID within the UOW.
- Package Schema
- The package schema.
- Package Name
- The package name.
- Package Version
- The package version.
- 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).
See
Sample output of
the
db2pd -apinfo command.
-applications parameter
For
the
-applications parameter, the following information
is returned:
- ApplHandl
- The application handle, including the node and the index.
- NumAgents
- The number of agents that are working on behalf of the application.
- CoorPid
- The process ID of the coordinator agent for the application.
- Status
- The status of the application.
- Appid
- The application ID. This value is the same as the appl_id monitor
element data. For detailed information about how to interpret this
value, see the documentation for the appl_id monitor
element.
- ClientIPAddress
- The IP address from which the database connection was established.
- EncryptionLvl
- The data stream encryption used by the connection. This is one
of NONE, LOW or HIGH. NONE implies
that no data stream encryption is being used. LOW implies
that the database server authentication type
is set to DATA_ENCRYPT. HIGH implies
that SSL is being used.
- SystemAuthID
- This is the system authorization ID of the connection.
- ConnTrustType
- The connection trust type. This is one of: non-trusted, implicit
trusted connection, or explicit trusted connection.
- TrustedContext
- The name of the trusted context associated with the connection
if the connection is either an implicit trusted connection or an explicit
trusted connection.
- RoleInherited
- This is the role inherited through a trusted connection, if any.
-bufferpools parameter
For
the
-bufferpools parameter, the following information
is returned:
- First Active Pool ID
- The ID of the first active buffer pool.
- Max Bufferpool ID
- The maximum ID of all active buffer pools.
- Max Bufferpool ID on Disk
- The maximum ID of all buffer pools defined on disk.
- Num Bufferpools
- The number of available buffer pools.
- ID
- The ID of the buffer pool.
- Name
- The name of the buffer pool.
- PageSz
- The size of the buffer pool pages.
- PA-NumPgs
- The number of pages in the page area of the buffer pool.
- BA-NumPgs
- The number of pages in the block area of the buffer pool. This
value is 0 if the buffer pool is not
enabled for block-based I/O.
- BlkSize
- The block size of a block in the block area of the buffer pool.
This value is 0 if the buffer pool is
not enabled for block-based I/O.
- NumTbsp
- The number of table spaces that are using the buffer pool.
- PgsLeft
- The number of pages left to remove in the buffer pool if its size
is being decreased.
- CurrentSz
- The current size of the buffer pool in pages.
- PostAlter
- The size of the buffer pool in pages when the buffer pool is restarted.
- SuspndTSCt
- The number of table spaces mapped to the buffer pool that are
currently I/O suspended. If 0 is returned
for all buffer pools, the database I/O is not suspended.
- Automatic
- Shows the self-tuning automatic status. "True" means self-tuning
for this buffer pool is enabled. "False" means self-tuning for this
buffer pool is not enabled.
- DatLRds
- Buffer Pool Data Logical Reads. Indicates the number of data pages
which have been requested from the buffer pool (logical) for regular
and large table spaces.
- DatPRds
- Buffer Pool Data Physical Reads. Indicates the number of data
pages read in from the table space containers (physical) for regular
and large table spaces.
- HitRatio
- Hit ratio for data pages in the buffer pool using formula 1 - DatPRds / DatLRds.
- TmpDatLRds
- Buffer Pool Temporary Data Logical Reads. Indicates the number
of data pages which have been requested from the buffer pool (logical)
for temporary table spaces.
- TmpDatPRds
- Buffer Pool Temporary Data Physical Reads. Indicates the number
of data pages read in from the table space containers (physical) for
temporary table spaces.
- HitRatio
- Hit ratio for temporary data pages in the buffer pool using formula
1 - TmpDatPRds / TmpDatLRds.
- IdxLRds
- Buffer Pool Index Logical Reads. Indicates the number of index
pages which have been requested from the buffer pool (logical) for
regular and large table spaces.
- IdxPRds
- Buffer Pool Index Physical Reads. Indicates the number of index
pages read in from the table space containers (physical) for regular
and large table spaces.
- HitRatio
- Hit ratio for index pages in the buffer pool using formula 1 - IdxPRds / IdxLRds.
- TmpIdxLRds
- Buffer Pool Temporary Index Logical Reads. Indicates the number
of index pages which have been requested from the buffer pool (logical)
for temporary table spaces.
- TmpIdxPRds
- Buffer Pool Temporary Index Physical Reads. Indicates the number
of index pages read in from the table space containers (physical)
for temporary table spaces.
- HitRatio
- Hit ratio for temporary index pages in the buffer pool using formula
1 - TmpIdxPRds / TmpIdxLRds.
- DataWrts
- Buffer Pool Data Writes. Indicates the number of times a buffer
pool data page was physically written to disk.
- IdxWrts
- Buffer Pool Index Writes. Indicates the number of times a buffer
pool index page was physically written to disk.
- DirRds
- Direct Reads From Database. The number of read operations that
do not use the buffer pool.
- DirRdReqs
- Direct Read Requests. The number of requests to perform a direct
read of one or more sectors of data.
- DirRdTime
- Direct Read Time. The elapsed time (in milliseconds) required
to perform the direct reads.
- DirWrts
- Direct Writes to Database. The number of write operations that
do not use the buffer pool.
- DirWrtReqs
- Direct Write Requests. The number of requests to perform a direct
write of one or more sectors of data.
- DirWrtTime
- Direct Write Time. The elapsed time (in milliseconds) required
to perform the direct writes.
- AsDatRds
- Buffer Pool Asynchronous Data Reads. Indicates the number of data
pages read in from the table space containers (physical) by asynchronous
engine dispatchable units (EDUs) for all types of table spaces.
- AsDatRdReq
- Buffer Pool Asynchronous Read Requests. The number of asynchronous
read requests.
- AsIdxRds
- Buffer Pool Asynchronous Index Reads. Indicates the number of
index pages read in from the table space containers (physical) by
asynchronous engine dispatchable units (EDUs) for all types of table
spaces.
- AsIdxRdReq
- Buffer Pool Asynchronous Index Read Requests. The number of asynchronous
read requests for index pages.
- AsRdTime
- Buffer Pool Asynchronous Read Time. Indicates the total amount
of time spent reading in data and index pages from the table space
containers (physical) by asynchronous engine dispatchable units (EDUs)
for all types of table spaces. This value is given in 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:
- 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:
- 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:
- 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:
- 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:
- 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.
- StmtUID
- The unique statement identifier.
- EnvID
- The environment identifier.
- Iso
- The isolation level of the environment.
- QOpt
- The query optimization level of the environment.
- Blk
- The blocking factor of the environment.
- Dynamic SQL Variations:
-
- AnchID
- The hash anchor identifier.
- StmtUID
- The unique statement identifier.
- EnvID
- The environment identifier for this variation.
- VarID
- The variation identifier.
- NumRef
- The number of times this variation has been referenced.
- Typ
- The internal statement type value for the variation section.
- Lockname
- The variation lockname.
- Val
- The variation valid flag. The following are possible values:
- Y
- Object is valid.
- N
- Object is invalid.
- X
- Object is inoperative.
- ?
- Object needs revalidation.
- Insert Time
- The time at which the variation was inserted into the package
cache.
- Sect Size
- The length of section data.
-edus
parameter
For the
-edus parameter,
the following information is returned:
- EDU ID
- The unique identifier for the engine dispatchable unit (EDU).
Except on Linux operating systems,
the EDU ID is mapped to the thread ID. On Linux operating system the EDU ID is a DB2 generated unique identifier.
- TID
- Thread identifier. Except on Linux operating
systems, the thread ID is the unique identifier for the specific thread.
On Linux operating systems,
this is a DB2 generated unique
identifier.
- Kernel TID
- A unique identifier for the operating system kernel thread in
service.
- EDU Name
- DB2 specific name for the
EDU.
- USR
- Total CPU user time consumed by the EDU.
- SYS
- Total CPU system time consumed by the EDU.
- USR Delta
- Indicates the delta of the CPU user time across a specified time
interval.
- SYS Delta
- Indicates the delta of the CPU system time across a specified
time interval.
See Sample output of the db2pd
-edus command.
-fcm parameter
For the
-fcm parameter,
the following information is returned:
- FCM Usage Statistics:
-
- Total Buffers
- Total number of buffers, including all free and in-use ones.
- Free Buffers
- Number of free buffers.
- Buffers LWM
- Lowest number of free buffers.
- Max Buffers
- Maximum number of buffers that can be allocated based on the amount
of virtual memory reserved when the instance was started.
- Total Channels
- Total number of channels, including all free and in-use ones.
- Free Channels
- Number of free channels.
- Channels LWM
- Lowest number of free channels.
- Max Channels
- Maximum number of channels that can be allocated based on the
amount of virtual memory reserved when the instance was started.
- Total Sessions
- Total number of sessions, including all free and in-use ones.
- Free Sessions
- Number of free sessions.
- Sessions LWM
- Lowest number of free sessions.
- Partition
- The database partition server number.
- Bufs Sent
- The total number of FCM buffers that are sent from the database
partition server where the db2pd command is running
to the database partition server that is identified in the output.
- Bufs Recv
- The total number of FCM buffers that are received by the database
partition server where the db2pd command is running
from the database partition server that is identified in the output.
- Status
- The logical connection status between the database partition server
where the db2pd command is running and the other
database partition servers that are listed in the output. The possible
values are:
- Inactive: The database
partition server is defined in the db2nodes.cfg file,
but is currently inactive (for example, the user has stopped the partition).
- Active: The database partition server
is active.
- Undefined: The database
partition server is not defined in the db2nodes.cfg file.
This might indicate an error.
- Unknown: The database partition server
is in an unknown state. This indicates an error.
- Buffers Current Consumption
-
- AppHandl
- The application handle, including the node and the index.
- TimeStamp
- A unique identifier for the usage of an application handle.
- Buffers In-use
- The number of buffers currently being used by an application.
- Channels Current Consumption
-
- AppHandl
- The application handle, including the node and the index.
- TimeStamp
- A unique identifier for the usage of an application handle.
- Channels In-use
- The number of channels currently being used by an application.
- Buffers Consumption HWM
-
- AppHandl
- The application handle, including the node and the index.
- TimeStamp
- A unique identifier for the usage of an application handle.
- Buffers Used
- The high-watermark number of buffers used by an application since
the start of the instance.
- Channels Consumption HWM
-
- AppHandl
- The application handle, including the node and the index.
- TimeStamp
- A unique identifier for the usage of an application handle.
- Channels Used
- The high-watermark number of channels used by an application since
the start of the instance.
-fmp parameter
For the
-fmp parameter,
the following information is returned:
- Pool Size: Current number of FMP processes
in the FMP pool.
- Max Pool Size: Maximum number of
FMP process in the FMP pool.
- Keep FMP: Value of keepfenced database
manager configuration parameter.
- Initialized: FMP is initialized.
Possible values are Yes and No.
- Trusted Path: Path of trusted procedures
- Fenced User: Fenced user ID
- FMP Process:
-
- FmpPid: Process ID of the FMP process.
- Bit: Bit mode. Values are 32 bit
or 64 bit.
- Flags: State flags for the FMP process.
Possible values are:
- 0x00000000 - JVM initialized
- 0x00000002 - Is threaded
- 0x00000004 - Used to run federated
wrappers
- 0x00000008 - Used for Health Monitor
- 0x00000010 - Marked for shutdown
and will not accept new tasks
- 0x00000020 - Marked for cleanup by db2sysc
- 0x00000040 - Marked for agent cleanup
- 0x00000100 - All ipcs for the process
have been removed
- 0x00000200 - .NET runtime initialized
- 0x00000400 - JVM initialized for
debugging
- 0x00000800 - Termination flag
- ActiveTh: Number of active threads
running in the FMP process.
- PooledTh: Number of pooled threads
held by the FMP process.
- Active: Active state of the FMP process.
Values are Yes or No.
- Active Threads:
-
- FmpPid: FMP process ID that owns
the active thread.
- EduPid: EDU process ID that this
thread is working.
- ThreadId: Active thread ID.
- Pooled Threads:
-
- FmpPid: FMP process ID that owns
the pooled thread.
- ThreadId: Pooled thread ID.
-fmpexechistory
| -fmpe parameter
For the
-fmpexechistory | -fmpe parameter,
the following information is returned:
- FMP Process:
-
- FmpPid - Process ID of the FMP process.
- Bit - Bit mode. Values are 32
bit or 64 bit.
- Flags - State flags for the FMP process.
Possible values are:
- 0x00000000 - JVM initialized
- 0x00000002 - Is threaded
- 0x00000004 - Used to run federated
wrappers
- 0x00000008 - Used for Health Monitor
- 0x00000010 - Marked for shutdown
and will not accept new tasks
- 0x00000020 - Marked for cleanup by db2sysc
- 0x00000040 - Marked for agent cleanup
- 0x00000100 - All ipcs for the process
have been removed
- 0x00000200 - .NET runtime initialized
- 0x00000400 - JVM initialized for
debugging
- 0x00000800 - Termination flag
- ActiveThrd - Number of active threads
running in the FMP process.
- PooledThrd - Number of pooled threads
held by the FMP process.
- ForcedThrd - Number of forced threads
generated by the FMP process.
- Active - Active state of the FMP
process. Values are Yes or No.
- Active Threads:
-
- EduPid - EDU process ID that this
thread is working.
- ThreadId - Active thread ID.
- RoutineID - The routine identifier.
- Timestamp - A unique identifier for
the usage of an application handle.
- Pooled Threads:
-
- ThreadId - Pooled thread ID.
- RoutineID - The routine identifier.
- Timestamp - A unique identifier for
the usage of an application handle.
- Forced Threads:
-
- ThreadId - Forced thread ID.
- RoutineID - The routine identifier.
- Timestamp - A unique identifier for
the usage of an application handle.
See Sample output of the db2pd
-fmpexechistory command.
-hadr
parameter
For the -hadr parameter,
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. This number is reset to zero when the database receives
a heartbeat again. 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_file monitor
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_file monitor
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.
-load
parameter
For the
-load parameter,
the following information is returned:
- LoadID
- The ID of a specific load operation.
- EDU ID
- The unique identifier for the engine dispatchable unit (EDU).
Except on Linux operating systems,
the EDU ID is mapped to the thread ID. On Linux operating system the EDU ID is a DB2 generated unique identifier.
- EDU Name
- DB2 specific name for the
EDU.
- TableName
- The name of the table.
- SchemaName
- The schema that qualifies the table name.
- AppHandl
- The application handle, including the node and the index.
- Application ID
- The application ID. This values is the same as the appl_id monitor
element data.
- StartTime
- The date and time when the load operation was originally invoked.
- LoadPhase
- The phase that the load operation is currently in.
-locks parameter
For the
-locks parameter,
the following information is returned:
- TranHdl
- The transaction handle that is requesting the lock.
- Lockname
- The name of the lock.
- Type
- The type of lock. The possible values are:
- Row (row lock)
- Pool (table space lock)
- Partition (data partition lock)
- Table (table lock)
- AlterTab (internal alter table lock)
- ObjectTab (internal object table
lock)
- OnlBackup (on line backup lock)
- DMS Seq (DMS sequence lock)
- Internal P (internal plan lock)
- Internal V (internal variation lock)
- Key Value (key value lock)
- No Lock (no lock held)
- Block Lock (MDC block lock)
- LF Release (long field lock)
- LFM File (long field lock)
- LOB/LF 4K (LOB / long field buddy
space lock)
- APM Seq (internal sequence lock)
- Tbsp Load (internal loading table
space lock)
- DJ UserMap (federated user mapping
lock)
- DF NickNm (federated nick name lock)
- CatCache (internal catalog lock)
- OnlReorg (on line reorg lock)
- Buf Pool (buffer pool lock)
- Mode
- The lock mode. The possible values are:
- IS (intent share)
- IX (intent exclusive)
- S (share)
- SIX (share with intention exclusive)
- X (exclusive)
- IN (intent none)
- Z (super exclusive)
- U (update)
- NS (scan share)
- NW (next key weak exclusive)
- Sts
- The lock status. The possible values are:
- G (granted)
- C (converting)
- W (waiting)
- Owner
- The transaction handle that owns the lock.
- 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.
- 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)
-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.7 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.
- 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:
- 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:
- IndexID
- The identifier of the index that is being used to reorganize the
table.
- TempSpaceID
- The table space during 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)
-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:
Additional
information that can help IBM® Support
to analyze and troubleshoot problems might also be returned.
For additional details about the returned information
using the -statisticscache command parameter,
see the topic "Catalog statistics tables".
-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:
- 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:
- 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:
- 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.
- 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:
- TrackmodState
- The modification status of a tablespace with respect to the last
or next backup. The possible values are:
- Clean - No modifications have occurred
in the tablespace since the previous backup. If an incremental or
delta backup is executed at this time, no data pages from this tablespace
would be backed up.
- Dirty - Table space contains data
that needs to be picked up by the next backup.
- InIncremental -
Table space contains modifications that were copied into an incremental
backup. This state is in a Dirty state
relative to a full backup such that a future incremental backup needs
to include some pages from this pool. This state is also in a Clean state
such that a future delta backup does not need to include any pages
from this pool.
- ReadFull - The latest table space
modification state change was caused by a dirty table space being
read by a full backup that might not have completed successfully,
or is currently in progress.
- ReadIncremental - The latest table
space modification state change was caused by a dirty table space
being read by an incremental backup that might not have completed
successfully, or is currently in progress.
- n/a - The trackmod configuration
parameter is set to No. Therefore, no table space modification status
information is available.
Table space Autoresize Statistics:
- Id
- The table space ID.
- AS
- Indicates whether or not the table space is using automatic storage.
The possible values are:
- AR
- Indicates whether or not the table space is enabled to be automatically
resized. The possible values are:
- InitSize
- For automatic storage table spaces, the value of this parameter
is the initial size of the table space in bytes.
- IncSize
- 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:
- 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:
Table space Containers:
- TspId
- The ID of the table space that owns the container.
- ContainNum
- The number assigned to the container in the table space.
- Type
- The type of container. The possible values are:
- Path
- Disk
- File
- Striped Disk
- Striped File
- TotalPgs
- The number of pages in the container.
- UsablePgs
- The number of usable pages in the container.
- StripeSet
- The stripe set where the container resides (applicable to DMS
table spaces only).
- Container
- The name of the container.
- PathID
- For automatic storage table spaces, the identifier of the storage
path on which the container resides.
See Sample
output of the db2pd -tablespaces command.
-tcbstats parameter
For
the
-tcbstats parameter, the following information
is returned:
- TCB Table Information:
-
- TbspaceID
- The table space identifier.
- TableID
- The table identifier.
- PartID
- For partitioned tables, this is the data partition identifier.
For non-partitioned table this will display 'n/a'.
- MasterTbs
- For partitioned tables, this is the logical table space identifier
to which the partitioned table belongs. For non-partitioned tables,
this value corresponds to the TbspaceID.
- MasterTab
- For partitioned tables, this is the logical table identifier of
the partitioned table. For non-partitioned tables, this value corresponds
to the TableID.
- TableName
- The name of the table.
- SchemaNm
- The schema that qualifies the table name.
- ObjClass
- The object class. The possible values are:
- Perm (permanent).
- Temp (temporary).
- DataSize
- The number of pages in the data object.
- LfSize
- The number of pages in the long field object.
- LobSize
- The number of pages in the large object.
- XMLSize
- The number of pages in the XML object.
- TCB Table Stats:
-
- TableName
- The name of the table.
- SchemaNm
- The schema that qualifies the table name.
- Scans
- The number of scans that have been performed against the table.
- UDI
- The number of update, delete, and insert operations
that have been performed against the table since the last time that
the table statistics were updated through the background statistics
collection process or manually using the RUNSTATS command.
- RTSUDI
- The number of update, delete, and insert operations that have
been performed against the table since the last time that the table
statistics were updated by real-time statistics gathering, background
statistics collection process, or manual RUNSTATS.
- PgReorgs
- The number of page reorganizations performed.
- NoChgUpdts
- The number of updates that did not change any columns in the table.
- Reads
- The number of rows read from the table when the table switch was
on for monitoring.
- FscrUpdates
- The number of updates to a free space control record.
- Inserts
- The number of insert operations performed on the table.
- Updates
- The number of update operations performed on the table.
- Deletes
- The number of delete operations performed on the table.
- OvFlReads
- The number of overflows read on the table when the table switch
was on for monitoring.
- OvFlCrtes
- The number of new overflows that were created.
- 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 : 0
All of the
counters can be reset to zero by using the
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.
- 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. This
includes the load operation ID and the application ID.
-wlocks parameter
For the
-wlocks parameter,
the following information is returned:
- ApplHandl
- The application handle, including the node and the index.
- TranHdl
- The transaction handle that is requesting the lock.
- LockName
- The name of the lock.
- Type
- The type of lock.
- Mode
- The lock mode. The possible values are:
- IS
- IX
- S
- SIX
- X
- IN
- Z
- U
- NS
- NW
- Conv
- The lock mode to which the lock will be converted after the lock
wait ends.
- Sts
- The lock status. The possible values are:
- G (granted)
- C (converting)
- W (waiting)
- CoorEDU
- The EDU ID of the coordinator agent for the application.
- AppName
- The name of the application.
- AuthID
- The authorization identifier.
- AppID
- The application ID. This values is the same as the appl_id monitor
element data.
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
- -cleaner
- The following is sample output of the -cleaner option:
db2pd -db sample -cleaner
Database Partition 0 - Database SAMPLE - Active - Up 0 days 00:06:34 -
Date 08/09/2010 14:17:58
Page Cleaners:
Group ID Clnr Idx State Cycle Task Pgs Gthr Pgs Ga'd IO outstnd Max AIO Pgs Thrsh Pgs D Stl Pgs Skppd
-1 0 Wait 0 None 0 0 0 32 0 0 0
-1 1 Wait 0 None 0 0 0 32 0 0 0
-1 2 Wait 0 None 0 0 0 32 0 0 0
-1 3 Wait 0 None 0 0 0 32 0 0 0
-1 4 Wait 0 None 0 0 0 32 0 0 0
...
Dirty lists for Bufferpool ID : 1
List ID # Dirty Clnr Idx Trigger Target LSN Pgs for Gap
0 4 0 None 0000000000000000 0
0 0 0 None 0000000000000000 0
1 8 1 None 0000000000000000 0
1 0 1 None 0000000000000000 0
2 2 2 None 0000000000000000 0
2 0 2 None 0000000000000000 0
3 1 3 None 0000000000000000 0
- -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
- -dirtypages
- The following is sample output of the -dirtypages option:
db2pd -db sample -dirtypages
Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:00:20 --
Date 08/09/2010 14:11:44
Bufferpool: 1
Dirty pages % : 34 / 1000 (3.40% dirty)
Bufferpool minbuflsn: 000000000138800C
Oldest page info:
DirtyLst TspID PPNum ObjID OPNum Typ UFlag fixcount wgt CPC LSN pgLtch
n/a 0 327 15 3 4 3 0 2 0 000000000138800C 0x07000000323508E8
Dirty pages:
DirtyLst TspID PPNum ObjID OPNum Typ UFlag fixcount wgt CPC LSN pgLtch
0 0 272 14 0 0 3 0 2 0 000000000138881C 0x070000003236C2E8 hX:0 sH:0 xW:0 rC:0
0 0 273 14 1 0 3 0 1 0 000000000138881C 0x070000003236B228 hX:0 sH:0 xW:0 rC:0
0 0 7541 18 9 1 3 0 2 0 000000000138E237 0x07000000323678A8 hX:0 sH:0 xW:0 rC:0
0 0 7540 18 8 1 3 0 2 0 000000000138E402 0x0700000032367A28 hX:0 sH:0 xW:0 rC:0
1 0 6945 15 5 1 3 0 2 0 0000000001388107 0x070000003236F3E8 hX:0 sH:0 xW:0 rC:0
1 0 300 14 4 1 3 0 2 0 000000000138889D 0x070000003236B6A8 hX:0 sH:0 xW:0 rC:0
...
Recovery information:
lowtranlsn : 000000000138E486
minbuflsn : 000000000138800C
nextlsn : 000000000138E4B0
LFH lowtranlsn : 000000000138E486
LFH minbuflsn : 000000000138800C
LFH nextlsn : 000000000138800C
Active Log bytes in use : 25764
Current Softmax : 4096000
DirtyLst - dirty list ID in this bufferpool
TspID - tablespace ID of this page
PPNum - pool page number
ObjID - object ID
OPNum - object page number
Typ - type of the object
UFlag - internal page flag
fixcount - number of active fixes on this page (in-use count)
wgt - weight of the page
CPC - clock
LSN - page LSN
pgLtch - page latch address, hX - held X, sH - # shard holders, xW - # of
X waiters
- -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 ;
- -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: 20
The following is a sample of the output of the
db2pd
-pages command specifying the
summary parameter:
venus@baryon:/home/venus =>db2pd -pages summary -db pdtest
Database Partition 0 -- Database PDTEST -- Active -- Up 0 days 00:02:07
Bufferpool Pages:
First Active Pool ID 1
Max Bufferpool ID 1
Max Bufferpool ID on Disk 1
Num Bufferpools 5
Total number of pages: 20
Summary info for all bufferpools:
BPID TbspaceID ObjID Total Dirty Permanent Temporary Data Index LongField XMLData SMP LOB LOBA BMP
1 0 5 4 0 3 0 1 2 0 0 0 1 0 0
1 0 10 15 7 14 0 3 12 0 0 0 0 0 0
1 2 4 1 1 1 0 1 0 0 0 0 0 0 0
Total number of pages: 20
- -reorgs index
- The following is an example of output obtained using the -reorgs
index parameter which reports the index reorg progress
for a range-partitioned table with 2 partitions.
Note: The first output
reports the Index Reorg Stats of the non-partitioned indexes. The
following outputs report the Index Reorg Stats of the partitioned
indexes on each partition; the index reorg statistics of only one
partition is reported in each output.
Index Reorg Stats:
Retrieval Time: 02/08/2010 23:04:21
TbspaceID: -6 TableID: -32768
Schema: ZORAN TableName: BIGRPT
Access: Allow none
Status: Completed
Start Time: 02/08/2010 23:03:55 End Time: 02/08/2010 23:04:04
Total Duration: 00:00:08
Prev Index Duration: -
Cur Index Start: -
Cur Index: 0 Max Index: 2 Index ID: 0
Cur Phase: 0 ( - ) Max Phase: 0
Cur Count: 0 Max Count: 0
Total Row Count: 750000
Retrieval Time: 02/08/2010 23:04:21
TbspaceID: 2 TableID: 5
Schema: ZORAN TableName: BIGRPT
PartitionID: 0 MaxPartition: 2
Access: Allow none
Status: Completed
Start Time: 02/08/2010 23:04:04 End Time: 02/08/2010 23:04:08
Total Duration: 00:00:04
Prev Index Duration: -
Cur Index Start: -
Cur Index: 0 Max Index: 2 Index ID: 0
Cur Phase: 0 ( - ) Max Phase: 0
Cur Count: 0 Max Count: 0
Total Row Count: 375000
Retrieval Time: 02/08/2010 23:04:21
TbspaceID: 2 TableID: 6
Schema: ZORAN TableName: BIGRPT
PartitionID: 1 MaxPartition: 2
Access: Allow none
Status: Completed
Start Time: 02/08/2010 23:04:08 End Time: 02/08/2010 23:04:12
Total Duration: 00:00:04
Prev Index Duration: -
Cur Index Start: -
Cur Index: 0 Max Index: 2 Index ID: 0
Cur Phase: 0 ( - ) Max Phase: 0
Cur Count: 0 Max Count: 0
Total Row Count: 375000
- -scansharing
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
- -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
Following is an example of output using the
-storagepaths parameter.
Database Storage Paths:
Number of Storage Paths 3
Address PathID PathState PathName
0x07000000400101C0 0 InUse /dataPath1
0x0700000040010540 1 DropPending /dataPath2
0x07000000400108C0 2 NotInUse /PathWithDPE_0 (/PathWithDPE_ $N)
- -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.LRG
A new 'Max HWM' column is added to the db2pd
-tablespaces output to indicate the maximum HWM for a DMS table space
since the instance was started. The 'HWM' column in the output
is the current HWM, which for a temporary DMS table space, represents
the point-in-time value of the amount of disk space used. For SMS
table spaces, the HWM and Max HWM will not have any value.
After a query has been issued, in-memory information
about the temporary tables used in the last transaction will be available
using
db2pd. The 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