text: --# Copyright IBM Corp. All Rights Reserved.
  --# SPDX-License-Identifier: Apache-2.0
  
  /*
   * Shows memory consumption of currently executing queries on the most constrained partition
   * 
   * Use this if DB_WLM_CONSTRAINED_REOURCE shows memory is the most constrained resource  
   * 
   */
  
  CREATE OR REPLACE VIEW DB_WLM_MEMORY_CONSUMPTION AS
  WITH 
      TOTAL_MEM    AS (SELECT MAX(BIGINT(VALUE)) AS CFG_MEM FROM SYSIBMADM.DBCFG WHERE NAME = 'sheapthres_shr' )
  ,   MAX_MEM_PART AS (SELECT MEMBER FROM TABLE(MON_GET_DATABASE(-2)) AS T ORDER BY SORT_SHRHEAP_ALLOCATED DESC FETCH FIRST 1 ROWS ONLY)
  ,   MEM_USAGE_ON_MEMBER AS (
          SELECT APPLICATION_HANDLE, UOW_ID, ACTIVITY_ID, SORT_SHRHEAP_ALLOCATED
          FROM
              MAX_MEM_PART Q
          ,   TABLE(MON_GET_ACTIVITY(NULL, Q.MEMBER)) AS T
      WHERE ACTIVITY_STATE = 'EXECUTING' OR ACTIVITY_STATE = 'IDLE'
      )
  SELECT
      A.APPLICATION_HANDLE
  ,   A.UOW_ID
  ,   A.ACTIVITY_ID
  ,   A.LOCAL_START_TIME
  ,   SECONDS_BETWEEN( CURRENT_TIMESTAMP, A.LOCAL_START_TIME)                                 AS RUNTIME_SECS
  ,   TIMESTAMPDIFF(2, (CURRENT_TIMESTAMP-A.LOCAL_START_TIME))-A.COORD_STMT_EXEC_TIME/1000    AS WAIT_ON_CLIENT_SECS
  ,   A.WLM_QUEUE_TIME_TOTAL / 1000                                                           AS QUEUED_SECS
  ,   B.APPLICATION_NAME
  ,   B.SESSION_AUTH_ID
  ,   B.CLIENT_IPADDR
  ,   A.ACTIVITY_STATE
  ,   A.ADM_BYPASSED
  ,   A.ESTIMATED_SORT_SHRHEAP_TOP                    AS EST_MEM_USAGE
  ,   C.SORT_SHRHEAP_ALLOCATED                        AS MEM_USAGE_CURR
  ,   D.CFG_MEM
  ,   DEC((FLOAT(C.SORT_SHRHEAP_ALLOCATED)    /FLOAT(D.CFG_MEM))*100,5,2)     AS QUERY_MEM_USED_PCT
  ,   DEC((FLOAT(A.ESTIMATED_SORT_SHRHEAP_TOP)/FLOAT(D.CFG_MEM))*100,5,2)     AS QUERY_MEM_EST_PCT
  ,   STMT_TEXT 
  FROM 
      TABLE(MON_GET_ACTIVITY(NULL,-2)) AS A
  ,   TABLE(MON_GET_CONNECTION(NULL,-2)) AS B
  ,   MEM_USAGE_ON_MEMBER AS C
  ,   TOTAL_MEM AS D 
  WHERE 
      A.APPLICATION_HANDLE = B.APPLICATION_HANDLE 
  AND A.MEMBER             = B.MEMBER
  AND A.APPLICATION_HANDLE = C.APPLICATION_HANDLE 
  AND A.MEMBER             = A.COORD_PARTITION_NUM
  AND A.UOW_ID             = C.UOW_ID
  AND A.ACTIVITY_ID        = C.ACTIVITY_ID 
  