dsmtop - Db2 text-based monitoring tool command

The dsmtop command is a simple text-based tool for monitoring, similar to the now deprecated db2top command. The dsmtop command can monitor Db2 Version 10.1 and above. It is intended primarily for use on Linux and AIX®, but will also run on, Windows although with some limitations.

Scope

The dsmtop command is a lightweight, low-overhead tool that works in a text-only environment, such as a simple Unix command line. There is no web server and no need for a windowing environment. Monitoring is accomplished by using mon_get table functions.

Command features

You can use the dsmtop command to see key performance indicators in the same way that the db2top command worked. The dsmtop command provide

Sessions
See at a glance which connections are active, blocked, or idle.
Running SQL
See a list of recently run statements. Drill down is provided to see the full SQL text or run explain on a statement.
Top Consumers
Find which connections or activities are consuming the most CPU, IO or other resource.
Time spent
Shows a breakdown of where the monitored database is spending time.

Run dsmtop

The easiest way to run dsmtop is to fully specify the database and user credentials. The dsmtop command will use the JDBC type 4 driver. Use the following method as all necessary libraries are bundled in the distribution.
dsmtop  -d <database_name>  -n <host_name>  -r <port_number>  -u <user_id>

For example:

dsmtop  -d gsdb  -n localhost  -r 50000  -u kdeck
The dsmtop command requires a password. You can specify the password on the command line with -p. However, this is not recommended as the password will then be visible to other users running similar commands. Once you are connected, you can save the credentials using the following menu choice:
File -> Write

Credentials are written to a file called dsmtoprc which is located in $HOME for Linux or UNIX systems, or %UserProfile% for Windows. Further uses of the dsmtop command will read from the dsmtoprc file to fill in default values for database, host, port, user ID and password (encrypted).

Run dsmtop without re-authenticating

The dsmtop command can connect to a local database without specifying your user ID or password, if you are currently authenticated already. To connect this way, the dsmtop command uses a JDBC type 2 driver. There are some requirements before you can connect in this way:
  • The JDBC type 2 driver requires Db2 Server or Db2 Client products installed.
  • For UNIX, Linux or AIX machines, you need to setup some environment variables such as LD_LIBRARY_PATH, LIBPATH or SHLIB_PATH to load the JDBC native library.
In a typical Db2 install on Linux or AIX, there are files provided that you can source to set the environment:
<INSTALL_PATH>/sqllib/db2profile
or
<INSTALL_PATH>/sqllib/db2cshrc

If these files are missing, refer to this page for information about how to generate them: Linux and UNIX environment variable settings

The dsmtop command will use the JDBC type 2 driver if you do not specify a user ID or a password, either through the command line or from the dsmtoprc file. You can also specify -j 2 on the command line to explicitly use the type 2 driver.

If you are using the JDBC type 2 driver and encounter an error similar to the following:
The DDM parameter is not supported. 
Unsupported DDM parameter code point: 0x2460. 
ERRORCODE=-4499, SQLSTATE=58016

The most likely cause is a known problem that can happen if the JDBC type 2 native library in your environment is newer than the db2jcc.jar file shipped with the dsmtop command. To resolve this issue, you should replace the db2jcc.jar from the dsmtop product with the db2jcc.jar from the Db2 Server or Db2 Client that is installed in the same machine. For example, if you have the Db2 product installed in the /home/sqllib directory, you should replace <your_base_path>/dsmtop/lib/db2jcc.jar with the /home/sqllib/java/db2jcc.jar

For more information on the dsmtop command, refer to: Text Based Monitoring for Db2 LUW - dsmtop