DB2® Monitoring

DB2 includes many facilities for tracing system activity at just about any level of detail.

This section covers the following topics:

Snapshot Monitors
Snapshot Monitor Commands
DB2 Event Monitors
Use an Event Monitor to Diagnose and Flush Deadlocks
DB2 Performance Expert

Snapshot Monitors

Information about snapshot monitors is available by searching at http://www.ibm.com/developerworks/.

Snapshot monitors collect information about the state of a DB2 instance and any databases it controls at a specific point in time. Snapshots are useful for determining the status of a database system. When taken at regular intervals, they can provide information that lets you observe trends and identify potential problem areas. Snapshot monitoring is performed by executing the GET SNAPSHOT command from the DB2 Command-Line Processor (CLP).

Snapshot Monitor Commands

To use snapshot monitors, turn on the monitor switches and view data. The following table describes the command and syntax for specific tasks:

Task	Command and Syntax
Turn on monitor switches	`db2 UPDATE MONITOR SWITCHES USING options` Options are: `lock on` `sort on` `statement on` `table on` `timestamp on` `uow on` Note: Monitor switches can also be controlled in the database manager configuration.
Check status of monitor switches	`GET MONITOR SWITCHES`
View snapshot data	Switches must be turned on to view data. `GET SNAPSHOT FOR options` Options: `bufferpools on database_name` `locks on database_name` `dynamic sql on database_name` `tables on database_name` `applications on database_name` `tablespaces on database_name` `database on database_name`

Task

Command and Syntax

Turn on monitor switches

db2 UPDATE MONITOR SWITCHES USING options

Options are:

lock on

sort on

statement on

table on

timestamp on

uow on

Note: Monitor switches can also be controlled in the database manager configuration.

Check status of monitor switches

GET MONITOR SWITCHES

View snapshot data

Switches must be turned on to view data.

GET SNAPSHOT FOR options

Options:

bufferpools on database_name

locks on database_name

dynamic sql on database_name

tables on database_name

applications on database_name

tablespaces on database_name

database on database_name

DB2 Event Monitors

Information about snapshot monitors is available by searching at http://www.ibm.com/developerworks/.

Event monitors are used to monitor events in an area over time, such as by producing event records at the start and end of multiple events. These records are useful for resource planning, trend analysis, and so on.

The most common uses for event monitors are for connections, locks, and statements. Output can be written to files, named pipes (useful if writing programs to retrieve monitor data), or to tables.

Event monitor output can be either blocked or nonblocked. Blocked output ensures against data loss, but should generally be avoided for systems that produce a large volume of records and for statement event monitors to avoid crashes.

You can limit the granularity of an event monitor to a single user or application.

Connection events can be used to track system usage on a user basis or application basis. This data allows you to identify poorly performing programs, the heaviest users, and usage trends. Daily review facilitates discussions with users about the activity or about adjusting the DB2 physical design.

Use an Event Monitor to Diagnose and Flush Deadlocks

Sterling B2B Integrator, along with the necessary DB2 registry variables, is designed to minimize the occurrence of deadlocks. However, deadlocks may still occur in some situations, including the following:

Custom BP code may obtain records in a different order.
DB2 may choose an access plan that retrieves records in a different order.

The following table describes the actions to be performed for specific tasks:

Task	What to Do
Set an event monitor to help diagnose deadlocks	Run this command: `db2 -v create event monitor $MON for deadlocks with details write to file $OUTDIR buffersize 64 nonblocked` `db2 -v set event monitor $MON state = 1` Options: `MON` – Monitor name (for example, `DLMON)` `OUTDIR` – directory to store deadlock information
Determine that a deadlock has occurred	View data in the db2diag.log or in the Sterling B2B Integrator logs
Release a deadlock by flushing buffers	Run the following command: `db2 flush event monitor $MON` `db2evmon -path $OUTDIR` The flush ensures that the deadlock records in the buffers are written out. The `db2evmon` command formats the deadlock information.

Task

What to Do

Set an event monitor to help diagnose deadlocks

Run this command:

db2 -v create event monitor $MON for deadlocks with details write to file $OUTDIR buffersize 64 nonblocked

db2 -v set event monitor $MON state = 1

Options:

MON – Monitor name (for example, DLMON)

OUTDIR – directory to store deadlock information

Determine that a deadlock has occurred

View data in the db2diag.log or in the Sterling B2B Integrator logs

Release a deadlock by flushing buffers

Run the following command:

db2 flush event monitor $MON

db2evmon -path $OUTDIR

The flush ensures that the deadlock records in the buffers are written out. The db2evmon command formats the deadlock information.

Optim Performance Manager Extended Edition

Optim Performance Manager is a performance analysis and tuning tool for managing a mix of DB2 systems. It can be used to identify, diagnose, solve, and prevent performance problems.

For more information about the Optim Performance Manager Extended Edition, refer to:https://www-01.ibm.com/common/ssi/cgi-bin/ssialias?htmlfid=897/ENUS5724-Z20&infotype=OC&subtype=NA&appname=skmwww#description.