MON_SAMPLE_SERVICE_CLASS_METRICS - Get sample service class metrics
The MON_SAMPLE_SERVICE_CLASS_METRICS table function reads system metrics for one or more service classes 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
- EXECUTE privilege on the routine
- DATAACCESS authority
- DBADM authority
- SQLADM authority
Default PUBLIC privilege
None
Syntax
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 active databases 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 databases in the instance.
- service_superclass_name
- An input argument of type VARCHAR(128) that specifies a valid service superclass name in the currently connected database when calling this function. If the argument is NULL or an empty string, performance metrics are retrieved for all the superclasses in the instance whose database name satisfies the constraint imposed by the db_name parameter.
- service_subclass_name
- An input argument of type VARCHAR(128) that specifies a valid service subclass name in the currently connected database when calling this function. If the argument is NULL or an empty string, performance metrics are retrieved for all the subclasses in the instance whose database name and service superclass name satisfy the constraints imposed by the db_name and service_superclass_name parameters, respectively.
- 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
Usage notes
The MON_SAMPLE_SERVICE_CLASS_METRICS table function returns one row of data for each service class in each active database, per member.
- SERVICE_SUPERCLASS_NAME
- SERVICE_SUBCLASS_NAME
- SERVICE_CLASS_ID
The table function performs no aggregation across service classes (on a member), or across
members (for a service class or more). However, aggregation can be achieved through SQL queries. The
input parameters have the effect of being ANDed
together. Therefore, if you specify
conflicting input parameters (for example, a superclass named SUPA, and subclass named SUBB which is
not a subclass of SUPA), then no rows are returned.
Example 1
SELECT varchar(service_superclass_name,30) AS service_superclass,
varchar(service_subclass_name,30) AS service_subclass,
decimal(sum(act_throughput),10,2) AS act_throughput,
decimal(sum(total_cpu_time) /
(sum(total_cpu_time) +
sum(total_disp_run_queue_time)),3,2) AS cpu_velocity
FROM TABLE(MON_SAMPLE_SERVICE_CLASS_METRICS
(null, current server, '', '', 30, -2)) AS t
WHERE service_subclass_name IS NOT NULL
GROUP BY service_superclass_name, service_subclass_name
ORDER BY service_superclass_name, service_subclass_name
SERVICE_SUPERCLASS SERVICE_SUBCLASS ...
--------------------------- ----------------------- ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS ...
SYSDEFAULTMAINTENANCECLASS SYSDEFAULTSUBCLASS ...
SYSDEFAULTSYSTEMCLASS SYSDEFAULTSUBCLASS ...
3 record(s) selected.
Output for query (continued).... ACT_THROUGHPUT CPU_VELOCITY
... --------------- -----------------
... 214.76 0.72
... 0 0
... 0 0
Example 2
SELECT varchar(db_name,18) AS db_name,
varchar(service_superclass_name,30) AS service_superclass,
varchar(service_subclass_name,30) AS service_subclass,
cpu_shares,
cpu_limit,
decimal(estimated_cpu_entitlement, 9, 2) AS estimated_cpu_entitlement,
decimal( cpu_utilization, 3, 2) AS cpu_utilization
FROM TABLE(MON_SAMPLE_SERVICE_CLASS_METRICS
(null, null, '', '', 300, -2)) AS t
ORDER BY db_name, service_superclass_name, service_subclass_name, member
DB_NAME SERVICE_SUPERCLASS SERVICE_SUBCLASS ...
------------------ --------------------------- ----------------------- ...
SAMPLE SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS ...
SAMPLE SYSDEFAULTUSERCLASS - ...
SAMPLE SYSDEFAULTMAINTENANCECLASS SYSDEFAULTSUBCLASS ...
SAMPLE SYSDEFAULTMAINTENANCECLASS - ...
SAMPLE SYSDEFAULTSYSTEMCLASS SYSDEFAULTSUBCLASS ...
SAMPLE SYSDEFAULTSYSTEMCLASS - ...
OTHER SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS ...
OTHER SYSDEFAULTUSERCLASS - ...
OTHER SYSDEFAULTMAINTENANCECLASS SYSDEFAULTSUBCLASS ...
OTHER SYSDEFAULTMAINTENANCECLASS - ...
OTHER SYSDEFAULTSYSTEMCLASS SYSDEFAULTSUBCLASS ...
OTHER SYSDEFAULTSYSTEMCLASS - ...
12 record(s) selected.
Output
for query
(continued)....CPU_SHARES CPU_LIMIT ESTIMATED_CPU_ENTITLEMENT CPU_UTILIZATION
...---------- --------- ------------------------- ---------------
... 1000 - 0.20 0.16
... 2000 70 0.20 0.16
... 1000 - 0.10 0.09
... 1000 - 0.10 0.09
... - - - 0.10
... - - - 0.10
... 1000 - 0.50 0.45
... 5000 70 0.50 0.45
... 1000 - 0.20 0.11
... 2000 - 0.20 0.11
... - - - 0.09
... - - - 0.09