System Monitor Guide and Reference

Snapshot Monitoring

The snapshot monitor provides two categories of information for each level being monitored:

• State

  This includes information such as:

  • the current status of the database
  • information on the current or most recent unit of work
  • the list of locks being held by an application
  • the status of an application
  • the current number of connections to a database
  • the most recent SQL statement performed by an application
  • run-time values for configurable system parameters.

• Counters

  These accumulate counts for activities from the time monitoring started until the time a snapshot is taken. Such as:

  • the number of deadlocks that have occurred
  • the number of transactions performed on a database
  • the amount of time an application has waited on locks.

For example, you can obtain a list of the locks held by applications connected to a database by taking a database lock snapshot. First, turn on the LOCK switch (UPDATE MONITOR SWITCHES), so that the time spent waiting for locks is collected.

Note:

You can create and populate the sample database by running sqllib/misc/db2sampl.

Issuing the GET SNAPSHOT command returns the following.

Figure 1. Results of GET SNAPSHOT FOR LOCKS Command

Database Lock Snapshot Database name = SAMPLE Database path = /home/bourbon/bourbon/NODE0000/SQL00005/ Input database alias = SAMPLE Locks held = 6 Applications currently connected = 1 Applications currently waiting on locks = 0 Snapshot timestamp = 04-11-1997 10:40:29.976539 Application handle = 1 Application ID = LOCAL.bourbon.970411143813 Sequence number = 0001 Application name = db2bp_32 Authorization ID = BOURBON Application status = UOW Waiting Status change time = Not Collected Application code page = 850 Locks held = 6 Total wait time (ms) = 0 Object Type Tablespace Name Table Schema Table Name Mode Status --------------- -------------------- -------------------- -------------------- ---- ---------- Row SYSCATSPACE SYSIBM SYSTABLES NS Granted Table SYSCATSPACE SYSIBM SYSTABLES IS Granted Table SYSCATSPACE SYSIBM SYSTABLESPACES S Granted Row SYSCATSPACE SYSIBM SYSPLAN S Granted Table SYSCATSPACE SYSIBM SYSPLAN IS Granted Internal S Granted

From this snapshot, you can see that there is currently one application connected to the SAMPLE database, and it is holding six locks.

     Locks held                                 = 6
     Applications currently connected           = 1

Note that the time (Status change time) when the Application status became UOW Waiting is returned as Not Collected, because the UOW switch is OFF.

The lock snapshot also returns the total time spent waiting for locks (so far), by applications connected to this database.

     Total wait time (ms)                       = 0

This is an example of an accumulating counter. "Resetting Monitor Data" explains how counters can be reset to zero.

Authority Required for Snapshot Monitoring

To perform any of the snapshot monitor tasks, you must have SYSMAINT, SYSCTRL, or SYSADM authority for the database manager instance that you wish to monitor.

Snapshot Monitor Interface

Snapshot monitoring is invoked using the following application programming interfaces (APIs):

sqlmon()

set or query monitor switch settings

sqlmonrset()

reset system monitor counters

sqlmonss()

take a snapshot

sqlmonsz()

estimate the size of the data that would be returned for a particular invocation of sqlmonss()

The Command Line Processor (CLP) provides a convenient command-based front-end to the snapshot APIs. For example, the GET SNAPSHOT command invokes the sqlmonss() API. Appendix A. "Database System Monitor Interfaces" contains detailed information on the commands and APIs associated with the database system monitor.

Figure 2. Snapshot Monitoring Interfaces

Information Available by Taking Snapshots

The following table lists all the supported snapshot request types. For certain request types, some information is returned only if the associated monitor switch is set ON. See Chapter 3. "Database System Monitor Data Elements" to determine if a required counter is under switch control.

In the table, the API Request type column identifies the value that is supplied as input to the SQLMA input structure in the sqlmonss() Snapshot API routine.

API request type	CLP command	Information returned
List of connections
SQLMA_APPLINFO_ALL	list applications [show detail]	Application identification information for all applications currently connected to a database that is managed by the DB2 instance on the node where snapshot is taken.
SQLMA_DBASE_APPLINFO	list applications for database dbname [show detail]	Same as SQLMA_APPLINFO_ALL for each application currently connected to the specified database.
SQLMA_DCS_APPLINFO_ALL	list dcs applications	Application identification information for all DCS applications currently connected to a database that is managed by the DB2 instance on the node where snapshot is taken.
Database manager snapshot
SQLMA_DB2	get snapshot for dbm	Database manager level information, including internal monitor switch settings.
	get dbm monitor switches	Returns internal monitor switch settings.
Database snapshot
SQLMA_DBASE	get snapshot for database on dbname	Database level information and counters for a database. Information is returned only if there is at least one application connected to the database.
SQLMA_DBASE_ALL	get snapshot for all databases	Same as SQLMA_DBASE for each database active on the node.
	list active databases	The number of connections to each active database. Includes databases that were started using the ACTIVATE DATABASE command, but have no connections.
Application snapshot
SQLMA_APPL	get snapshot for application applid appl-id	Application level information, includes cumulative counters, status information, and most recent SQL statement executed (if statement switch is set).
SQLMA_AGENT_ID	get snapshot for application agentid appl-handle	Same as SQLMA_APPL.
SQLMA_DBASE_APPLS	get snapshot for applications on dbname	Same as SQLMA_APPL, for each application that is connected to the database on the node.
SQLMA_APPL_ALL	get snapshot for all applications	Same as SQLMA_APPL, for each application that is active on the node.
Table snapshot
SQLMA_DBASE_TABLES	get snapshot for tables on dbname	Table activity information at the database and application level for each application connected to the database, and at the table level for each table that was accessed by an application connected to the database. Requires the table switch.
Lock snapshot
SQLMA_APPL_LOCKS	get snapshot for locks for application applid appl-id	List of locks held by the application. Also, lock wait information if any and the lock switch is ON.
SQLMA_APPL_LOCKS_AGENT_ID	get snapshot for locks for application agentid appl-handle	Same as SQLMA_APPL_LOCKS.
SQLMA_DBASE_LOCKS	get snapshot for locks on dbname	Lock information at the database level, and application level for each application connected to the database. Requires the lock switch.
Table space snapshot
SQLMA_DBASE_TABLESPACES	get snapshot for tablespace on dbname	Information about table space activity at the database level, the application level for each application connected to the database, and the table space level for each table space that has been accessed by an application connected to the database. Requires the buffer pool switch.
Buffer pool snapshot
SQLMA_BUFFERPOOLS_ALL	get snapshot for all bufferpools	Buffer pool activity counters. Requires the buffer pool switch.
SQLMA_DBASE_BUFFERPOOLS	get snapshot for bufferpools on dbname	Same as SQLMA_BUFFERPOOLS_ALL, but for specified database only.

Snapshot Uses an Instance Connection

You do not need to be connected to a database in order to use the snapshot APIs. They are performed under an instance connection, which is a connection between an application and an instance of the DB2 database manager.

The instance attachment is usually done implicitly to the instance specified by the DB2INSTANCE environment variable when the first database system monitor API is invoked by the application. It can also be done explicitly, using the ATTACH TO NODE command.

Once an application is attached, all system monitor requests that it invokes are directed to that instance. This allows a client to monitor a remote server, by simply attaching to the instance on it.

Availability of Snapshot Monitor Data

If all applications disconnect from a database, then the system monitor data for that database is no longer available. To obtain monitor information for all database activity during a given period you may want to use an event monitor. Alternatively, you can keep the database active until your final snapshot has been taken, either by starting it with the ACTIVATE DATABASE command, or by maintaining a permanent connection to the database.

[ DB2 List of Books | Search the DB2 Books ]