MON_SAMPLE_WORKLOAD_METRICS - Get sample

The MON_SAMPLE_WORKLOAD_METRICS table function reads system metrics for one or more workloads across one or more databases at two points in time: at the time the function is called and after a given amount of time has passed.

The function computes various statistics from these metrics.

Authorization

One of the following authorities is required to execute the routine:
  • EXECUTE privilege on the routine
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority

Default PUBLIC privilege

None

Syntax

Read syntax diagramSkip visual syntax diagramMON_SAMPLE_SERVICE_CLASS_METRICS(hostname ,db_name,workload_name,sample_time,member)

The schema is SYSPROC.

Table function parameters

hostname
An input argument of type VARCHAR(255) that specifies a fully qualified host name or an IP address of the node from which to generate the report. If the argument is NULL or an empty string, metrics are taken from all nodes in the instance.
db_name
An input argument of type VARCHAR(128) that specifies a valid database name in the same instance as the currently connected database when calling this function. The database must have a directory entry type of either "Indirect" or "Home", as returned by a LIST DATABASE DIRECTORY command. If the argument is NULL or an empty string, metrics are taken from all active databases in the instance.
workload_name
An input argument of type VARCHAR(128) that specifies a specific workload for which the metrics are to be returned. If the argument is NULL or an empty string, metrics are returned for all workloads in the instance whose database name satisfies the constraint imposed by the db_name parameter.
sample_time
The amount of time the function collects data before computing a result on that data. This value is measured in seconds and must be greater than or equal to 1.
member
An input argument of type INTEGER that specifies a valid member in the same instance as the currently connected database when calling this function. Specify -1 for the current database member, or -2 for all database members. If the NULL value is specified, -1 is set implicitly.

Information returned

Table 1. Information returned from MON_SAMPLE_WORKLOAD_METRICS
Column name Data type Description or corresponding monitor element
HOSTNAME VARCHAR(255) hostname - Host name monitor element
DB_NAME VARCHAR(128) db_name - Database name monitor element
WORKLOAD_NAME VARCHAR(128) workload_name - Workload name monitor element
WORKLOAD_ID INTEGER workload_id - Workload ID monitor element
MEMBER SMALLINT member- Database member
UOW_THROUGHPUT DOUBLE uow_throughput - Unit of work throughput monitor element
UOW_LIFETIME_AVG DOUBLE uow_lifetime_avg - Unit of work lifetime average monitor element
UOW_COMPLETED_TOTAL BIGINT uow_completed_total - Total completed units of work monitor element
TOTAL_CPU_TIME BIGINT total_cpu_time - Total CPU time monitor element
TOTAL_DISP_RUN_QUEUE_TIME BIGINT total_disp_run_queue_time - Total dispatcher run queue time monitor element
ACT_THROUGHPUT DOUBLE act_throughput - Activity throughput monitor element
ACT_COMPLETED_TOTAL BIGINT act_completed_total - Total completed activities
CPU_UTILIZATION DOUBLE cpu_utilization - CPU utilization monitor element
CPU_VELOCITY DOUBLE cpu_velocity - CPU velocity monitor element

Usage notes

The MON_SAMPLE_WORKLOAD_METRICS table function returns one row of data per workload and per member for each database. The table function performs no aggregation across workloads (on a member), or across members (for a workload or more). However, aggregation can be achieved through SQL queries.

Example

Display the unit of work (UOW) throughput, activity throughput, and average CPU utilization for a 30 second period, for each workload and across all partitions.
SELECT varchar(workload_name,30) AS workload_name,
       decimal(sum(uow_throughput),10,2) AS uow_throughput, 
       decimal(sum(act_throughput),10,2) AS act_throughput, 
       decimal(sum(cpu_utilization),3,2) AS cpu_utilization
   FROM TABLE(MON_SAMPLE_WORKLOAD_METRICS
      (null, current server, '',30 ,-2)) AS t 
   GROUP BY workload_name
   ORDER BY workload_name
This an example of output from this query.
WORKLOAD_NAME           UOW_THROUGHPUT          ...
----------------------- ----------------------- ...
SYSDEFAULTUSERWORKLOAD                   124.43 ...
SYSDEFAULTADMWORKLOAD                         0 ...

  2 record(s) selected.
Output for query (continued).
... ACT_THROUGHPUT  CPU_UTILIZATION  
... --------------- -----------------
...          214.76              0.89
...               0                 0