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: 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. |
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.
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.