Section 16.7. The Snapshot Monitor

16.7. The Snapshot Monitor

To ensure that your system is performing optimally and to examine any issues that may arise, you normally will need to take DB2 snapshots. DB2 snapshots are like taking an X-ray of the various performance indicators within the DB2 engine, and just like doctors examine X-rays, you will examine the snapshot information.

Before capturing a snapshot, first determine what data you need the database manager to gather. Table 16.4 lists the information provided by the Snapshot Monitor, the monitor switch name, and the DBM parameter. If you want any of the following special types of data to be collected, set the appropriate monitor switches.

Table 16.4. Data Returned by the Snapshot Monitor

Group	Information Provided	Monitor Switch	DBM Parameter
Sorts	Number of heaps used, overflows, sorts performance	SORT	DFT_MON_SORT
Locks	Number of locks held, number of deadlocks	LOCK	DFT_MON_LOCK
Tables	Measure activity (rows read, rows written)	TABLE	DFT_MON_TABLE
Buffer pools	Number of reads and writes, time taken	BUFFERPOOL	DFT_MON_BUFPOOL
Unit of work	Start times, end times, completion status	UOW	DFT_MON_UOW
SQL statements	Start time, stop time, statement identification	STATEMENT	DFT_MON_STMT
Timestamp	Timestamps for operations	TIMESTAMP	DFT_MON_TIMESTAMP

The switches corresponding to the information provided in Table 16.4 are all OFF by default, except for the switch corresponding to times and timestamp information, which is ON by default.

In Version 8, you can take a snapshot using either:

• The get snapshot command

or

• SQL SELECT statements against table functions

The SQL table functions are very powerful: You can use the power of the SQL language to gather only the information that you are interested in, and you can examine changes in the output over time.

Table 16.5 lists the different levels at which you can take snapshots.

Table 16.5. Levels for Taking Monitoring Snapshots

Level	Information Captured
Application	Applications.
Buffer Pool	Buffer pool activity.
Database	Databases.
Database Manager (Instance)	For an active instance.
Dynamic SQL	Point-in-time statement from the SQL statement cache for the database.
Lock	For locks held by applications against a database.
Table	For tables within a database.
Table Space	For table spaces within a database.

16.7.1. Setting the Monitor Switches

To capture snapshot information, the Snapshot Monitors must be enabled. You can enable them at either the instance or session level.

To enable the monitors at the instance level, you need to update the database configuration and set the monitor switch to ON:

 update dbm cfg using DFT_MON_BUFPOOL ON 

To enable the monitors at the session level, you can update the monitor switch directly:

 update monitor switches using BUFFERPOOL ON 

When you have set monitor switches at the session level, you can only take snapshots in the same session. Snapshots taken in one session will not pick up the monitor switch settings for other sessions. If you have set the instance-level monitor switch and stopped and restarted DB2, you can take snapshots in any session attached to the DB2 instance.

16.7.2. Capturing Snapshot Information

Since Version 8, you can capture snapshot information in two ways:

• Using the GET SNAPSHOT command

• Selecting from a snapshot table function

The GET SNAPSHOT command captures the requested snapshot information and writes the information to the screen or to an ASCII file. You then need to examine the output of the snapshot for the information that you are looking for. Since you access the snapshot table functions using SQL, you can select only the data you are interested in, store the data quickly into a history table, and so on.

To get a snapshot for all of the activity on the database sample, you would issue the command:

 get snapshot for all on sample 

To get the same information using the snapshot table function, you would use the statement:

 SELECT *   FROM TABLE(SNAPSHOT_DATABASE('SAMPLE',-1 )) as SNAPSHOT_DATABASE 

For a complete list of the snapshot table functions, refer to the DB2 UDB SQL Reference.

16.7.3. Resetting the Snapshot Monitor Switches

The data returned by a Snapshot Monitor is based primarily on counters, and the counters are associated with a monitor switch. Monitor switches are initialized or reset when one of the following occurs.

• Application-level monitoring is used, and the application connects to the database.

• Database-level monitoring is used, and the first application connects.

• Table-level monitoring is used, and the table is first accessed.

• Table space-level monitoring is used, and the table space is first accessed.

• Issuing the RESET MONITOR command.

• Turning on a particular monitor switch.

You can reset monitor switches for the entire instance by issuing the command reset monitor all, and for a database by issuing the command reset monitor for database database_name.