MON_GET_SERVICE_SUPERCLASS_STATS table function - Return statistics of service superclasses

One of the following authorizations is required:

• EXECUTE privilege on the routine
• DATAACCESS authority
• DBADM authority
• SQLADM authority
• WLMADM authority

Example 1: The following query displays some basic statistics for all the service superclasses on the system, across all database members:

SELECT SUBSTR(SERVICE_SUPERCLASS_NAME, 1, 26) SERVICE_SUPERCLASS_NAME,
       MEMBER,
       LAST_RESET,
       CONCURRENT_CONNECTION_TOP CONCURRENT_CONN_TOP 
FROM TABLE(MON_GET_SERVICE_SUPERCLASS_STATS('', -2)) as SCSTATS

Sample output is as follows:

SERVICE_SUPERCLASS_NAME    MEMBER         ...
-------------------------- -------------- ...
SYSDEFAULTSYSTEMCLASS                   0 ...
SYSDEFAULTMAINTENANCECLASS              0 ...
SYSDEFAULTUSERCLASS                     0 ...

... LAST_RESET                 CONCURRENT_CONN_TOP
... -------------------------- -------------------
... 2006-09-05-09.38.44.396788                   0
... 2006-09-05-09.38.44.396795                   0
... 2006-09-05-09.38.44.396796                   1

Example 2: Because every activity must be mapped to a database service class before being run, you can monitor the global state of the system by using the service class statistics table functions and querying all of the service classes on all members. In the following example, a null value is passed for service_superclass_name to return statistics for all service superclasses, and the value -2 is specified for member to return statistics for all members:

  SELECT SUBSTR(SERVICE_SUPERCLASS_NAME,1,19) AS SUPERCLASS_NAME,       
SUBSTR(CHAR(MEMBER),1,4) AS MEMB,       
CAST(COORD_ACT_LIFETIME_AVG / 1000 AS DECIMAL(9,3))         
AS AVGLIFETIME,       
CAST(COORD_ACT_LIFETIME_STDDEV / 1000 AS DECIMAL(9,3))       
AS STDDEVLIFETIME,       
SUBSTR(CAST(LAST_RESET AS VARCHAR(30)),1,16) AS LAST_RESET  
FROM TABLE(MON_GET_SERVICE_SUPERCLASS_STATS(CAST(NULL AS VARCHAR(128)),       
-2)) AS SCSTATS  ORDER BY SUPERCLASS_NAME, MEMB

The statement returns service superclass statistics such as average activity lifetime and standard deviation in seconds, as shown in the following sample output:

SUPERCLASS_NAME     MEMB AVGLIFETIME STDDEVLIFETIME LAST_RESET      
------------------- ---- ----------- -------------- ----------------
SYSDEFAULTUSERCLASS 0        634.511         22.774 2019-03-08-14.22

SELECT SUBSTR(SERVICE_SUPERCLASS_NAME,1,19) AS SUPERCLASS_NAME,       
SUBSTR(CHAR(MEMBER),1,4) AS MEMB,         
CONCURRENT_ACT_TOP AS ACTTOP,         
CONCURRENT_WLO_TOP AS CONNTOP  
FROM TABLE(MON_GET_SERVICE_SUPERCLASS_STATS(CAST(NULL AS VARCHAR(128)),        
-2)) AS SCSTATS  ORDER BY SUPERCLASS_NAME, MEMB

Sample output is as follows:

SUPERCLASS_NAME     MEMB ACTTOP      CONNTOP    
------------------- ---- ----------- -----------
SYSDEFAULTUSERCLASS 0             11           5
SYSDEFAULTUSERCLASS 1              9           6