![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
I was recently asked to look at the interfaces to the real-time monitoring information available in DB2 for Linux, UNIX, and Windows. I knew very little about the monitoring capabilities and what was available, so I was not really sure where to start. I poked around in the DB2 Information Center, searched the web, and talked with some colleagues looking for something that would help me get started. While there is a lot of detailed information available about monitoring, I was unable to find anything that would help guide a beginner. Therefore, I decided to capture what I learned about getting started in this article. It will certainly not cover all facets of monitoring, since I have learned enough to realize that there are a lot of capabilities within the DB2 for Linux, UNIX, and Windows product to capture, organize, and access monitoring information. This article is not intended to duplicate the material in the documentation and therefore the most benefit will be achieved by reading the information provided when following the links along the path that is described.
I started out by looking for an overview of what monitoring means in DB2. In the Information Center under “Database Fundamentals” there is an entry for Database monitoring, so I clicked on it. This defined monitoring at a very high level and pointed to topics on Interfaces for database monitoring and Monitor elements. The first of these topics points out that information is available in real-time about the state of various aspects of the database at a point in time and can also be captured as historical data in event monitors. This topic indicates that in order to monitor database operation in real-time you would use monitoring table functions with another link to further information about Table functions for monitoring.
It is in this topic that I began to get a sense of the breadth of monitoring support. There is monitoring data for systems, activities and data objects. The monitoring data for each of these perspectives can be viewed using table functions for system information, activities, data objects, as well as for locking, and system memory. The subtopics for each of these begin to point to the specific table functions to access the real-time monitoring data. The subtopics also start to classify the monitor elements into groups including request monitor elements, activity monitor elements, and data object monitor elements.
At this point, I avoided following related links to the various table function descriptions. Instead I went on to the aforementioned Monitor elements. This topic describes the 5 different types of data included in monitoring elements (counter, gauge, watermark, text, and timestamp) and links to more description of request monitor elements (which includes a subtopic with more description of the activity monitor elements), data object monitor elements, and time-spent monitoring elements. These topics also point to some of the table functions that can be used to access the monitor elements.
Now I had to choose whether to start reading through the real-time monitoring table functions or the monitor elements. Since there are close to 1300 monitor elements described, I opted to build my understanding of monitoring by first considering the table functions for monitoring.
Thanks to a colleague that reviewed an early draft of this article, I learned that there are also some useful views for monitoring that are available. These views make use of the real-time monitoring table functions to expose a primary set of metrics and calculations that should help you get started with monitoring. Links to these 10 views are provided, categorized according to the same 3 main perspectives used to categorize the table functions. You might want to start by trying these views out before digging into the various table functions.
- System information views
MON_CONNECTION_SUMMARY: retrieve key metrics for all connections in the currently connected database
MON_CURRENT_UOW: retrieve key metrics for all units of work on all members in the currently connected database
MON_DB_SUMMARY: retrieve accumulated metrics for all service classes across all members of the currently connected database
MON_SERVICE_SUBCLASS_SUMMARY: retrieve key metrics for all service subclasses in the currently connected database
MON_WORKLOAD_SUMMARY: retrieve key metrics for all workloads in the currently connected database - Activity views
MON_CURRENT_SQL: retrieve key metrics for all activities that were submitted and have not yet been completed on all members of the currently connected database
MON_PKG_CACHE_SUMMARY: retrieve a high-level summary of key metrics of the database package cache - Data object views
MON_BP_UTILIZATION: retrieve key metrics for all bufferpools on all database partitions in the currently connected database
MON_TBSP_UTILIZATION: retrieve key metrics for all table spaces on all database partitions in the currently connected database
MON_LOCKWAITS: retrieve key metrics for applications that are waiting to obtain locks in the currently connected database
The choice of what to read about next can be driven by what kind of monitoring information you need most urgently. As noted earlier, the table functions are described under the various perspectives. You can choose the perspective that sounds like the information of most interest to you. Each of the topics follows the same basic format with a short description of the perspective, references to the related table functions, and what configuration controls there are for the collection of the data. I started with table functions for system information, so I’ll describe where I went from there.
The “Monitoring system information using table functions” topic lists 8 table functions with a reference to each of the functions descriptions found at the bottom of the topic under related references. The 8 functions are really 4 pairs of functions with one providing a result table with each column representing a different monitor element and the other function providing monitor element details in the form of an XML document containing the monitor elements. These basic and detailed variants of the functions provide different approaches to viewing monitoring information that can be used for different purposes, but still within the context of a query. If you look at the MON_GET_CONNECTION table function topic, the format of the description will be consistent with the other real-time monitor table functions. It describes the parameters of the function, provides examples, and describes the columns that are returned by the function. In some cases, like this particular function which as 237 columns, there are a lot of columns returned with monitoring information for the connection. Contrast this with the 3 columns returned from MON_GET_CONNECTION_DETAILS table function where the monitor element details are captured in the XML column.
Understanding the results from the various real-time monitor functions is really dependent on an understanding of the monitor elements that they return. Some of the names for the elements are self-describing and can be readily understood without much further reading. That said, a description of each element is provided under the Monitor element reference. While reading about a monitor table function, many of the monitor elements listed for the function will include a link to the description of the monitor element to make it easier for you work your way towards an understanding of the data returned by the functions.
Gaining an understanding of the complete set of real-time monitor table functions can take some time because you will need to iterate through the function descriptions listed in each of the perspectives. The amount of detail can be intimidating. You might find it is more reasonable to focus on the set of functions and monitor elements that interest you most and try them out. Working with the examples of specific function usage from the perspectives of most interest to you, start actually using some of the table functions in your database environment to get a practical understanding based on your DB2 for Linux, UNIX, and Windows environment.
It is my hope that this article will help you on the way to learning and using the real-time monitoring capabilities that are available in your DB2 database. A summary of the path through the documentation is provided in the next section. See the Downloads section to download a pdf file that provides a map with links to the documentation referenced throughout the article.
If you want to get started in a hurry, here is a quick summary of the path described in the article.
- Read Database monitoring along with the documentation included in the linked topics on Interfaces for database monitoring and Monitor elements.
- Read about Table functions for monitoring.
- Read about Monitor elements and also read through the linked topics on request monitor elements (which includes a subtopic with more description of the activity monitor elements) and data object monitor elements.
- Choose a monitoring perspective (system information, activities,data objects, locking, and system memory) and
- Follow the related references to the various real-time monitor table functions.
- Check out the various monitoring views:
- System information views
MON_CONNECTION_SUMMARY
MON_CURRENT_UOW
MON_DB_SUMMARY
MON_SERVICE_SUBCLASS_SUMMARY
MON_WORKLOAD_SUMMARY - Activity views
MON_CURRENT_SQL
MON_PKG_CACHE_SUMMARY - Data object views
MON_BP_UTILIZATION
MON_TBSP_UTILIZATION
MON_LOCKWAITS
- System information views
- When reading about a view or table function, get more details about monitor elements by following the links to element descriptions or find the monitor element in the Monitor element reference.
- Use the views to select interesting columns or use the examples from the table function descriptions to retrieve real-time monitor information from your DB2 for Linux, UNIX, and Windows environment.
| Description | Name | Size | Download method |
|---|---|---|---|
| Monitoring roadmap with Information Center links | MonitoringMap.pdf | 69KB | HTTP |
Information about download methods Get Adobe® Reader®
Learn
- "IBM DB2
Database for Linux, UNIX, and Windows Information Center (DB2 10.1):" Find all
the information you need to use the DB2 family of products. .
- In the DB2
for Linux, UNIX, and Windows area on developerWorks, get the resources
you need to advance your DB2 skills.
Get products and technologies
-
Download a free trial version of DB2 for Linux,
UNIX, and Windows.
-
Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same
core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
-
Evaluate IBM
products in the way that suits you best: Download a product trial, try
a product online, use a product in a cloud environment, or spend a few hours
in the
SOA Sandbox learning how to implement Service Oriented Architecture efficiently.
Discuss
- Participate in the discussion forum.
- Get involved in the developerWorks
community. Connect with other developerWorks users while exploring the
developer-driven blogs, forums, groups, and wikis.
Richard Swagerman is a Database Language Architect in the DB2 for Linux, Unix, and Windows Development team in Toronto. Rick (that's what folks really call him) has spent the last 27 years working in the database area, the last 22 years as a language architect defining and extending SQL, adding XML and XQuery along the way. He remembers starting out with an SQL Reference manual that was just a couple of hundred pages (Version 10.1 would be approximately 2500 pages).
