The snapshot monitor provides two categories of information for each level being monitored:
This includes information such as:
These accumulate counts for activities from the time monitoring started until the time a snapshot is taken. Such as:
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.
Database Lock Snapshot Database name = SAMPLE Database path = /home/bourbon/bourbon/NODE... Input database alias = SAMPLE Locks held = 5 Applications currently connected = 1 Applications currently waiting on locks = 0 Snapshot timestamp = 03-17-1999 15:40:29.976539 Application handle = 0 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 = 5 Total wait time (ms) = 0 List of Locks Lock Object Name = 4 Object Type = Row Tablespace Name = SYSCATSPACE Granted Table Schema = SYSIBM Table Name = SYSTABLES Mode = NS Status = Granted Lock Escalation = NO Lock Object Name = 2 Object Type = Table Tablespace Name = SYSCATSPACE Granted Table Schema = SYSIBM Table Name = SYSTABLES Mode = IS Status = Granted Lock Escalation = NO Lock Object Name = 259 Object Type = Row Tablespace Name = SYSCATSPACE Granted Table Schema = SYSIBM Table Name = SYSTABLES Mode = NS Status = Granted Lock Escalation = NO Lock Object Name = 7 Object Type = Table Tablespace Name = SYSCATSPACE Granted Table Schema = SYSIBM Table Name = SYSTABLES Mode = IS Status = Granted Lock Escalation = NO Lock Object Name = 0 Object Type = Internal P Lock Tablespace Name = Table Schema = Table Name = Mode = S Status = Granted Lock Escalation = NO
From this snapshot, you can see that there is currently one application connected to the SAMPLE database, and it is holding five locks.
Locks held = 5
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.
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 monitoring is invoked using the following application programming interfaces (APIs):
The Command Line Processor (CLP) provides a convenient command-based front-end to the snapshot APIs. For example, the GET SNAPSHOT command invokes the db2GetSnapshot() API.
| Note: | Starting in Version 6, the sqlmonss() - Get Snapshot API is replaced by the db2GetSnapshot() API. |
Appendix A. Database System Monitor Interfaces contains detailed information on the commands and APIs associated with the database system monitor.
Figure 1. Snapshot Monitoring Interfaces
 |
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 db2GetSnapshot()
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. | |
| SQLMA_DCS_DBASE | get snapshot for dcs database on dbname | Database level information and counters for a specific DCS database. Information is returned only if there is at least one application connected to the database. |
| SQLMA_DCS_DBASE_ALL | get snapshot for all databases | Same as SQLMA_DCS_DBASE for each database active on the node. |
| 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. |
| SQLMA_DCS_APPL | get snapshot for dcs application applid appl-id | Application level information, includes cumulative counters, status information, and most recent SQL statement executed (if statement switch is set). |
| SQLMA_DCS_APPL_ALL | get snapshot for all dcs applications | Same as SQLMA_DCS_APPL, for each DCS application that is active on the node. |
| SQLMA_DCS_APPL_HANDLE | get snapshot for dcs application agentid appl-handle | Same as SQLMA_DCS_APPL. |
| SQLMA_DCS__DBASE_APPLS | get snapshot for dcs applications on dbname | Same as SQLMA_DCS_APPL, for each DCS application that is connected to the database 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. |
| Dynamic SQL snapshot | ||
| SQLMA_DYNAMIC_SQL | get snapshot for dynamic sql on dbname | Point-in-time statement information from the SQL statement cache for the database. |
Snapshot monitoring requires an instance attachment or a database connection. (An instance attachment is a connection between an application and an instance of the DB2 database manager.)
If there is neither an attachment to an instance, nor a connection to a database, a default instance attachment is created. 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 command.
If there is both an instance attachment and a database connection, the instance attachment is used.
Once an application is attached or connected, 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, or connecting to one of the databases on it.
The DB2 statement cache stores packages and statistics for frequently used SQL statements. You can examine SQL activity by taking a snapshot of this cache. Due to the volume of records that can be returned from such a snapshot, a table function exists to view its contents (see SQLCACHE_SNAPSHOT).
A snapshot of the statement cache can only be taken over a database connection:
If write to file is attempted over an instance attachment, the request will be rejected.
If all applications disconnect from a database and the database deactivates, 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.