This article is the first in a five-part series on monitoring and tuning DB2 UDB V8.1 for Linux, UNIX, and Windows. In Part 1,we’ll discuss snapshot monitoring. Snapshot monitoring can be used to get a “picture” of database activity at a point-in-time. It provides the status of particular resources at the time the snapshot command is issued. That’s why I refer to it as providing point-in-time monitoring data. With the exception of the TIMESTAMP monitoring switch, snapshot monitoring is not enabled by default and must be enabled at the instance or database level. Snapshot monitoring can be enabled at the instance level by turning on the default monitoring switches. To review the switch settings at the instance level, issue the GET DBM MONITOR SWITCHES or GET DBM CFG command and review the output as shown in Figure 1.

          Database Manager Configuration

     Node type = Enterprise Server Edition with local and remote clients

Database manager configuration release level            = 0x0a00

Default database monitor switches
   Buffer pool                        (DFT_MON_BUFPOOL) = OFF
   Lock                                  (DFT_MON_LOCK) = OFF
   Sort                                  (DFT_MON_SORT) = OFF
   Statement                             (DFT_MON_STMT) = OFF
   Table                                (DFT_MON_TABLE) = OFF
   Timestamp                        (DFT_MON_TIMESTAMP) = ON
   Unit of work                           (DFT_MON_UOW) = OFF
Monitor health of instance and databases   (HEALTH_MON) = OFF

Figure 1: Instance level default monitor switch settings. (Note: Output has been edited to remove irrelevant information.)

Alternatively, on UNIX you can use the grep command to search for the DFT_MON parameter. Refer to Table 1 for a list of instance and database level monitor switches.

DFT_MON_BUFFERPOOL

DFT_MON_LOCK

DFT_MON_SORT

DFT_MON_STMT

DFT_MON_TABLE

DFT_MON_UOW

*HEALTH_MON  

*DFT_MON_TIMESTAMP

*New in V8

Table 1: Instance and database level monitor switches

As shown in Table 1, there are eight monitor switches. To enable DB2 to collect data for a particular resource, the monitoring switch must be enabled. In the case of the HEALTH_MON monitor switch, it has to be enabled in order for the Health Monitor (a lightweight server side agent) to monitor and report threshold breeches and to generate alerts and notify a DBA or group of DBAs of such breeches. Monitoring switches can be enabled at the instance level by issuing the following db2 UPDATE DBM CFG USING DFT_MON_LOCK ON command or at the database level by issuing the following command:

db2 UPDATE MONITOR SWITCHES USING SORT ON 

Figure 2 and Figure 3 show the instance and database level monitor switches after the above commands have been issued.

          Database Manager Configuration

      Node type = Enterprise Server Edition with local and remote clients

  Database manager configuration release level            = 0x0a00

   Default database monitor switches
   Buffer pool                         (DFT_MON_BUFPOOL) = OFF
   Lock                                   (DFT_MON_LOCK) = ON
   Sort                                   (DFT_MON_SORT) = OFF
   Statement                              (DFT_MON_STMT) = OFF
   Table                                 (DFT_MON_TABLE) = OFF
   Timestamp                         (DFT_MON_TIMESTAMP) = ON
   Unit of work                            (DFT_MON_UOW) = OFF
 Monitor health of instance and databases   (HEALTH_MON) = OFF

Figure 2: Output from the db2 UPDATE DBM CFG USING DFT_MON_LOCK ON command

            Monitor Recording Switches

Switch list for db partition number 0
Buffer Pool Activity Information  (BUFFERPOOL) = OFF
Lock Information                        (LOCK) = OFF
Sorting Information                     (SORT) = ON  10-06-2003 23:01:04.122783
SQL Statement Information          (STATEMENT) = OFF
Table Activity Information             (TABLE) = OFF
Take Timestamp Information         (TIMESTAMP) = ON  10-06-2003 16:16:00.039279
Unit of Work Information                 (UOW) = OFF

Figure 3: Output from the db2 UPDATE MONITOR SWITCHES USING SORT ON command

If the monitor switches are enabled at the instance level then each application will inherit the switches. However, if not enabled at the instance level, then the switches have to be enabled at the database level. Once enabled, snapshot data will be available for the application setting the switch. Each application or user has their own logical view of monitor data. Therefore each application has to enable the switches of interest. Most of the data returned by the snapshot commands represent DB2 activity in the form of counters, for example ROWS READ, SORT OVERFLOWS, AGENTS WAITING FOR A TOKEN, etc. The RESET MONITOR SWITCHES command can be used to reset the monitor switch counters to zero for the database you are working with. Alternatively, the RESET MONITOR SWITCHES ALL command can be used and it will reset counters across all databases in the instance. Using the RESET command is useful when you want to calculate a specific hit ratio or indicator of activity during a previous interval. If the counters are reset to zero, you can easily identify activity that occurred during the monitoring interval versus working with accumulated counters and having to subtract previous counter values to obtain interval values. Of course, this can be done either way, but it is easier to collect data for a specific test by resetting the counters. However, on the other hand, accumulated counters are a good indication of database activity over an extended period of time and are useful for observing average and exceptional database activity. Again, this only effects the application issuing the RESET command. This monitor switch behavior can be confusing, so read the last sentence again!

As an example, if a DBA is monitoring a database and taking snapshots from a command line processor window and another DBA has enabled monitoring switches at a different tie and is using a vendor tool utilizing snapshots then each DBA will be seeing a slightly different snapshot data output.

Generally, monitor switches should be enabled or turned on with the exception of the statement and lock switch. These two switches should be enabled as needed due to amount of overhead they generate. In general, it is quite difficult to monitor what is happening if snapshot monitoring switches are not enabled.

IBM estimates indicate that running with monitoring switches enabled places approximately three percent overhead on the database being monitored. This level of overhead is generally an acceptable level across the database industry.

Once monitoring switches have been enabled, a snapshot command must be issued to generate the output of interest. These basic snapshot commands are listed in Table 2.

Table 2: Sample snapshot commands

To issue a snapshot command the following authorities are required: SYSCTRL, SYSMAINT, or SYSADM.

Snapshots provide you with a picture of database activity at a point-in-time. However, with snapshots, it is possible that you will not be able to capture the event of interest since again, we are dealing with a point-in-time. Snapshots are generally good for identifying potential problems and are good for providing information on tablespace and bufferpool activity. Later in this series, I’ll talk about how to interpret snapshot data and how to use and interpret event monitor data, which is used to capture the complete picture of database activity. I’ll also include examples of how to drill down into the event monitor detail data.

New in V8 are SQL snapshot functions. These SQL functions enable snapshots to be taken via SQL statements from a command line or application. This was previously only available through the Administrative Application Programming Interface (API). There are twenty new SQL snapshot functions in V8. Refer to Table 3 for a listing of SQL snapshot functions.

SNAPSHOT_AGENT 

SNAPSHOT_APPL_INFO

SNAPSHOT_APPL 

SNAPSHOT_BP

SNAPSHOT_CONTAINER 

SNAPSHOT_DATABASE v 

SNAPSHOT_DBM 

SNAPSHOT_FCM

SNAPSHOT_DYN_SQL 

SNAPSHOT_FCMNODE

SNAPSHOT_LOCK  

SNAPSHOT_LOCKWAIT

SNAPSHOT_STATEMENT 

SNAPSHOT_SUBSECT  

SNAPSHOT_TABLE  

SNAPSHOT_TBS 

SNAPSHOT_SWITCHES 

SNAPSHOT_QUIESCER

SNAPSHOT_ RANGES

SNAPSHOT_TBS_CFG 

Table 3: SQL Snapshot functions

SQL snapshot functions return a table as a result of the function, and the results can be returned to the application, command line processor or inserted into a table to create an historical SQL snapshot repository. Refer to the following for a sample SQL snapshot command:

SELECT * FROM TABLE(SNAPSHOT_BP('SAMPLE', 0)) AS PG;

Using the DDL in Figure 4, you can create a table with which to store the output from the SQL snapshot function.

CREATE TABLE BP_SNAP (
SNAPSHOT_TIMESTAMP       TIMESTAMP,
POOL_DATA_L_READS        BIGINT,
POOL_DATA_P_READS        BIGINT,
POOL_DATA_WRITES         BIGINT,
POOL_INDEX_L_READS       BIGINT,
POOL_INDEX_P_READS       BIGINT,
POOL_INDEX_WRITES        BIGINT,
POOL_READ_TIME           BIGINT,
POOL_WRITE_TIME          BIGINT,
POOL_ASYNC_DATA_RD       BIGINT,
POOL_ASYNC_DT_WRT        BIGINT,
POOL_ASYNC_IX_WRT        BIGINT,
POOL_ASYNC_READ_TM       BIGINT,
POOL_ASYNC_WR_TIME       BIGINT,
POOL_ASYNC_DT_RDRQ       BIGINT,
DIRECT_READS             BIGINT,
DIRECT_WRITES            BIGINT,
DIRECT_READ_REQS         BIGINT,
DIRECT_WRITE_REQS        BIGINT,
DIRECT_READ_TIME         BIGINT,
DIRECT_WRITE_TIME        BIGINT,
POOL_ASYNC_IX_RDS        BIGINT,
POOL_DATA_TESTORE        BIGINT,
POOL_INDEX_TESTORE       BIGINT,
POOL_INDEX_FESTORE       BIGINT,
POOL_DATA_FESTORE        BIGINT,
UNREAD_PREF_PGS          BIGINT,
FILES_CLOSED             BIGINT,
BP_NAME                  CHAR(18),
DB_NAME                  CHAR(8),
DB_PATH                  VARCHAR(255),
INPUT_DB_ALIAS           CHAR(8) )
In userspace1;

Figure 5: BP_SNAP DDL

Table 4: BP_SNAP Sample Table Layout for storing snapshot data from the SNAPSHOT_BP SQL function

The DDL to create the tables to enable data to be inserted from SQL snapshot functions is not presently provided with DB2 V8 (FP3). However, the table definitions are contained in the SQL Reference. Just lookup the SQL function of interest and you will find the table definition in the description. You can use it as a source for DDL and then you can create the appropriate SQL snapshot function table to serve as your snapshot repository. Refer to Figure 5 which contains the DDL necessary to create the DB2 snapshot repository table to hold SQL_BP snapshot function output. Figure 6 contains the SQL statement that can be used to issue an SQL snapshot function and insert data into the table in Figure 5. Another source of table definitions for SQL snapshot data is the snapshot function parameter definitions in the DB2 catalog table SYSIBM.ROUTINEPARMS.

INSERT INTO BP_SNAP
SELECT * FROM TABLE( SNAPSHOT_BP( 'SAMPLE', -1 )) as SNAPSHOT_BP;

Figure 6: SQL statement to issue snapshot function and insert data into associated DB2 snapshot repository table

The real beauty of the new SQL snapshot functions is that we now have the ability to retain historical data in DB2. This enables DBAs to look back in time to determine what happened during a particular time, and this data can also be used to identify trends and peak workloads and to capture representative database activity during periods of normal and abnormal database activity.

Currently, canned queries with which to query snapshot repository data are not provided with DB2. So you’ll have to develop your own, but at least the data is available to you!

Furthermore, these SQL snapshots do not have to be taken by a DBA issuing a command real time. These SQL functions can be integrated into scripts and they can be scheduled to run during your intervals of interest. Also, in the same script we can have the output inserted directly into snapshot repository tables. This can all be done automatically and without intervention. This is really how you would want to incorporate this new SQL snapshot function. But you can still take SQL snapshots as needed when problems occur and the scheduled script would be automatically capturing data and storing it in tables at the specified interval, and subsequently, you can easily query these tables and find out what was happening.

It’s important to take tablespace space requirements into consideration when creating your snapshot repository tables. This is because there is no automatic purge provided and you will have to determine how much snapshot data you need to retain in your environment. A good starting point would be two weeks to 30 days worth of data. I can envision some organizations having a requirement of a year or longer. After that, you could archive the data or purge it. The amount of data retained and the period involved will vary from environment to environment. Just remember to develop an archive and purge strategy. Of course, management may dictate the time period for retaining the data.

These SQL snapshot functions provide what I call a breakthrough in monitoring capability in DB2. For the first time in DB2 to we have the ability to capture historical data to DB2 tables. This has been a top customer requirement for some time. When your manager asks you what happened three days ago or if a user inquires as to what was going on during a certain period, three days past, you can now query your snapshot repository and solve the mystery!

In Part 2 of this series, I will discuss the new write-to-table event monitor capability in DB2 V8 and then follow that up with articles on Interpreting Snapshot and Event Monitor data. Stay tuned!