When building or managing applications, an important goal is achieving good performance. In fact, users generally expect instantaneous performance, even if this is not included in the requirements.

In order to manage just about anything, including performance, you must be able to measure it. DB2 UDB on Linux, UNIX and Windows includes many facilities for measuring performance and tracing system activity. These facilities allow for measuring activity at just about any level of detail required by database administrators (DBA), application developers, and system administrators.

Purposes of monitoring

The three main purposes of monitoring are problem determination, performance management, and trend analysis. The meaning of problem determination is obvious: You or somebody has detected a problem, and you work to solve it. Problem determination requires a sense of what is happening now, as it is necessary to see what is causing, or has recently caused, the problem. With some performance management and trending, you can avoid most of problems.

Performance management allows you to use system resources optimally, and helps ensure that some problems are avoided. By using performance management information and techniques, you can try to avoid some time on problem determination and increase overall user satisfaction.

Trend analysis takes performance management to another level where historical data is kept and used to determine growth and trends in usage. Trends help you identify changes in overall system activity and plan hardware upgrades if they are needed. Also, if there is an unexpected jump (different from what your trends show), it allows you to ask the question: "What changed?"

DB2 usage trends usually stay in line with business activity, but not always. The business unit often knows its busy periods, but it might not appreciate their impact on system resources. For system load problems, it is useful to compare the system activity against the same period yesterday or last week, month, or year to identify changes between periods. Tuning decisions, particularly those related to database manager and database configuration parameters, benefit from your understanding of usage trends.

Back to top

Types of monitors

To help with these monitoring objectives, DB2 provides two types of monitors: snapshot and event monitors. Snapshot monitors show activity for a given point in time. You can think of it as a picture of system activity. This will let you see what applications are connected to a database, help you diagnose lock problems, and allow you to view buffer pool, table space, and table usage, and statements being executed. Snapshot monitors become much more useful when you keep history and compare the snapshots over time.

Event monitors, unlike snapshots, capture everything that happens in your particular area of interest over a period of time. Rather than just a snapshot, event monitors can be thought of a movie capturing what occurred over time. DB2 produces event records at the start and end of many events. While snapshots are most useful while a problem is occurring, events are most accurate for historical uses, such as system chargeback, resource planning, and trend analysis.

In DB2 Version 8, keeping history becomes easier with the new ability to write event monitors directly to DB2 tables and the ability to use snapshot table functions to store snapshots into DB2 tables.

Back to top

Snapshot monitors

To use snapshot monitors, you first must turn the monitor switches on using the following command:

db2 update monitor switches using bufferpool on lock on sort on statement on table on uow on

It is also possible to control the monitor switches in the database manager configuration, although there is an exception with the table monitor switch because you must always start it using the above command.

To check the status of the monitor switches, you can use the Get Monitor Switches command:

The following chart shows the command to turn on a snapshot monitor and the main types of information collected:

Once you have the switches turned on, you can look at the available data.To look at a snapshot, you use the GET SNAPSHOT command. Figure 3 shows the available commands for viewing the DB2 internal state:

From the command line, it's obvious that there is quite a bit of output. This points to the advantage of writing it to a DB2 table. After you have built the monitor and are gathering data, a few calculations can reduce this to usable information.

Back to top

Write your own snapshot monitor

While getting a snapshot is relatively easy, take this a step further using the new table functions in DB2 Version 8. This allows you to not only do problem determination but also to do performance management and trend analysis.

The table functions allow performance data to be displayed through select statements. If the insert statement is used with a subselect, the returned data can be stored into DB2 tables.

The three steps to write a snapshot monitor are:

• Create the snapshot tables.
• Populate the snapshot tables with a script.
• Develop reports from the data.

There are 20 snapshot functions in all, but the four most important are database, buffer pool, table space, and table. The example below uses buffer pools.

Set up snapshot tables

To create the snapshot tables, you can get the column definition from the SYSCAT. FUNCPARMS system table. This table will receive the data from the insert statement.

-- UPQ020 Create a table to store buffer pool snapshots
-- The snapshot is stored into this table using UPS021 
-- 
-- UPQ022 and following will contain SQL to report from 
-- these tables.
--
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;

This DDL is publicly available from www.db-hq.net. The following URL, for entry UPS025, will define all four tables:

http://www.db-hq.net/Articles/db2luw/perfluw/LUWv8SNP/UPS025.sql

Copy and paste the text to a file and save it on your computer. You can create the tables using the following command:

db2 -tvf UPS025.sql

Populate the snapshot tables

To store a single snapshot into the BP_SNAP table, you can use the following SQL.:

-- UPQ021 Store a snapshot into a table.
-- The table is created using UPQ020
-- In the near future, this query will be incorporated into a shell script. 
-- 
-- UPQ022 and following will contain SQL to report from 
-- these tables.
--
INSERT INTO BP_SNAP
SELECT 
	SNAPSHOT_TIMESTAMP,
	POOL_DATA_L_READS,
	POOL_DATA_P_READS,
	POOL_DATA_WRITES,
	POOL_INDEX_L_READS,
	POOL_INDEX_P_READS,
	POOL_INDEX_WRITES,
	POOL_READ_TIME,
	POOL_WRITE_TIME,
	POOL_ASYNC_DATA_READS,
	POOL_ASYNC_DATA_WRITES,
	POOL_ASYNC_INDEX_WRITES,
	POOL_ASYNC_READ_TIME,
	POOL_ASYNC_WRITE_TIME,
	POOL_ASYNC_DATA_READ_REQS,
	DIRECT_READS,
	DIRECT_WRITES,
	DIRECT_READ_REQS,
	DIRECT_WRITE_REQS,
	DIRECT_READ_TIME,
	DIRECT_WRITE_TIME,
	POOL_ASYNC_INDEX_READS,
	POOL_DATA_TO_ESTORE,
	POOL_INDEX_TO_ESTORE,
	POOL_INDEX_FROM_ESTORE,
	POOL_DATA_FROM_ESTORE,
	UNREAD_PREFETCH_PAGES,
	FILES_CLOSED,
	BP_NAME,
	DB_NAME,
	DB_PATH,
	INPUT_DB_ALIAS
FROM TABLE( SNAPSHOT_BP( 'perfdb', -1 )) as SNAPSHOT_BP;

A single row of data now exists. To make this more useful, you can download the following shell script and use it to populate the four snapshot tables:

http://www.db-hq.net/Articles/db2luw/perfluw/LUWv8SNP/UPS024.sql

Interpret snapshot output

Many aspects from the snapshots are self-explanatory, while others require some calculation.

The simplest query for getting snapshot data is:

-- UPQ022 Our first sample query for snapshot data.
--
-- The table is created using UPQ020, and populated using UPQ021
-- In the near future, UPQ021 will be incorporated into a shell script. 
--
SELECT SNAPSHOT_TIMESTAMP AS TSTAMP, POOL_DATA_L_READS AS DATA_LREADS, 
  POOL_DATA_P_READS AS DATA_PREADS, POOL_DATA_WRITES AS DATA_WRITES, 
  POOL_INDEX_L_READS AS IX_LREADS, BP_SNAP.POOL_INDEX_P_READS AS IX_PREADS   
FROM BP_SNAP;

Note: Buffer pool tuning is one of the best opportunities for improving DB2 performance. A complete discussion of buffer pool tuning is beyond the scope of this article, but it is worth your time to investigate this important area. The Microsoft Excel workbook link below is a good starting point. Look for other articles on the Web about this topic. Some good examples on IBM developerWorks":
DB2 Basics: Table Spaces and Buffer Pools
Best practices for tuning DB2 UDB Version 8.1 and its databases

In order to make the result more readable from the command line, not all columns were selected. Of course, it is possible to put conditions in the WHERE clause of SELECTstatements to only report exceptions.

Many of the same conditions apply to buffer pools and table spaces. Buffer pools are areas in memory. Buffer pool measurements show overall performance. Table spaces are files, and their measurements show individual performance.

Buffer pool calculations

The DB2 System Monitor Guide and Reference contains a series of formulas you can use to determine buffer pool efficiency. I automated these and placed them in an Excel workbook.

The Excel Workbook contains three spreadsheets. The first is a calculation of buffer pool performance ratios. The second is an example of getting DB2 data into Excel through Open Database Connectivity (ODBC). The third is a graphing example that uses the Excel charting facility. The following is a link to the workbook:

http://db-hq.net/downloads/BP%20Analysis.xls

Back to top

Event monitors

You can use event monitors to get a complete picture of activity. They show activity from start to finish, and often consist of both a start and end event record. The most common uses for event monitors are for connections, locks, and statements.

Event monitor output can be written to files, named pipes, and starting in DB2 Version 8, to tables. The output can either be blocked or non-blocked. If the output is blocked, this ensures that there is no data loss, but this can cause big problems on busy systems that produce a large volume of records. Blocked output should generally be avoided, particularly for statement event monitors, as the instance can crash. You can limit the granularity of an event monitor to a single user or application.

Named pipes are useful if you are writing your own programs to retrieve monitor data. Use them unblocked to avoid impact on DB2 if things get busy.

Connection events are useful to track system usage on a user or application basis. This data allows you to identify poorly performing programs, the heaviest users, and usage trends. If the data is reviewed daily, it gives you the opportunity to follow up with users to discuss activity and possibly provide SQL training or adjust the physical design to better support their use of DB2.

Multiple event monitors can be defined and be active at the same time. Connection events are generally of sufficiently low volume as to not cause a problem. The following SQL statement defines an event monitor for all connections:

CREATE EVENT MONITOR dlmon FOR CONNECTIONS WRITE TO TABLE;

Once the event monitor is setup you need to turn the event monitor on using:

SET EVENT MONITOR dlmon STATE=1; 

You can also set event monitors to automatically start when the database starts. The execution of the above statement causes the creation of four tables:

• connheader_dlmon
• conn_dlmon
• connmemuse_dlmon
• control_dlmon

Note: Buffer pool tuning is one of the best opportunities for improving DB2 performance. A complete discussion of buffer pool tuning is beyond the scope of this article, but it is worth your time to investigate this important area. The Microsoft Excel workbook link below is a good starting point. Look for other articles on the Web about this topic. Some good examples on developerWorks are:
"DB2 Basics: Table Spaces and Buffer Pools"
"Best practices for tuning DB2 UDB V8.1 and its databases".

Back to top

Summary

Measurement is the key to understanding how your organization uses DB2. By reviewing performance data on a regular basis, you will develop a better understanding of the workload at your organization, and you will be able to take corrective action before problems occur.

Back to top

Download

Name	Size	Download method
bpanalysis.zip	37 KB	FTP | HTTP

Information about download methods

Resources

DB2 UDB System Monitor Guide and Reference

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Purposes of monitoring
• Types of monitors
• Snapshot monitors
• Write your own snapshot monitor
• Event monitors
• Summary
• Download
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers