Determining why queries are queued

Queries are queued when resources are constrained. To identify whether the most constrained resource is the number of threads or the amount of sort memory, use the SYSIBMADM.DBCFG view to view the configured resources, and the MON_GET_ACTIVITY and MON_GET_DATABASE table functions to understand current resource consumption.

WITH LOADTRGT(LOADTRGT) AS (SELECT MAX(VALUE) FROM SYSIBMADM.DBCFG WHERE NAME = 'wlm_agent_load_trgt'),
     CPUINFO(CPUS_PER_HOST) AS (SELECT MAX(CPU_ONLINE / CPU_HMT_DEGREE) FROM TABLE(ENV_GET_SYSTEM_RESOURCES())),
     PARTINFO(PART_PER_HOST) AS (SELECT COUNT(*) PART_PER_HOST FROM TABLE(DB_MEMBERS()) AS T WHERE T.MEMBER_TYPE = 'D' GROUP BY HOST_NAME FETCH FIRST 1 ROWS ONLY ),
     SORTMEM (SHEAPTHRESSHR, SHEAPMEMBER) AS (SELECT VALUE, MEMBER FROM SYSIBMADM.DBCFG WHERE NAME = 'sheapthres_shr'),
     STMTS(THREADDEMAND, MEMDEMAND) AS (SELECT SUM(EFFECTIVE_QUERY_DEGREE), SUM(ESTIMATED_SORT_SHRHEAP_TOP) FROM TABLE(MON_GET_ACTIVITY(NULL,-2))
       AS T WHERE ADM_BYPASSED = 0 AND (ACTIVITY_STATE = 'EXECUTING' OR ACTIVITY_STATE = 'IDLE') AND MEMBER=COORD_PARTITION_NUM),
     ALLOCMEM(ALLOCMEM, ALLOCMEMBER) AS (SELECT SORT_SHRHEAP_ALLOCATED, MEMBER FROM TABLE(MON_GET_DATABASE(-2)) AS T)
SELECT MAX(DEC((FLOAT(ALLOCMEM)/FLOAT(SHEAPTHRESSHR))*100, 5,2)) AS PERCENT_SORTMEM_USED,
       MAX(DEC((FLOAT(MEMDEMAND)/FLOAT(SHEAPTHRESSHR))*100, 5,2)) AS PERCENT_SORTMEM_DEMAND,
       MAX(DEC((FLOAT(THREADDEMAND)/(FLOAT(LOADTRGT) * FLOAT(CPUS_PER_HOST) / FLOAT(PART_PER_HOST)))*100,5,2)) AS PERCENT_THREADS_USED
FROM LOADTRGT, SORTMEM, STMTS, ALLOCMEM, CPUINFO, PARTINFO
WHERE SHEAPMEMBER=ALLOCMEMBER

Based on the output, use the procedures described in the following topics to identify potentially problematic queries. For example, the following sample output indicates that sort memory, not threads, is the most constrained resource.

PERCENT_SORTMEM_USED PERCENT_SORTMEM_DEMAND PERCENT_THREADS_USED
-------------------- ---------------------- --------------------
               76.99                  80.03                11.76