DB2 Version 9.7 for Linux, UNIX, and Windows

MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics

The MON_GET_SERVICE_SUBCLASS table function returns metrics for one or more service subclasses.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-MON_GET_SERVICE_SUBCLASS--(--service_superclass_name--,------>

>--service_subclass_name--,--member--)-------------------------><

The schema is SYSPROC.

Table function parameters

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, metrics are retrieved for all the superclasses in the database.
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, metrics are retrieved for all the subclasses in the database.
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.

Authorization

EXECUTE privilege on the MON_GET_SERVICE_SUBCLASS function.

Example

Display the total CPU time used and total number of requests processed for each service class, ordered by CPU usage.

SELECT varchar(service_superclass_name,30) as service_superclass,
                  varchar(service_subclass_name,30) as service_subclass, 
                  sum(total_cpu_time) as total_cpu, 
                  sum(app_rqsts_completed_total) as total_rqsts 
FROM TABLE(MON_GET_SERVICE_SUBCLASS('','',-2)) AS t 
GROUP BY service_superclass_name, service_subclass_name 
ORDER BY total_cpu desc

The following example is a sample output from this query.

SERVICE_SUPERCLASS             SERVICE_SUBCLASS               ... 
------------------------------ ------------------------------ ... 
SYSDEFAULTUSERCLASS            SYSDEFAULTSUBCLASS             ... 
SYSDEFAULTMAINTENANCECLASS     SYSDEFAULTSUBCLASS             ...  
SYSDEFAULTSYSTEMCLASS          SYSDEFAULTSUBCLASS             ... 

  3 record(s) selected.

Output for query (continued).

... TOTAL_CPU            TOTAL_RQSTS  
... -------------------- -------------------- 
...               967673                  100  
.. .                   0                    0  
...                    0                    0     

Usage notes

The metrics returned by the MON_GET_SERVICE_SUBCLASS table function represent the accumulation of all metrics for requests that have executed under the indicated service subclass. Metrics are rolled up to a service class on unit of work boundaries, and periodically during the execution of requests. Therefore, the values reported by this table function reflect the current state of the system at the time of the most recent rollup. Metrics are strictly increasing in value. To determine the value of a given metric for an interval of time, use the MON_GET_SERVICE_SUBCLASS table function to query the metric at the start and end of the interval, and compute the difference.

Request metrics are controlled through the COLLECT REQUEST METRICS clause on service superclasses and the mon_req_metrics database configuration parameter at the database level. Metrics are only collected for a request if the request is processed by an agent in a service subclass whose parent service superclass has request metrics enabled, or if request metrics collection is enabled for the entire database. By default, request metrics are enabled at the database level. If request metrics are disabled at the database level and for a service superclass, the metrics reported for each connection mapped to that service superclass stop increasing (or remain at 0 if request metrics were disabled at database activation time).

The MON_GET_SERVICE_SUBCLASS table function returns one row of data per service subclass and per member. No aggregation across service classes (on a member), or across members (for a service class or more), is performed. However, aggregation can be achieved through SQL queries as shown in the example. The input parameters have the effect of being ANDed together. Therefore, if you specify conflicting input parameters (for example, a superclass name SUPA and subclass name SUBB that is not a subclass of SUPA), no rows are returned.

Tip: A request might execute in more than one service subclass. For example, this situation might occur if a request is mapped from one service subclass to another by using a Workload Manager (WLM) threshold with a REMAP ACTIVITY action. Although the time spent metrics are updated for each service subclass under which the request executes, the request counters are incremented for the service subclass where the request completed. Therefore, you should not analyze the averages of request times for a single subclass. All subclasses to which an activity can be mapped must be analyzed in conjunction with one another. For example, if a threshold exists that can map activities from service subclass A to service subclass B, then when you compute averages of requests, you should aggregate the counters and metrics for service subclasses A and B, and compute the averages using the aggregates.

Information returned

Table 1. Information returned for MON_GET_SERVICE_SUBCLASS
Column Name Data Type Description or corresponding monitor element
SERVICE_SUPERCLASS_NAME VARCHAR(128) service_superclass_name - Service superclass name
SERVICE_SUBCLASS_NAME VARCHAR(128) service_subclass_name - Service subclass name
SERVICE_CLASS_ID INTEGER service_class_id - Service class ID
MEMBER SMALLINT member- Database member
ACT_ABORTED_TOTAL BIGINT act_aborted_total - Total aborted activities
ACT_COMPLETED_TOTAL BIGINT act_completed_total - Total completed activities
ACT_REJECTED_TOTAL BIGINT act_rejected_total - Total rejected activities
AGENT_WAIT_TIME BIGINT agent_wait_time - Agent wait time
AGENT_WAITS_TOTAL BIGINT agent_waits_total - Total agent waits
POOL_DATA_L_READS BIGINT pool_data_l_reads - Buffer pool data logical reads
POOL_INDEX_L_READS BIGINT pool_index_l_reads - Buffer pool index logical reads
POOL_TEMP_DATA_L_READS BIGINT pool_temp_data_l_reads - Buffer pool temporary data logical reads
POOL_TEMP_INDEX_L_READS BIGINT pool_temp_index_l_reads - Buffer pool temporary index logical reads
POOL_TEMP_XDA_L_READS BIGINT pool_temp_xda_l_reads - Buffer pool temporary XDA data logical reads
POOL_XDA_L_READS BIGINT pool_temp_xda_l_reads - Buffer pool temporary XDA data logical reads
POOL_DATA_P_READS BIGINT pool_data_p_reads - Buffer pool data physical reads
POOL_INDEX_P_READS BIGINT pool_index_p_reads - Buffer pool index physical reads
POOL_TEMP_DATA_P_READS BIGINT pool_temp_data_p_reads - Buffer pool temporary data physical reads
POOL_TEMP_INDEX_P_READS BIGINT pool_temp_index_p_reads - Buffer pool temporary index physical reads
POOL_TEMP_XDA_P_READS BIGINT pool_temp_xda_p_reads - Buffer pool temporary XDA data physical reads
POOL_XDA_P_READS BIGINT pool_xda_p_reads - Buffer pool XDA data physical reads
POOL_DATA_WRITES BIGINT pool_data_writes - Buffer pool data writes
POOL_INDEX_WRITES BIGINT pool_index_writes - Buffer pool index writes
POOL_XDA_WRITES BIGINT pool_xda_writes - Buffer pool XDA data writes
POOL_READ_TIME BIGINT pool_read_time - Total buffer pool physical read time
POOL_WRITE_TIME BIGINT pool_write_time - Total buffer pool physical write time
CLIENT_IDLE_WAIT_TIME BIGINT client_idle_wait_time - Client idle wait time
DEADLOCKS BIGINT deadlocks - Deadlocks detected
DIRECT_READS BIGINT direct_reads - Direct reads from database
DIRECT_READ_TIME BIGINT direct_read_time - Direct read time
DIRECT_WRITES BIGINT direct_writes - Direct writes to database
DIRECT_WRITE_TIME BIGINT direct_write_time - Direct write time
DIRECT_READ_REQS BIGINT direct_read_reqs - Direct read requests
DIRECT_WRITE_REQS BIGINT direct_write_reqs - Direct write requests
FCM_RECV_VOLUME BIGINT fcm_recv_volume - FCM recv volume
FCM_RECVS_TOTAL BIGINT fcm_recvs_total - FCM recvs total
FCM_SEND_VOLUME BIGINT fcm_send_volume - FCM send volume
FCM_SENDS_TOTAL BIGINT fcm_sends_total - FCM sends total
FCM_RECV_WAIT_TIME BIGINT fcm_recv_wait_time - FCM recv wait time
FCM_SEND_WAIT_TIME BIGINT fcm_send_wait_time - FCM send wait time
IPC_RECV_VOLUME BIGINT ipc_recv_volume - Interprocess communication recv volume
IPC_RECV_WAIT_TIME BIGINT ipc_recv_wait_time - Interprocess communication recv wait time
IPC_RECVS_TOTAL BIGINT ipc_recvs_total - Interprocess communication recvs total
IPC_SEND_VOLUME BIGINT ipc_send_volume - Interprocess communication send volume
IPC_SEND_WAIT_TIME BIGINT ipc_send_wait_time - Interprocess communication send wait time
IPC_SENDS_TOTAL BIGINT ipc_sends_total - Interprocess communication send total
LOCK_ESCALS BIGINT lock_escals - Number of lock escalations
LOCK_TIMEOUTS BIGINT lock_timeouts - Number of lock timeouts
LOCK_WAIT_TIME BIGINT lock_wait_time - Time waited on locks
LOCK_WAITS BIGINT lock_waits - Lock waits
LOG_BUFFER_WAIT_TIME BIGINT log_buffer_wait_time - Log buffer wait time
NUM_LOG_BUFFER_FULL BIGINT num_log_buffer_full - Number of full log buffers
LOG_DISK_WAIT_TIME BIGINT log_disk_wait_time - Log disk wait time
LOG_DISK_WAITS_TOTAL BIGINT log_disk_waits_total - Log disk waits total
RQSTS_COMPLETED_TOTAL BIGINT rqsts_completed_total - Total requests completed
ROWS_MODIFIED BIGINT rows_modified - Rows modified
ROWS_READ BIGINT rows_read - Rows read
ROWS_RETURNED BIGINT rows_returned - Rows returned
TCPIP_RECV_VOLUME BIGINT tcpip_recv_volume - TCP/IP received volume
TCPIP_SEND_VOLUME BIGINT tcpip_send_volume - TCP/IP send volume
TCPIP_RECV_WAIT_TIME BIGINT tcpip_recv_wait_time - TCP/IP recv wait time
TCPIP_RECVS_TOTAL BIGINT tcpip_recvs_total - TCP/IP recvs total
TCPIP_SEND_WAIT_TIME BIGINT tcpip_send_wait_time - TCP/IP send wait time
TCPIP_SENDS_TOTAL BIGINT tcpip_sends_total - TCP/IP sends total
TOTAL_APP_RQST_TIME BIGINT total_app_rqst_time - Total application request time
TOTAL_RQST_TIME BIGINT total_rqst_time - Total request time
WLM_QUEUE_TIME_TOTAL BIGINT wlm_queue_time_total - Workload manager total queue time

WLM_QUEUE_ASSIGNMENTS
_TOTAL

BIGINT wlm_queue_assignments_total - Workload manager total queue assignments
TOTAL_RQST_MAPPED_IN BIGINT total_rqst_mapped_in - Total request mapped-in
TOTAL_RQST_MAPPED_OUT BIGINT total_rqst_mapped_out - Total request mapped-out
TOTAL_CPU_TIME BIGINT total_cpu_time - Total CPU time
TOTAL_WAIT_TIME BIGINT total_wait_time - Total wait time
APP_RQSTS_COMPLETED_TOTAL BIGINT app_rqsts_completed_total - Total application requests completed
TOTAL_SECTION_SORT_TIME BIGINT total_section_sort_time - Total section sort time
TOTAL_SECTION_SORT_PROC_TIME BIGINT total_section_sort_proc_time - Total section sort processing time
TOTAL_SECTION_SORTS BIGINT total_section_sorts - Total section sorts
TOTAL_SORTS BIGINT total_sorts - Total Sorts
POST_THRESHOLD_SORTS BIGINT post_threshold_sorts - Post threshold sorts
POST_SHRTHRESHOLD_SORTS BIGINT post_shrthreshold_sorts - Post shared threshold sorts
SORT_OVERFLOWS BIGINT sort_overflows - Sort overflows
TOTAL_COMPILE_TIME BIGINT

total_compile_time - Total compile time

TOTAL_COMPILE_PROC_TIME BIGINT

total_compile_proc_time - Total compile processing time

TOTAL_COMPILATIONS BIGINT

total_compilations - Total compilations

TOTAL_IMPLICIT_COMPILE_TIME BIGINT

total_implicit_compile_time - Total implicit compile time

TOTAL_IMPLICIT_COMPILE_PROC_TIME BIGINT

total_implicit_compile_proc_time - Total implicit compile processing time

TOTAL_IMPLICIT_COMPILATIONS BIGINT

total_implicit_compilations - Total implicit complications

TOTAL_SECTION_TIME BIGINT

total_section_time - Total section time

TOTAL_SECTION_PROC_TIME BIGINT

total_section_proc_time - Total section processing time

TOTAL_APP_SECTION_EXECUTIONS BIGINT

total_app_section_executions - Total section executions

TOTAL_ACT_TIME BIGINT

total_act_time - Total activity time

TOTAL_ACT_WAIT_TIME BIGINT

total_act_wait_time - Total activity wait time

ACT_RQSTS_TOTAL BIGINT

act_rqsts_total - Total activity requests

TOTAL_ROUTINE_TIME BIGINT

total_routine_time - Total routine time

TOTAL_ROUTINE_INVOCATIONS BIGINT

total_routine_invocations - Total routine invocations

TOTAL_COMMIT_TIME BIGINT

total_commit_time - Total commit time

TOTAL_COMMIT_PROC_TIME BIGINT

total_commit_proc_time - Total commits processing time

TOTAL_APP_COMMITS BIGINT

total_app_commits - Total application commits

INT_COMMITS BIGINT

int_commits - Internal commits

TOTAL_ROLLBACK_TIME BIGINT

total_rollback_time - Total rollback time

TOTAL_ROLLBACK_PROC_TIME BIGINT

total_rollback_proc_time - Total rollback processing time

TOTAL_APP_ROLLBACKS BIGINT

total_app_rollbacks - Total application rollbacks

INT_ROLLBACKS BIGINT

int_rollbacks - Internal rollbacks

TOTAL_RUNSTATS_TIME BIGINT

total_runstats_time - Total runtime statistics

TOTAL_RUNSTATS_PROC_TIME BIGINT

total_runstats_proc_time - Total runtime statistics processing time

TOTAL_RUNSTATS BIGINT

total_runstats - Total runtime statistics

TOTAL_REORG_TIME BIGINT

total_reorg_time - Total reorganization time

TOTAL_REORG_PROC_TIME BIGINT

total_reorg_proc_time - Total reorganization processing time

TOTAL_REORGS BIGINT

total_reorgs - Total reorganizations

TOTAL_LOAD_TIME BIGINT

total_load_time - Total load time

TOTAL_LOAD_PROC_TIME BIGINT

total_load_proc_time - Total load processing time

TOTAL_LOADS BIGINT

total_loads - Total loads

CAT_CACHE_INSERTS BIGINT

cat_cache_inserts - Catalog cache inserts

CAT_CACHE_LOOKUPS BIGINT

cat_cache_lookups - Catalog cache lookups

PKG_CACHE_INSERTS BIGINT

pkg_cache_inserts - Package cache inserts

PKG_CACHE_LOOKUPS BIGINT

pkg_cache_lookups - Package cache lookups

THRESH_VIOLATIONS BIGINT

thresh_violations - Number of threshold violations

NUM_LW_THRESH_EXCEEDED BIGINT

num_lw_thresh_exceeded - Number of thresholds exceeded

IDA_SEND_WAIT_TIME BIGINT ida_send_wait_time - Time spent waiting to send data
IDA_SENDS_TOTAL BIGINT ida_sends_total - Number of times data sent
IDA_SEND_VOLUME BIGINT ida_send_volume - Total data volume sent
IDA_RECV_WAIT_TIME BIGINT ida_recv_wait_time - Time spent waiting to receive data
IDA_RECVS_TOTAL BIGINT ida_recvs_total - Number of times data received
IDA_RECV_VOLUME BIGINT ida_recv_volume - Total data volume received
ADDITIONAL_DETAILS BLOB(100K) Reserved for future use