Investigating queuing caused by hard resource entitlements

Queries are queued when there are no available resources to admit additional work. When a service class has a hard resource entitlement, queries that run in that service class are queued to prevent the resource usage in that service class from exceeding its entitlement. Consequently, queries can be queued even when there is spare capacity available for the database, because the resource usage for work in the service class has been explicitly constrained.

The MON_GET_SERIVCE_SUPERCLASS_STATS table function can be used to view the current resource entitlement for each service class as well as the entitlement type (hard or soft). The MON_GET_SERVICE_SUPERCLASS table function can be used to view the current demand for each resource controlled by the adaptive workload manager.

Using these table functions together, the following query can be used to display the current resource demand for each service class with a hard resource entitlement where there are activities queued for execution.

SELECT SUBSTR(T.SERVICE_SUPERCLASS_NAME, 1, 30) AS SERVICE_CLASS,
       MAX(DEC(RESOURCE_ENTITLEMENT, 5, 2)) AS RESOURCE_ENTITLEMENT,
       MAX(RESOURCE_SHARE_TYPE) AS ENTITLEMENT_TYPE,
       MAX(DEC(AGENT_LOAD_TRGT_DEMAND, 5, 2)) AS THREAD_DEMAND,
       MAX(DEC(SORT_SHRHEAP_DEMAND)) AS SORTMEM_DEMAND
FROM TABLE(MON_GET_SERVICE_SUPERCLASS(NULL, -2)) AS T,
     TABLE(MON_GET_SERVICE_SUPERCLASS_STATS(NULL,-1)) AS Q
WHERE T.SERVICE_CLASS_ID = Q.SERVICE_CLASS_ID AND
      RESOURCE_SHARE_TYPE = 'HARD' AND
      T.SERVICE_CLASS_ID IN (SELECT DISTINCT PARENTID
                             FROM TABLE(MON_GET_ACTIVITY(NULL,-2)) AS T,
                                  SYSCAT.SERVICECLASSES AS S
                             WHERE T.SERVICE_CLASS_ID = S.SERVICECLASSID AND
                                     ACTIVITY_STATE = 'QUEUED')
GROUP BY T.SERVICE_SUPERCLASS_NAME

The following output shows that the service class XYZ1 has a hard resource entitlement of 25%. Work in the service class is currently using 25% of the entitled thread resources and 5% of the entitled sort resources. Because the service class has reached its hard resource entitlement, expect additional incoming work to be queued even if there are additional threading or sort memory resources available on the database.

SERVICE_CLASS                  RESOURCE_ENTITLEMENT ENTITLEMENT_TYPE THREAD_DEMAND SORTMEM_DEMAND   
------------------------------ -------------------- ---------------- ------------- -----------------
XYZ1                                         25.00  HARD                     25.00                5.