db2top

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 (Linux Power PC as of version 9.7 FP3). 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.

The links below will take you to descriptions of each of the command line arguments and each of the data reporting screens in db2top. As new articles are written describing more features of the product, such as the sorting, filtering, and column customization features, links to these articles will be added to this page. If you would like to read an overview of the db2top tool, Tao Wang and Shen Li have written a great db2top article on IBM DeveloperWorks.

Command Line Arguments

-d : Database name
-n : Node name
-u : User name
-p : User password
-V : Default explain schema
-i : Interval in seconds between snapshots
-b : background mode
-a : Monitor only active objects
-B : enable bold
-R : Reset snapshot at start up
-k : Display accumulated counters
-x : Extended display
-P : Partition snapshot (number or current)
-f : Replay monitoring session from snapshot data collector file
-C : Run db2top in snapshot data collector mode

Data Screens

Database Part 1
The first major screen of db2top that we will examine is the Database screen. This screen gives you the big picture view of your database. You can use this screen to see how busy your database is in terms of the number of applications connected to it and issuing work, the amounts of memory being consumed for various purposes, such as sorts or the caching of reads and writes, the efficiency of I/O, and the impacts of contention, such as high lock wait times and lock escalations.
Database Part 2
In the first post of this series on the db2top Database screen, we took a look at the gauges that make up the top part of the screen and saw the additional data about backups that becomes available when the screen widened to 141 columns or wider. In this second post, we will examine the four rows of database-level information presented below the gauges, including information on memory usage, active and idle connections, locks, logs, buffer pools, and sorts. You will also see the impact of the delta and cumulative modes on some of these data.
Sessions Part 1
Today we will take our first detailed look at the Sessions screen. The sessions screen lets you view applications connected to the database and see what they’re doing and how many resources they are consuming.
Sessions Part 2
In the previous post on the Sessions screen, we looked at the gauges and table columns that make up the default screen. In today’s post, we will examine all the extra information that becomes available on larger screens.
Sessions Part 3
In the first post on the Sessions screen, we examined the gauges and table columns that appear when the screen is 80 to 140 columns wide. In the second post, we saw what additional columns appear when you increase the width of the screen past 140 columns. In this final post on the Sessions screen, we will see the additional information that appears for large screens when extended mode is enabled.
Agent Part 1
To understand on a deep level how DB2 for LUW executes your SQL, you need to understand the concepts of agents, subagents, sections, and subsections. DB2, especially with the Data Partitioning Feature (DPF), takes advantage of parallelism, not only in running large numbers of SQL statements simultaneously, but also to execute efficiently the individual statements themselves. When a statement comes into DB2, it can be broken down into multiple subsections and a subagent can process each subsection in parallel with one another to quickly return a result. The db2top Agent screen gives you insight into this process, allowing you to see the state of each subagent and subsection as a statement is executed and helping you identify the source of performance problems such as the occurrence of a sort or hash join overflow. In today’s post, we will examine the Agent screen for non-DPF systems.
Agent Part 2
In part 2 of this series on the db2top Agent screen, you will see just how much more detailed the Agent screen becomes on DB2 systems that have the Data Partitioning Feature (DPF). Not only can you see the individual subsections of an executing statement, but also how they are processed at each partition. You also see the information rolled up to the subsection level, the statement level, and all the way up to the application itself. The subsection level information helps you to diagnose problems of skew between partitions, while the higher level aggregates help you identify problems of low sort heap allocation at the application level or heavy sort heap usage at the individual statement level.
Agent Part 3
In part 3 of this series on the db2top Agent screen, we will see statement-level and application-level information displayed on the Agent screen for DB2 systems that use the Data Partitioning Feature (DPF). Among other things, these higher level aggregates can help you identify problems of low sort heap allocation at the application level or heavy sort heap usage at the individual statement level. They also let you observe not only what kind of statement is being processed (dynamic, static, non-SQL) but also what operation is being performed on the statement, such as whether it is being compiled or executed.
Tablespaces Part 1
Databases are generally concerned with storing data on disk and retrieving it quickly, which makes monitoring the speed of your reads and writes essential. It is also important to know how fast your storage needs are growing, the makeup of your workload (writes versus reads), and whether data is being efficiently cached in memory. All of this information is available to you on the Tablespaces screen of db2top. In today’s post, we’ll take a look at some of the aggregates that db2top computes for you across all of your tablespaces. You’ll see how it’s possible to have a hit ratio of 90% while at the same time having an average hit ratio of only about 50%.
Tablespaces Part 2
In the first post in this series, we saw gauges and aggregates of table space information across all the table spaces in your database. In today’s post, you will see how db2top presents individual table spaces and their attributes. You will see ways to gauge the efficiency of your caching by looking at individual hit ratios, check your storage space usage, and identify data skew problems in systems that use the Data Partitioning Feature (DPF).
Bufferpools Part 1
Considering the dramatic differences in speed between memory access and disk access, it is not surprising that DB2’s own documentation claims that configuring buffer pools is the single most important tuning area for DB2. In most cases, using more than one buffer pool on your system can have performance advantages. In today’s post, you will see what information db2top provides to help you to make better decisions about how many buffer pools you need and how big they each need to be. We will examine how db2top lets you track database-wide hit ratios for your buffer pools, even separating them out into data, index, and temp hit ratios, and see whether your buffer pools are performing mostly reads versus writes or mostly data accesses versus index accesses or temp accesses.
Bufferpools Part 2
In the first post on db2top’s Bufferpools screen, we saw aggregates across all of the buffer pools in the database. In this second part, you will see what db2top reports for individual buffer pools.
Tables Part 1
Have you ever wanted to see a (near) real-time view of how much data is being read from or written to your tables? How about your temporary tables? The db2top Tables screen does all of this and more.
Tables Part 2
In the first post about the Tables screen, we examined the data that is available on small screens between 80 and 140 columns wide. In today’s post, you will learn about the gauge and aggregates that appear for larger screens. On such screens, you will be able to see the balance of your workload between reads and writes and the balance of memory consumption between temporary and permanent tables. You will also learn about another screen that can be launched directly from the Tables screen and shows top temporary storage consumers. You will see how much of the total each consumes and what applications and users are responsible for such memory consuming work.
Dynamic SQL
The first time I used db2top’s Dynamic SQL screen, I tried the ‘X’ key to switch to extended mode and had no idea what I had just done. Everything was reordered, hash values seemed to completely change, and the values in the Num Execution column shot upward. Eventually, I realized what was happening and I will share that with you in today’s post along with other details about the Dynamic SQL screen.
Locks
Today’s db2top screen is a very useful one for tracking down locking problems. You can use it to find lock waiters, lock holders and follow the chain from waiter to holder to diagnose the source of your long lock wait times.
Memory Part 1
A well-tuned DB2 for LUW system strikes a careful balance among the various consumers of resources such as CPU, disk, and memory. Applications, utilities, and internal database processes all compete for limited memory resources. The db2top tool can give you insight into your biggest memory consumers using the Memory screen, which we will examine in today’s post.
Memory Part 2
In the first post on the db2top Memory screen, we examined the gauges and table that are visible at all screen sizes. We saw that we could differentiate between the various memory pools on the system and determine how much memory each was consuming in near real-time. We also saw that we could measure the skew of memory pool usage among partitions in systems that use the Data Partitioning Feature. In today’s post, we will examine the aggregates that become available at large screen sizes. We can use these aggregates to see how much memory is being consumed by lock lists, the catalog cache, and sort heap (both shared and private) at both the instance and database levels.
Statements
Today’s topic is the Statements screen. Unlike the Dynamic SQL screen, the Statements screen lets you see both dynamic and static statements and it groups them by the applications in which they run. As with the Agent screen, you can use this screen to see the operation that a statement is in the middle of, such as a fetch on a cursor and you can also see the name of that cursor. If the statement is static SQL, you can also see the section being executed.
Partitions
Today’s post is about DB2 Data Partitioning Feature (available in Infosphere Warehouse) and how you can use db2top to monitor your partitions. In this post, you’ll see how to use the Partitions screen to watch for skew between partitions in real-time.
Utilities
A critical aspect of maintaining a database is getting the most current data possible into it. In data warehouses, this need is often met with extract-transform-load (ETL) technologies. DB2 for LUW addresses the loading aspect with a very sophisticated built-in load utility that makes it easy to load large amounts of data quickly into your database. Despite the load utility’s efficiency, large amounts of data generally translates into large amounts of time spent loading and it becomes valuable to be able to track the progress of such utilities as they execute. In today’s post, we’ll look at how the db2top Utilities screen makes it easy to monitor the progress of your utilities, including loads and backups.
HADR
HADR stands for High Availability Disaster Recovery. It is a technology that protects your database against data loss and allows it to be highly available even when you have a partial or complete site failure. It does this by replicating data changes from one database, called the primary, to another, called the standby. When your DB2 database is configured for HADR, db2top has some additional monitoring functionality specifically designed for such a configuration. Today we will look at the HADR screen in db2top.
Federation
Federation is a technology that allows you to build a heterogeneous set of database management systems into a single interface without requiring you to move all your data into one database. DB2 for LUW federation lets you query and perform transactions against Oracle, DB2 for z/OS, Microsoft SQL Server, or even another DB2 for LUW system. In today’s post, I will show you how to set up a simple federated system that connects to a DB2 for LUW data source and we will see how to monitor it with the db2top Federated screen.
Bottlenecks Part 1
A database workload performs at its best when resources are made available to it in all the right proportions. Having any one resource constrained too much and everything slows down. You can have more CPU cycles available than you’ll ever need, but if your IO subsystem can’t keep up with the work, it becomes a bottleneck and you’ll never achieve your desired throughput. In today’s post, we’ll take a look at the db2top Bottlenecks screen and see how it can help you find and resolve bottlenecks in your system by finding the top consumers of each of the major system resources.
Bottlenecks Part 2
The db2top Bottlenecks screen lets us see who the biggest consumers of each resource are and how much of the resource they are consuming. These top consumers can often cause such resources to become bottlenecked, so the Bottlenecks screen gives you an easy way to find the cause of such bottlenecks so that you can take action. Today, we will examine each of the individual resources for which the bottleneck screen reports the biggest consumer and how much it consumes.
Skew
You can observe data access skew by looking at how the data actually gets used, that is, to see if more data is being read from one partition than the others by the queries being run by the users of the database. In today’s post, we will investigate the Skew screen of db2top and learn what it can tell you about data access skew in your database.

Features

Watch
The db2top tool is not just a tool for letting you observe a running system – it also lets you record activity for later analysis. One way to record data for later is the db2top Watch feature, which lets you precisely specify which SQL statements you want to collect. With this feature, for example, you could watch only those SQL statements coming from a particular application or user.
Filters
On a busy system, sometimes the amount of data returned by our monitoring tools can be overwhelming. Fortunately, db2top was designed with this in mind and has a very flexible and powerful regular expressions feature for extracting the information critical to your decision-making from reams of monitoring data.
Sort
The db2top tool is all about showing the top sessions, bufferpools, tablespaces, etc. by some criteria. It wouldn’t be very useful if it didn’t let you choose these criteria. Fortunately, db2top lets you quickly base the order in which the data is presented on the criteria of your choice.
Change Columns Order
In today’s post, we will look at how you can customize the order in which columns are presented for the majority of those screens in db2top that present their information in a table.
Native Snapshot
The db2top tool, on each of its various screens, presents a subset of the full capabilities of the DB2 snapshot monitor. In this post, we’ll see how you can use the native snapshot feature to gain access to the full snapshot.
Display Settings
When a monitoring or reporting tool reaches a certain level of sophistication, it needs to be able to report on itself as well. The db2top tool has a large number of configurable options, so it is fortunate that there is a screen that lets us see the present value of each such option. In today’s post, we will look at the Display Settings screen.
Performance Report
Up to now, we have been looking mostly at db2top as a tool for observing the behavior of your system in real-time, but background mode and recording and playback has shown us that db2top can produce and examine historical data as well. The db2top tool can also be used to perform an analysis on such historical data and present a performance report. This ‘analysis’ feature is the topic of today’s post.

Videos

How to change column order and preserve it in the next session
A video to demonstrate how to change the column order and keep it changed for the next time you launch db2top.
The Watch feature
For the second video in this video series, we have a demonstration of the db2top Watch feature. It demonstrates capturing a single application’s SQL, capturing complete database structures to a set of files, and capturing all the dynamic SQL in the cache.
Regular expressions
A demonstration of the regular expressions (regex) feature of db2top. In the video, I apply regular expressions to the Dynamic SQL screen to filter what SQL statements are shown. I demonstrate setting and clearing a regex, simple regular expressions, and POSIX extended regular expression metacharacters.
The Locks screen
Today’s video is a demonstration of the Locks screen of db2top. In the video, I create a lock chain and launch db2top to examine the Application Status, Object Name, Locked By and Lock Count columns for three applications involved in the chain. I then use the Lock Chain screen to visualize the chain.

Miscellaneous

.db2toprc Configuration File
The db2top tool is very configurable. It would be inconvenient to configure it to your liking only to lose your configuration when you shut down the tool, so an option is available for saving your configuration to a file. Today we will examine this configuration file and look at what kinds of tweaks you can make.
Gauges
Gauges in db2top are graphical representations of proportions. Gauges appear on many of db2top’s screens, often grouped together.
Do db2top High Watermarks Give Wrong Answers?
There is a gauge on the Memory screen called Memory hwm% and computing it involves aggregating high watermarks. This is a problematic calculation and it is only one of many such calculations in the product.