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
>>-MON_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.
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
DB2 10.5 for Linux, UNIX, and Windows