Example: Obtaining point-in-time statistics from service classes
Every activity is mapped to a service class before being executed. You can monitor the system by using the service class statistics table functions and querying all of the service classes on all of the database members to obtain point-in-time statistics.
You can use the following statement to
obtain service class statistics, such as the average activity lifetime.
Passing an empty string for an argument for the WLM_GET_SERVICE_SUBCLASS_STATS
table function means that the result is not to be restricted by that
argument. The value of the last argument, member,
is -2 (a wildcard character), which means that
data from all database members is to be returned.
Note: Lifetime
information is only returned for those service classes that are defined
with COLLECT AGGREGATE ACTIVITY DATA.
SELECT SUBSTR(SERVICE_SUPERCLASS_NAME,1,19) AS SUPERCLASS_NAME,
SUBSTR(SERVICE_SUBCLASS_NAME,1,18) AS SUBCLASS_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(WLM_GET_SERVICE_SUBCLASS_STATS('', '', -2)) AS SCSTATS
ORDER BY SUPERCLASS_NAME, SUBCLASS_NAME, MEMB
SUPERCLASS_NAME SUBCLASS_NAME MEMB AVGLIFETIME STDDEVLIFETIME LAST_RESET
------------------- ------------------ ---- ----------- -------------- ----------------
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 0 691.242 34.322 2006-07-24-11.44
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 1 644.740 22.124 2006-07-24-11.44
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 2 612.431 43.347 2006-07-24-11.44
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 3 593.451 28.329 2006-07-24-11.44
You can also use the WLM_GET_SERVICE_SUBCLASS_STATS
table function to obtain the high watermark for the concurrency of
coordinator activities that run in the service class on each member:
SELECT SUBSTR(SERVICE_SUPERCLASS_NAME,1,19) AS SUPERCLASS_NAME,
SUBSTR(SERVICE_SUBCLASS_NAME,1,18) AS SUBCLASS_NAME,
SUBSTR(CHAR(MEMBER),1,4) AS MEMB,
CONCURRENT_ACT_TOP AS ACTHIGHWATERMARK
FROM TABLE(WLM_GET_SERVICE_SUBCLASS_STATS('', '', -2)) AS SCSTATS
ORDER BY SUPERCLASS_NAME, SUBCLASS_NAME, MEMB
SUPERCLASS_NAME SUBCLASS_NAME MEMB ACTHIGHWATERMARK
------------------- ------------------ ---- ----------------
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 0 10
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 1 0
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 2 0
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 3 0
By reviewing the average lifetime and number of completed activities, you can use the output of the WLM_GET_SERVICE_SUBCLASS_STATS table function to obtain a rolled-up view of the workload on each member in the database. Significant variations in the high watermarks and averages returned by a table function might indicate a change in the workload on the system.