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.

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.

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.