dmctop

Key features

The dmctop utility 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_xxx table functions. Views can be set to fast refresh rates to provide a real-time view of activity in the monitored database.

You can use dmctop to see key performance indicators in the same way that the db2top command worked. The dmctop utility provides:

Sessions

See at a glance which connections are active, blocked, or idle. Drill down on a connection to see details of the current state of execution of SQL that is in question.

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 broken down to the full granularity provided by Db2.

pureScale® performance

See Db2 pureScale performance metrics and cache facility state.

HADR status

See primary and standby database HADR configuration state.

Federation

View federated activity for the federated database.

Comparison of db2top and dsmtop

Dependency

The dmctop utility has dependency on Db2 CLI driver.

1. Download the IBM® Data Server Driver for ODBC and CLI.
   1. Go to the IBM Support Fix Central website:
   2. Select Information Management from the Product Group drop down box.
   3. Select the IBM Data Server Client Packages from the Select from Information Management drop-down box.
   4. Select the IBM Data Server Driver for ODBC and CLI product version that you require from the Installed Version drop-down box.
   5. Select the platform from the Platform drop-down box then click Continue.

      A list of all client and driver packages available for your platform, including IBM Data Server Driver for ODBC and CLI will be displayed.

2. Install the driver manually:
   1. Copy the compressed file that contains the driver to the target system.
   2. Select an install directory in the target system and uncompress the file.
   3. Optional: Remove the compressed file.
   4. Setup an environment variable
      For Linux:

      LD_LIBRARY_PATH=<DRIVER_INSTALL_PATH>/lib

      For Windows:

      PATH=%PATH%;<DRIVER_INSTALL_PATH>\bin

Downloading dmctop

You can download dmctop from IBM Support Fix Central web site: .

Run dmctop from Db2 server

Run dmctop remotely

The easiest way to run dmctop is to fully specify the database and user credentials. The dmctop utility will use the CLI driver.

dmctop -d <database_name> -n <host_name> -r <port_number> -u <user_id> [-cert-path Root-CA certificate absolute path]

dmctop.exe -d <database_name> -n <host_name> -r <port_number> -u <user_id> [-cert-path Root-CA certificate absolute path]

For example:

dmctop -d SAMPLE -n localhost -r 50000 -u db2inst1

dmctop.exe -d SAMPLE -n localhost -r 50000 -u db2inst1

For example:

dmctop -d SAMPLE -n localhost -r 50000 -u db2inst1 -cert-path \home\db2inst1\ssl\inernal-CA.arm

Windows: dmctop.exe -d SAMPLE -n localhost -r 50000 -u db2inst1 -cert-path C:/ssl/inernal-CA.arm

If SSL is not configured for databases then -insecure option can be used to disable default secure connection.

For example:

dmctop -d SAMPLE -n localhost -r 50000 -u db2inst1 -insecure

dmctop.exe -d SAMPLE -n localhost -r 50000 -u db2inst1 -insecure

dmctop 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.

For example:

dmctop -d SAMPLE -n localhost -r 50000 -u db2inst1 -p <password>

dmctop.exe -d SAMPLE -n localhost -r 50000 -u db2inst1 -p <password>

Run dmctop in background mode

When using the -b parameter, dmctop saves information to file in CSV format. Issuing multiple sub-options for background mode (-b) is not supported.

The -b parameter takes one of the following single character sub-option values

d - overview

l - connections

a - top consumer activities

J - Skew monitor

s - in-flight statements

D - Package cache

L - Lock statistics

m - DB memory

I - Instance memory

F - Federation

A - HADR

For example:

Using default file name

dmctop -d SAMPLE -n localhost -r 50000 -u db2inst1 -insecure -b d

dmctop.exe -d SAMPLE -n localhost -r 50000 -u db2inst1 -insecure -b d

Specifying output file name

dmctop -d SAMPLE -n localhost -r 50000 -u db2inst1 -insecure -b d -f <outputFile>.csv

dmctop.exe -d SAMPLE -n localhost -r 50000 -u db2inst1 -insecure -b d -f <outputFile>.csv

Authorization

User ID used with dmctop to monitor database requires the following privileges,. For detailed privilege information, refer to the README file included in the package.