db2top - DB2 monitoring tool command

db2top utility - DB2 problem determination (Optimize performance and prevent problems in complex DB2 LUW environments)

Introduction

There are several methods to collect information and diagnose DB2 system performance issues. The snapshot monitor is one of the most commonly used tools to collect information in order to narrow down a problem. However, most entries in snapshots are cumulative values and show the condition of the system at a point in time. As a continuous improvement and enhancement in technology, DB2 introduced an utility named db2top.

The db2top tool comes with DB2, and can be used to calculate the delta values for those snapshot entries in real time. This tool provides a GUI under a command line mode, so that users can get a better understanding while reading each entry. This tool also integrates multiple types of DB2 snapshots, categorizes them, and presents them in different screens for the GUI environment.

The db2top tool is a monitoring tool that comes packaged with every copy of DB2 for Linux and Unix as of version 9.1 FP6, version 9.5 FP2, and version 9.7. If you have any one of these versions of DB2, there is nothing you need to download to get access to db2top. The db2top tool is not available for Windows. The db2top tool provides functionality for DB2 that is similar to what the UNIX top command provides for the UNIX operating system.

Abstract

db2top command Syntax :

Usage:  db2top [-d dbname] [-n nodename] [-u username] [-p password] [-V schema]

               [-i interval] [-P [part]] [-a] [-B] [-R] [-k] [-x]

               [-f file [+time] [/HH:MM:SS]] 

               [-b options [-s [sample]] [-D separator] [-X] -o outfile]

               [-C] [-m duration]

        db2top -h

        -d : Database name (default DB2DBDFT)

        -n : Node name

        -u : User name

        -p : User password

        -V : Default explain schema

        -i : Interval in seconds between snapshots

        -b : background mode

             option: d=database, l=sessions, t=tablespaces, b=bufferpools, T=tables,

                     D=Dynamic SQL, s=Statements, U=Locks, u=Utilities, F=Federation,

                     m=Memory -X=XML Output, -L=Write queries to ALL.sql,

                     -A=Performance analysis

        -o : output file for background mode

        -a : Monitor only active objects

        -B : enable bold

        -R : Reset snapshot at startup

        -k : Display cumulated counters

        -x : Extended display

        -P : Partition snapshot (number or current)

        -f : Replay monitoring session from snapshot data collector file,

             can skip entries when +seconds is specified

        -D : Delimiter for -b option

        -C : Run db2top in snapshot data collector mode

        -m : Max duration in minutes for -b and -C

        -s : Max # of samples for -b

        -h : this help

Parameters can be set in $HOME/.db2toprc, type w in db2top to generate the resource

configuration file.

How to start db2top?

db2top can be run in two modes, interactive mode or batch mode. In interactive mode, the user enters command directly at the terminal text user interface and waits for the system to respond. Note that the left and right arrow keys on the keyboard can be used to scroll columns to left or right, so that you can see the hidden columns on many screens in interactive mode. On the other hand, in batch mode a series of jobs are executed without user interaction.

Run db2top in interactive mode

Enter the following command from a command line to start db2top in interactive mode:

        db2top –d databasename

Run db2top in batch mode

You can use db2top in batch mode to monitor a running database unattended. Users can record performance information using db2top in the background and the historical data is stored for further analysis.

The following code listing shows how you would run db2top in collection mode for a long period (for example, eight hours in total, and a 10 seconds interval between each snapshot):

db2top -d databasename -f collect.file -C -m 480 -i 10

Sample script for batch mode/back ground mode :

#/usr/bin/sh

# This scripts generates data for the db2top excel macro

# It will create a directory called /tmp/background

# Copy this directory under windows

# Copy the db2top excel macro in the directory

# Open the excel document and run full report

#

#

export WAITTIME=${1-5} # in minutes

export DATABASE=${2} # in case there is a collection file

export DIR=/tmp/background

#

# Run db2top and exit after WAITTIME minutes

#

BACK_GROUND() {

db2top -d $DATABASE -m $WAITTIME -b $1 -o ${DIR}/db2top-$DATABASE-${2}.txt &

}

#

# Start here

#

rm -fR $DIR

mkdir -p $DIR

echo "Running db2top for $WAITTIME minute(s)"

BACK_GROUND d DBM

BACK_GROUND l APPL

BACK_GROUND t TBS

BACK_GROUND b BP

BACK_GROUND T TABLES

BACK_GROUND m MEMORY

BACK_GROUND s STATEMENTS

BACK_GROUND D DYNAMIC_SQL

BACK_GROUND U LOCKS

wait

echo "Done..."

What can be monitored by db2top?

Database (d)

Tablespace (t)

Dynamic SQL (D)

Session (l)

Bufferpool (b)

Lock (U)

Table (T)

Bottlenecks (B)

Memory (m)

Utilities (u)

Federation (F)

HADR (A)

Details of agent (a) <agentid>

Statements (s)

Partitions (p)

Run Native Snapshot (S)

Conclusion

db2top is basically a tool to periodically capture snapshots and allow users to read the result visually instead of parsing snapshot files. Much more performance monitoring can be done with this tool. Lot more options are available with filters which makes this tool special and useful for a DBA and makes easier for finding and narrow down the problem and find a solution/strategy in database.