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.
- 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.
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: Analyzing workloads by activity type. 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 displays 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. For an example of how to use this table function, see Example: Monitoring current system behavior at different levels using Db2 workload management table functions. The deprecated WLM_GET_ACTIVITY_DETAILS table function is also available. |
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.
|
Service superclasses | Both the
MON_GET_SERVICE_SUPERCLASS table function and the MON_GET_SERVICE_SUPERCLASS_DETAILS table function
return metrics for one or more service superclasses. The metrics returned by the table functions
represent the accumulation of all metrics for requests that have executed under the indicated
service superclass.
|
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.
|
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.
Objects for which statistics are returned | Functions and statistics returned |
---|---|
Service superclasses | The MON_GET_SERVICE_SUPERCLASS_STATS table function displays summary statistics across database members at the service superclass level. Statistics include the number of completed activities and average execution time for each member. This information is useful when you are looking at general system health and the distribution of activities across service classes and database members. |
Service subclasses | The
WLM_GET_SERVICE_SUBCLASS_STATS table function displays 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:
The deprecated WLM_GET_SERVICE_SUBCLASS_STATS_V97 table function is also available. |
Workloads | The WLM_GET_WORKLOAD_STATS
table function displays 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 examples of how to use this table function, see:
|
Work action sets | The WLM_GET_WORK_ACTION_SET_STATS table function displays 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 displays 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 information 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.