Monitoring request information using table functions

The request monitoring perspective encompasses the complete volume of work and effort expended by the data server to process application requests. From this perspective, you can determine what the data server is doing as a whole as well as for particular subsets of application requests.

Monitor elements for this perspective, referred to as request monitor elements, cover the entire range of data server operations associated with processing requests.

Request monitor elements are continually accumulated and aggregated in memory so they are immediately available for querying. Request monitor elements are aggregated across requests at various levels of the workload management (WLM) object hierarchy: by unit of work, by workload, by service class. They are also aggregated by connection.

Use the following table functions for accessing current request monitoring information:
  • MON_GET_AGENT
  • MON_GET_CONNECTION and MON_GET_CONNECTION_DETAILS
  • MON_GET_SERVICE_SUBCLASS and MON_GET_SERVICE_SUBCLASS_DETAILS
  • MON_GET_UNIT_OF_WORK and MON_GET_UNIT_OF_WORK_DETAILS
  • MON_GET_UTILITY
  • MON_GET_WORKLOAD and MON_GET_WORKLOAD_DETAILS

This set of table functions enables you to drill down or focus on request monitor elements at a particular level of aggregation. Table functions are provided in pairs: one for relational access to commonly used data and the other for XML access to the complete set of available monitor elements.

The request monitoring information is collected by these table functions by default for a database created in version 12.1. You can change default settings using one or both of the following methods:
  • Setting the mon_req_metrics database configuration parameter to specify the minimum level of collection for all service classes.
  • Issue the CREATE SERVICE CLASS or ALTER SERVICE CLASS statement with the COLLECT REQUEST METRICS clause to specify the level of collection for a service superclass. Use this setting to increase the level of collection for a given service class over the minimum level of collection set for all service classes.

If you specify NONE, request monitor elements are not collected. If you specify BASE, base level of data for all request monitor elements is collected for all service classes.

Example

The following example shows how to collect request monitoring information for only a subset of service classes:
  1. Set the database configuration parameter mon_req_metrics to NONE as follows:
    UPDATE DB CFG FOR SAMPLE USING mon_req_metrics NONE
  2. For each required service class, set the level of collection by issuing the ALTER SERVICE CLASS statement as follows:
    ALTER SERVICE CLASS service-class-name COLLECT REQUEST METRICS BASE.