The db2top command provides a unified, single-system view of a multi-partition database or single-partition database on the AIX®, Linux, HP-UX, and Solaris operating systems. Quickly identifies global problems, or specific database partition problems in the system. By combining snapshot information from each database partition, a dynamic real-time view of a running DB2® system is provided.
db2top can be run in interactive mode or in batch mode.
Instance. A connection is not required in replay or client mode.
>>-db2top--+- -A----------------------------+------------------>< +- -a----------------------------+ +- -B----------------------------+ +- -b -suboption-----------------+ +- -C--suboption-----------------+ +- -D--delimiter-----------------+ +- -d--database name-------------+ +- -f--file </HH:MM:SS><+offset>-+ +- -h----------------------------+ +- -i--interval in seconds-------+ +- -k----------------------------+ +- -L----------------------------+ +- -m--duration in minutes-------+ +- -n--node name-----------------+ +- -o--outfile-------------------+ +- -P--dbpartition number--------+ +- -p--password------------------+ +- -R----------------------------+ +- -s--number--------------------+ +- -u--username------------------+ +- -V--schema--------------------+ '- -x----------------------------'
Running db2top in collection mode for a long period (for example, 4 hours):
db2top -f collect.file -C -m 240
Running db2top in replay mode with automatic performance analysis. Automatic performance analysis is available for any functions supported in background mode and is based on the default sort criteria specified in the .db2toprc configuration file for the selected suboption (automatic performance analysis is also available in background mode, even when not replaying). As an example, to analyze the most active sessions, issue the following command:
db2top -f collect.file -b l -A
Running db2top in replay mode, jumping to the desired point in time to analyze further:
db2top -f collect.file /HH:MM:SS
The following parameters can only be used with the -b parameter: -X, -L, -A, -s, -D, -o.
If the -f parameter is not specified, db2top is run in replay mode. If the -f parameter is specified, it indicates output filename. The database name specified with the -d parameter does not need to exist. It is only referenced if you want to issue explains or Dump DB Struct.
In replay mode (-C parameter is not used), if snapshot data has been previously collected in file, offset jumps to a certain point in time in the file. It can be expressed in seconds (+10s), minutes (+10m) or hours (+10h). /HH:MM:SS skips entries until the specified point in time.
In the collector mode (-C parameter is used), you can specify the output filename. </HH:MM:SS><+offset> will be ignored if you are in collector mode
Specifies that statements that are currently executing or most recent statements from current sessions are captured at each reporting interval specified by the -i parameter. If an SQL statement starts and finishes between an interval, it will not be included.
In the background mode, use -b l -L parameters to catch SQL statements executed in a session and output them to the ALL.sql file in the current working directory.
Currently executing or the most recent SQL statement for a connected session are captured at each interval (as specified by -i parameter). If a SQL statement starts and finishes between an interval, it will not be caught.
The db2top monitoring utility can be run in replay mode, which means it can run against a saved copy of the raw binary snapshot data. To run in replay mode, db2top must be first run in data collector mode, either in batch mode by running db2top from the command line with the -C parameter, or by activating or deactivating data collection from an interactive session by pressing C. This will create a file <db2snap-hostname.bin> in the current directory. Then, the db2top utility can be run against <db2snap-<dbname>-<Machine><bits>.bin> using the -f arguments. The db2top monitoring utility does not need to attach to the DB2 instance in replay mode, which is convenient for remote monitoring. It is possible to limit the content and size of the stream file by specifying any of the sub-options available to the -C parameter.
The command parameters are as below:
-b l --> Run in background mode, while catching the "sessions" related
info in db2top
-C --> run db2top in snapshot collector mode
-d CUST --> database
-i 3 --> 3 second interval
-m 60 --> limit duration of db2top to 60 minutes
-n node --> node
-o db2top.xml --> output file db2top.xml
-p password --> password
-s 10000 --> collect 10000 samples
-u userid --> userid
-V sv9 --> Schema
-x --> display additional counters on session
db2top -d CUST -n node -u userid -p password -V sv9 -i 3 -b l -X -o db2top.xml -m 60
db2top -d CUST -n node -u userid -p password -V sv9 -b l -x -s 10000 -X -f db2snap-CUST-AIX64.bin
db2top -d CUST -f db2snap-CUST-AIX64.bin
For more information, refer to the DB2 problem determination using db2top utility at http://www.ibm.com/developerworks/data/library/techarticle/dm-0812wang/