text: --# Copyright IBM Corp. All Rights Reserved.
  --# SPDX-License-Identifier: Apache-2.0
  
  /*
   * Tablespace activity metrics using same SQL as dsmtop tablespaces screen
   */
  
  -- SQL taken from dsmtop
  CREATE OR REPLACE VIEW DB_TABLESPACE_ACTIVITY AS
  SELECT  -- CASE WHEN TBSP_STATE <> 'NORMAL' THEN CAST ('!' || TBSP_NAME AS varchar (30)) ELSE CAST (TBSP_NAME AS varchar (30)) END AS TBSP_NAME
    TBSP_NAME
  , CASE WHEN TBSP_CONTENT_TYPE = 'ANY'     THEN VARCHAR('Regular', 16)
         WHEN TBSP_CONTENT_TYPE = 'LARGE'   THEN VARCHAR('Large', 16)
         WHEN TBSP_CONTENT_TYPE = 'SYSTEMP' THEN VARCHAR('System temporary', 16)
         WHEN TBSP_CONTENT_TYPE = 'USRTEMP' THEN VARCHAR('User temporary', 16)
         ELSE                                    VARCHAR('Unknown', 16)  END AS TBSP_CONTENT_TYPE
  , TBSP_STATE
  , SUM(MTSP.POOL_DATA_L_READS + MTSP.POOL_TEMP_DATA_L_READS + MTSP.POOL_XDA_L_READS + MTSP.POOL_TEMP_XDA_L_READS + MTSP.POOL_INDEX_L_READS + MTSP.POOL_TEMP_INDEX_L_READS + MTSP.POOL_COL_L_READS + MTSP.POOL_TEMP_COL_L_READS) AS POOL_L_READS
  , SUM(MTSP.POOL_DATA_P_READS + MTSP.POOL_INDEX_P_READS + MTSP.POOL_XDA_P_READS + MTSP.POOL_TEMP_DATA_P_READS + MTSP.POOL_TEMP_INDEX_P_READS + MTSP.POOL_TEMP_XDA_P_READS + MTSP.POOL_COL_P_READS + MTSP.POOL_TEMP_COL_P_READS) AS POOL_P_READS
  , SUM(MTSP.POOL_DATA_LBP_PAGES_FOUND + MTSP.POOL_INDEX_LBP_PAGES_FOUND + MTSP.POOL_XDA_LBP_PAGES_FOUND + MTSP.POOL_COL_LBP_PAGES_FOUND - MTSP.POOL_ASYNC_DATA_LBP_PAGES_FOUND - MTSP.POOL_ASYNC_INDEX_LBP_PAGES_FOUND - MTSP.POOL_ASYNC_XDA_LBP_PAGES_FOUND - MTSP.POOL_ASYNC_COL_LBP_PAGES_FOUND)
                                                                                                                                          AS ALL_HIT_RATIO_NUMER
  , SUM(MTSP.POOL_ASYNC_DATA_READS + MTSP.POOL_ASYNC_INDEX_READS + MTSP.POOL_ASYNC_XDA_READS + MTSP.POOL_ASYNC_COL_READS)                 AS ASYNC_READS
  , SUM(MTSP.POOL_ASYNC_DATA_READ_REQS + MTSP.POOL_ASYNC_INDEX_READ_REQS + MTSP.POOL_ASYNC_XDA_READ_REQS + MTSP.POOL_ASYNC_COL_READ_REQS) AS ASYNC_READ_REQ
  , SUM(MTSP.POOL_DATA_WRITES + MTSP.POOL_INDEX_WRITES + MTSP.POOL_XDA_WRITES + MTSP.POOL_COL_WRITES + MTSP.DIRECT_WRITES)                AS WRITES
  , SUM(MTSP.POOL_ASYNC_DATA_WRITES + MTSP.POOL_ASYNC_INDEX_WRITES + MTSP.POOL_ASYNC_XDA_WRITES + MTSP.POOL_ASYNC_COL_WRITES)             AS ASYNC_WRITES
  , SUM(MTSP.DIRECT_WRITES)     AS DIRECT_WRITES
  , SUM(MTSP.POOL_DATA_WRITES)  AS POOL_DATA_WRITES
  , SUM(MTSP.POOL_INDEX_WRITES) AS POOL_INDEX_WRITES
  , SUM(MTSP.DIRECT_READS)      AS DIRECT_READS
  , SUM(MTSP.DIRECT_READ_REQS)  AS DIRECT_READ_REQS
  , SUM(MTSP.POOL_READ_TIME + MTSP.DIRECT_READ_TIME)   AS READ_TIME_MS
  , SUM(MTSP.POOL_DATA_P_READS + MTSP.POOL_INDEX_P_READS + MTSP.POOL_TEMP_DATA_P_READS + MTSP.POOL_TEMP_INDEX_P_READS + MTSP.DIRECT_READS) AS TOTAL_READS
  , SUM(MTSP.POOL_WRITE_TIME + MTSP.DIRECT_WRITE_TIME) AS WRITE_TIME_MS
  , SUM(MTSP.DIRECT_WRITES + MTSP.POOL_DATA_WRITES + MTSP.POOL_INDEX_WRITES) AS TOTAL_WRITES
  , COUNT(*) AS NUM_DBP
  , SUM(TBSP_PAGE_SIZE * (MTSP.POOL_ASYNC_DATA_READS + MTSP.POOL_ASYNC_INDEX_READS + MTSP.POOL_ASYNC_XDA_READS + MTSP.POOL_ASYNC_COL_READS)) AS READ_BYTES
  , SUM(TBSP_USED_PAGES * TBSP_PAGE_SIZE)  AS USED_SPACE_BYTES
  , SUM(TBSP_TOTAL_PAGES * TBSP_PAGE_SIZE) AS TOTAL_SPACE_BYTES
  , CASE WHEN
       SUM(TBSP_EXTENT_SIZE) = 0 THEN NULL ELSE CAST (
      SUM(TBSP_TOTAL_PAGES) * 1.0 /
       SUM(TBSP_EXTENT_SIZE) AS double) END AS NUM_EXTENT
  , CASE WHEN MAX(TBSP_TYPE) = 'DMS' THEN
       SUM(TBSP_PAGE_TOP * TBSP_PAGE_SIZE) ELSE NULL END AS TOP_SPACE_BYTES
  , CASE WHEN MAX(TBSP_TYPE) = 'DMS' THEN
       SUM(TBSP_MAX_PAGE_TOP * TBSP_PAGE_SIZE) ELSE NULL END AS MAX_TOP_SPACE_BYTES
  , CAST (CASE WHEN MAX(TBSP_TYPE) = 'DMS' AND SUM(TBSP_TOTAL_PAGES) <> 0 THEN
       SUM(TBSP_USED_PAGES) * 1.0 /
       SUM(TBSP_TOTAL_PAGES) ELSE NULL END AS double) AS PERCENT_FULL
  , CASE WHEN
       MAX(TBSP_MAX_PAGE_TOP) = 0 THEN NULL ELSE CAST (1 -
       AVG(TBSP_USED_PAGES) * 1.0 /
       MAX(TBSP_MAX_PAGE_TOP) AS double) END AS DATA_SKEW
  , CASE WHEN
       MAX(MTSP.POOL_DATA_L_READS + MTSP.POOL_TEMP_DATA_L_READS + MTSP.POOL_XDA_L_READS + MTSP.POOL_TEMP_XDA_L_READS + MTSP.POOL_INDEX_L_READS + MTSP.POOL_TEMP_INDEX_L_READS + MTSP.POOL_COL_L_READS + MTSP.POOL_TEMP_COL_L_READS) = 0 THEN NULL ELSE (1 -
       AVG(MTSP.POOL_DATA_L_READS + MTSP.POOL_TEMP_DATA_L_READS + MTSP.POOL_XDA_L_READS + MTSP.POOL_TEMP_XDA_L_READS + MTSP.POOL_INDEX_L_READS + MTSP.POOL_TEMP_INDEX_L_READS + MTSP.POOL_COL_L_READS + MTSP.POOL_TEMP_COL_L_READS) * 1.0 /
       MAX(MTSP.POOL_DATA_L_READS + MTSP.POOL_TEMP_DATA_L_READS + MTSP.POOL_XDA_L_READS + MTSP.POOL_TEMP_XDA_L_READS + MTSP.POOL_INDEX_L_READS + MTSP.POOL_TEMP_INDEX_L_READS + MTSP.POOL_COL_L_READS + MTSP.POOL_TEMP_COL_L_READS)) END AS IO_SKEW
  , MAX(TBSP_TYPE) AS TBSP_TYPE
  , CASE WHEN MAX(FS_CACHING) = 0 THEN VARCHAR('Yes', 3) ELSE VARCHAR('No', 3) END AS FS_CACHING
  , CASE WHEN MAX(TBSP_USING_AUTO_STORAGE) = 1 THEN VARCHAR('Yes', 3) ELSE VARCHAR('No', 3) END AS TBSP_USING_AUTO_STORAGE
  , CASE WHEN MAX(TBSP_AUTO_RESIZE_ENABLED) = 1 THEN VARCHAR('Yes', 3) ELSE VARCHAR('No', 3) END AS TBSP_AUTO_RESIZE_ENABLED
  , CASE WHEN MAX(TBSP_AUTO_RESIZE_ENABLED) = 1 AND MAX(TBSP_LAST_RESIZE_FAILED) = 1 THEN VARCHAR('Yes', 3) WHEN MAX(TBSP_AUTO_RESIZE_ENABLED) = 1 AND MAX(TBSP_LAST_RESIZE_FAILED) <> 1 THEN VARCHAR('No', 3)  ELSE NULL END AS TBSP_LAST_RESIZE_FAILED
  , MIN(TBSP_LAST_RESIZE_TIME) AS TBSP_LAST_RESIZE_TIME
  , SUM(MTSP.FILES_CLOSED) AS FILES_CLOSED
  , SUM(POOL_NO_VICTIM_BUFFER) AS POOL_NO_VICTIM_BUFFER
  , SUM(MTSP.UNREAD_PREFETCH_PAGES) AS UNREAD_PREFETCH_PAGES
  , SUM(MTSP.POOL_XDA_L_READS + MTSP.POOL_TEMP_XDA_L_READS) AS XDA_L_READS
  , SUM(MTSP.POOL_XDA_P_READS + MTSP.POOL_TEMP_XDA_P_READS) AS XDA_P_READS
  , SUM(MTSP.POOL_XDA_WRITES)                AS XDA_WRITES
  , SUM(TBSP_PAGE_SIZE)                      AS TBSP_PAGE_SIZE_BYTES
  , SUM(TBSP_PAGE_SIZE * TBSP_EXTENT_SIZE)   AS EXTENT_SIZE_BYTES
  , SUM(TBSP_PAGE_SIZE * TBSP_PREFETCH_SIZE) AS PREFETCH_SIZE_BYTES
  , MIN(TABLESPACE_MIN_RECOVERY_TIME) AS MIN_RECOVERY_TIME
  , SUM(MTSP.POOL_DATA_WRITES + MTSP.POOL_INDEX_WRITES + MTSP.POOL_XDA_WRITES + MTSP.POOL_COL_WRITES + MTSP.DIRECT_WRITES + MTSP.POOL_DATA_L_READS + MTSP.POOL_INDEX_L_READS + MTSP.POOL_TEMP_DATA_L_READS + MTSP.POOL_TEMP_INDEX_L_READS + MTSP.POOL_XDA_L_READS + MTSP.POOL_TEMP_XDA_L_READS + MTSP.POOL_COL_L_READS + MTSP.POOL_TEMP_COL_L_READS) AS IO
      FROM               TABLE(MON_GET_TABLESPACE(NULL,-2)) AS MTSP
        LEFT OUTER JOIN SYSCAT.BUFFERPOOLS                  AS BP    ON MTSP.TBSP_CUR_POOL_ID = BP.BUFFERPOOLID
        LEFT OUTER JOIN  TABLE(MON_GET_BUFFERPOOL(NULL,-2)) AS MBP   ON BP.BPNAME = MBP.BP_NAME AND MTSP.MEMBER = MBP.MEMBER
  GROUP BY
      TBSP_NAME
  ,   TBSP_CONTENT_TYPE
  ,   TBSP_STATE