DB2 Version 10.1 for Linux, UNIX, and Windows

Real-time monitoring with table functions

Real-time monitoring data includes information about work currently running on the system, statistics, and metrics for work that has been performed on the system that can help you to determine usage patterns and resource allocation and identify problem areas. You use DB2® table functions to obtain this operational information.

Table functions with names that begin with WLM_ are DB2 workload management table functions. These table functions provide access to a set of data relevant to managing your workload, such as workload management statistics, as a virtual DB2 table against which you can issue a SELECT statement. This enables you to write applications to query data and analyze it as if it were in a physical table on the data server. The DB2 workload management table functions are qualified with the SYSPROC schema name.

Table functions with names that begin with MON_ are monitoring metrics functions. Monitoring metrics provide monitoring data about the health of and query performance on your DB2 data server, which can then be used as input to a 3rd party tool or in conjunction with additional scripting you provide to analyze the metrics returned. Only those monitoring metrics functions that are relevant for DB2 workload management are included here. The monitor metrics table functions are similar to the workload management statistics table functions. Both return elements describing work that has taken place on the system. The key differences between these monitoring metrics table functions and the DB2 workload management table functions are:

The DB2 workload management table functions provide data that is more statistical in nature, such as computed values like averages, high watermarks, standard deviations, etc. In contrast, the monitoring metrics table functions provide a much more complete set of raw monitoring data.
The data reported by the DB2 statistics functions is reset when data is sent to a statistics event monitor. This resetting of data is necessary to make values such as high watermarks meaningful over a specific collection interval. Data reported by the monitoring metrics functions is also captured by a statistics event monitor, but is never reset. The data reported by monitoring interfaces accumulates from the time a database is activated until the time it is deactivated.

Some table functions return sets of information about the work that is currently running on a system:

Table 1. Table functions that show you the work currently running on the system
Objects for which information is collected	Functions and information returned
Workload occurrences	The WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES table function returns a list of workload occurrences, across database members, that are assigned to a service class. For each occurrence, there is information about the current state and the connection attributes used to assign the workload to the service class and activity statistics indicating activity volume and success rates. For an example of how to use this table function, see Example: Investigating agent usage by service class. The deprecated WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES_V97 table function is also available.
Workload occurrence activities	The WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function returns a list of current activities associated with a workload occurrence. For each activity, information is available about the current state of the activity (for example, executing or queued), the type of activity (for example, LOAD, READ, or DDL), and the time at which the activity started. For examples of how to use this table function, see Example: Aggregating data using DB2 workload management table functions and Scenario: Identifying activities that are taking too long to complete. The deprecated WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES_V97 table function is also available.
Service class agents	The WLM_GET_SERVICE_CLASS_AGENTS table function returns a list of database agents associated with a service class or an application handle. Information returned also shows the current state of the agent, the action that the agent is performing, and the status of that action. For an example of how to use this table function, see Example: Investigating agent usage by service class. The deprecated WLM_GET_SERVICE_CLASS_AGENTS_V97 table function is also available.
Activities	The MON_GET_ACTIVITY_DETAILS table function returns metrics and other detailed information as an XML document about a specific activity identified by its application handle, unit of work ID, and activity ID. One detail returned is the activity type; depending on that type, a set of additional data is returned. For example, for SQL activities, cost estimates and information about the statement text, package data, and rows returned or modified are provided. Details about the isolation level and processor resource are also available, amongst others. The deprecated WLM_GET_ACTIVITY_DETAILS table function is also available. For an example that shows you how to use this table function, see Example: Monitoring current system behavior at different levels using DB2 workload management table functions.

Some table functions return monitoring data for all requests executed on the system aggregated by service subclass and workload objects:

Table 2. Table functions that show you monitoring data aggregated by DB2 workload management objects
Objects for which data is aggregated	Functions and information returned
Workloads	Both the MON_GET_WORKLOAD table function and the MON_GET_WORKLOAD_DETAILS table function return metrics for one or more workloads. The metrics returned by this function represent the accumulation of all metrics of all workload occurrences that use the same workload definition. The MON_GET_WORKLOAD table function returns the most commonly used metrics in a column-based format and is an efficient method of retrieving base metrics. The MON_GET_WORKLOAD_DETAILS table function returns the entire set of available metrics in an XML document format, which provides maximum flexibility for formatting output. The XML based output can be parsed directly by an XML parser, or it can be converted to relational format by the XMLTABLE function.
Service subclasses	Both the MON_GET_SERVICE_SUBCLASS table function and the MON_GET_SERVICE_SUBCLASS_DETAILS table function return metrics for one or more service subclasses. The metrics returned by the table functions represent the accumulation of all metrics for requests that have executed under the indicated service subclass. The MON_GET_SERVICE_SUBCLASS table function returns the most commonly used metrics in a column based format and is an efficient method of retrieving base metrics. The MON_GET_SERVICE_SUBCLASS_DETAILS table function returns the entire set of available metrics in an XML document format, which provides maximum flexibility for formatting output. The XML-based output can be parsed directly by an XML parser, or it can be converted to relational format by the XMLTABLE function.
Connections	The MON_GET_CONNECTION table function returns data that is aggregated across user connections to the system.
Units of work	The MON_GET_UNIT_OF_WORK table function returns data that is aggregated for the current unit of work within a user connection.

Statistical information

General statistical information is also available for a number of different objects. You can use this statistical information for a number of different purposes, such as for verifying that changes to your DB2 workload management configuration have had the expected effect. If you create a new work class to classify READ activities, for example, you can verify that READ activities are being classified under the new work class correctly. You can also use table functions to quickly recognize certain problems with the system. For example, you can use table functions to determine an acceptable value for the average activity lifetime and recognize when this value exceeds its usual range, possibly indicating a problem that requires further investigation.

The following table lists the statistics that you can obtain by using table functions. All statistics table functions return the statistics that accumulated since the last time that you reset the statistics.

Table 3. Table functions that show you statistical information
Objects for which statistics are returned	Functions and statistics returned
Service superclasses	The WLM_GET_SERVICE_SUPERCLASS_STATS table function shows summary statistics across database members at the service superclass level: namely, high-water marks for concurrent connections, which are useful when determining peak workload activity.
Service subclasses	The WLM_GET_SERVICE_SUBCLASS_STATS table function shows summary statistics across database members at the service subclass level (all activities run in service subclasses). Statistics include numbers of completed activities and average execution times. This information is useful when you are looking at general system health and distribution of activities across service classes and database members. For examples of how to use this table function, see Example: Obtaining point-in-time statistics from service classes, Example: Aggregating data using DB2 workload management table functions, Example: Analyzing a service class-related system slowdown, and Scenario: Investigating a workload-related system slowdown. The deprecated WLM_GET_SERVICE_SUBCLASS_STATS_V97 table function is also available.
Workloads	The WLM_GET_WORKLOAD_STATS table function shows summary statistics across database members at the workload level. These include high-water marks for concurrent workload occurrences and numbers of completed activities. This information is useful when you are monitoring general system health or drilling down to identify problem areas. For an example of how to use this table function, see Scenario: Investigating a workload-related system slowdown. The deprecated WLM_GET_WORKLOAD_STATS_V97 table function is also available.
Work action sets	The WLM_GET_WORK_ACTION_SET_STATS table function shows summary statistics across database members at the work action set level: namely, the number of activities in each work class that had the corresponding work actions applied to them. This information is useful for understanding the effectiveness of a work action set and understanding the types of activities running on the system. For an example of how to use this table function, see Example: Analyzing workloads by activity type.
Threshold queues	The WLM_GET_QUEUE_STATS table function shows summary statistics across database members for the queues used for thresholds. Statistics include the current and total numbers of queued activities and total time spent in a queue. This informations is useful when you are querying current queued activity or validating that you defined a threshold correctly. Excessive queuing might indicate that a threshold is too restrictive, and very little queuing might indicate that a threshold is not restrictive enough or not needed.

Statistics are useful only if the time period during which they are collected is meaningful. Collecting statistics over a very long time, and for any length of time using the WLM_COLLECT_STATS stored procedure, might be less useful if it becomes difficult to identify changes to trends or problem areas because there is too much old data. Thus, you can reset statistics at any time.

Because of the default workload and default user service classes, monitoring capabilities exist from the moment that you install the DB2 data server. These can help you to start identifying sources of activities that you can use to create workloads and the service classes to which you can assign them.