Road map to real-time monitoring in DB2 for Linux, UNIX, and Windows

Are you wondering where to find the information you need to learn about and start using the real-time monitoring information available in the IBM® DB2® for Linux®, UNIX®, and Windows® product? This article sets out a path through the documentation that will help you deal with this question by providing direct links to the appropriate topics in the Information Center.

Richard E. Swagerman (swagrman@ca.ibm.com), DB2 Language Architect, IBM

Rick                 Swagerman photoRichard 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).



07 February 2013

Also available in Russian

Introduction

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.

Discovering the path

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.


Path summarized

If you want to get started in a hurry, here is a quick summary of the path described in the article.

  1. Read Database monitoring along with the documentation included in the linked topics on Interfaces for database monitoring and Monitor elements.
  2. Read about Table functions for monitoring.
  3. 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.
  4. Choose a monitoring perspective (system information, activities,data objects, locking, and system memory) and
    1. Follow the related references to the various real-time monitor table functions.
    2. Check out the various monitoring views:
    Alternatively, iterate through the views and then the perspectives to learn about all the real-time monitor views and table functions.
  5. 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.
  6. 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.

Download

DescriptionNameSize
Monitoring roadmap with Information Center linksMonitoringMap.pdf69KB

Resources

Learn

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

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=857505
ArticleTitle=Road map to real-time monitoring in DB2 for Linux, UNIX, and Windows
publish-date=02072013