Temporary Storage Investigation

TempStorageInvestigation.sql

/**************************************************************************************/
/* Track temporary storage usage with QSYS2.SYSTEM_STATUS and QSYS2.SYSTMPSTG         */
/**************************************************************************************/

-- Create schema for function and detail file creation.
CREATE SCHEMA TMPSTGMON;

-- Information as presented by Navigator
SELECT SYSTEM_ASP_STORAGE,
       CURRENT_TEMPORARY_STORAGE,
       SYSTEM_ASP_USED,
       DECIMAL(DECFLOAT(CURRENT_TEMPORARY_STORAGE) / 1000, 10, 2) AS CURRENT_TEMPORARY_STORAGE_GB,
       DECIMAL(DECFLOAT(MAXIMUM_TEMPORARY_STORAGE_USED) / 1000, 10, 2) AS MAX_TEMPORARY_STORAGE_USED_GB,
       DECIMAL(DECFLOAT(CURRENT_TEMPORARY_STORAGE) * 100 / DECFLOAT(SYSTEM_ASP_STORAGE), 5, 2) AS CURRENT_TEMPORARY_PERCENTAGE_USED
    FROM TABLE (QSYS2.SYSTEM_STATUS());

-- Take granular information from SYSTMPSTG and combine it with total temp storage information from SYSTEM_STATUS
WITH BUCKET_INFO AS (
        SELECT GLOBAL_BUCKET_NAME,
               BUCKET_CURRENT_SIZE / 1000000 AS CURRENT_TEMP_SIZE_MB,
               BUCKET_PEAK_SIZE / 1000000 AS PEAK_TEMP_SIZE_MB
            FROM QSYS2.SYSTMPSTG
    ),

TEMP_STORAGE_INFO AS (
    SELECT 
       SYSTEM_ASP_STORAGE,
       DECIMAL(DECFLOAT(CURRENT_TEMPORARY_STORAGE), 10, 2) AS CURRENT_TEMP_MB,
       DECIMAL(DECFLOAT(MAXIMUM_TEMPORARY_STORAGE_USED), 10, 2) AS PEAK_TEMP_MB
       FROM TABLE (QSYS2.SYSTEM_STATUS())
)

    SELECT GLOBAL_BUCKET_NAME, 
           DECIMAL(CURRENT_TEMP_SIZE_MB, 10, 2), 
           DECIMAL(PEAK_TEMP_SIZE_MB, 10, 2),
           DECIMAL(CURRENT_TEMP_SIZE_MB / (SELECT CURRENT_TEMP_MB FROM TEMP_STORAGE_INFO) * 100, 5, 2) AS PERCENT_OF_CURRENT_TEMP,
           DECIMAL(PEAK_TEMP_SIZE_MB / (SELECT PEAK_TEMP_MB FROM TEMP_STORAGE_INFO) * 100, 5, 2) AS PERCENT_OF_PEAK_TEMP,
           DECIMAL(CURRENT_TEMP_SIZE_MB / (SELECT SYSTEM_ASP_STORAGE FROM TEMP_STORAGE_INFO) * 100, 5, 2) AS PERCENT_OF_TOTAL_ASP
        FROM BUCKET_INFO;

-- Create a function to handle automatic reporting!
CREATE OR REPLACE procedure TMPSTGMON.TEMP_STORAGE_NOTIFIER()
        LANGUAGE SQL
        MODIFIES SQL DATA
BEGIN
    DECLARE STMT VARCHAR(1000);
    DECLARE FILENAME VARCHAR(100);

    DECLARE DB_SEGMENT_WARNING INT DEFAULT 10000;
    DECLARE SQE_WARNING INT DEFAULT 1;
    DECLARE MTI_WARNING INT DEFAULT 10000;
    DECLARE LOB_WARNING INT DEFAULT 1000;
    DECLARE JOB_WARNING INT DEFAULT 100;
    
    DECLARE DB_SEGMENT_USAGE INT;
    DECLARE SQE_USAGE INT;
    DECLARE MTI_USAGE INT;
    DECLARE LOB_USAGE INT;
    DECLARE ACTIVE_JOB_USAGE INT;
    
    DECLARE HIT_LIMIT INT DEFAULT 0;
    
    -- Get the current temporary storage usage information (in MB) from QSYS2.SYSTMPSTG, and save them for the calculations below.
    SELECT BUCKET_CURRENT_SIZE / 1000000 INTO DB_SEGMENT_USAGE FROM QSYS2.SYSTMPSTG WHERE GLOBAL_BUCKET_NAME = '*DATABASE  Segment Cache';
    SELECT BUCKET_CURRENT_SIZE / 1000000 INTO SQE_USAGE FROM QSYS2.SYSTMPSTG WHERE GLOBAL_BUCKET_NAME = '*DATABASE  SQE Heap';
    SELECT BUCKET_CURRENT_SIZE / 1000000 INTO MTI_USAGE FROM QSYS2.SYSTMPSTG WHERE GLOBAL_BUCKET_NAME = '*DATABASE  DSI SQE MTI';
    SELECT SUM(BUCKET_CURRENT_SIZE) / 1000000 INTO LOB_USAGE FROM QSYS2.SYSTMPSTG WHERE GLOBAL_BUCKET_NAME IN ('*DATABASE  DS SQE LOB', '*DATABASE  DS SQQQ LOB');
    SELECT COUNT(*) INTO ACTIVE_JOB_USAGE FROM QSYS2.SYSTMPSTG WHERE (BUCKET_CURRENT_SIZE / 1000000) > 100 AND JOB_NAME IS NOT NULL AND JOB_STATUS = '*ACTIVE';
    
    CALL SYSTOOLS.LPRINTF('Checking for excessive temporary storage thresholds...');
    
    --
    -- If any temporary storage bucket exceeds the configured warning level, perform two actions:
    --      1.) Get more information about the temporary storage usage by calling an SQL service to provide 
    --          more detail about that specific bucket and save that data to a new file.
    --      2.) Build a string to print/email/send 
    
    -- Look for SQL Query Engine temp storage usage.
    IF SQE_USAGE > SQE_WARNING THEN
        SET HIT_LIMIT = 1;
        SET FILENAME = 'TMPSTGMON.' CONCAT VARCHAR_FORMAT(CURRENT DATE, 'MONDDYY') CONCAT 'SQE';
        CALL SYSTOOLS.LPRINTF('SQE temp storage threshold exceeded! Threshold: ' CONCAT SQE_WARNING CONCAT 'MB. Used: ' CONCAT SQE_USAGE CONCAT 'MB. See ' CONCAT FILENAME CONCAT ' for details.');
        
        -- Create details file
        SET STMT = 'CREATE TABLE ' CONCAT FILENAME CONCAT ' AS (SELECT * FROM TABLE(QSYS2.ACTIVE_QUERY_INFO()) ORDER BY AVERAGE_TEMPORARY_STORAGE DESC LIMIT 50) WITH DATA';
        PREPARE DETAIL_DATA_STMT FROM STMT;
        EXECUTE DETAIL_DATA_STMT;
    END IF;
    
    -- Maintained Temporary Index temp storage usage
    IF MTI_USAGE > MTI_WARNING THEN
        SET HIT_LIMIT = 1;
        SET FILENAME = 'TMPSTGMON.' CONCAT VARCHAR_FORMAT(CURRENT DATE, 'MONDDYY') CONCAT 'MTI';
        CALL SYSTOOLS.LPRINTF('MTI temp storage threshold exceeded! Threshold: ' CONCAT MTI_WARNING CONCAT 'MB. Used: ' CONCAT MTI_USAGE CONCAT 'MB. See ' CONCAT FILENAME CONCAT 'for details.');
        
        -- Create details file
        SET STMT = 'CREATE TABLE ' CONCAT FILENAME CONCAT ' AS (SELECT * FROM TABLE(QSYS2.MTI_INFO()) ORDER BY MTI_SIZE DESC LIMIT 30) WITH DATA';
        PREPARE DETAIL_DATA_STMT FROM STMT;
        EXECUTE DETAIL_DATA_STMT;
    END IF;
    
    -- Individual *ACTIVE job temp storage usage
    IF ACTIVE_JOB_USAGE > 0 THEN
        SET HIT_LIMIT = 1;
        SET FILENAME = 'TMPSTGMON.' CONCAT VARCHAR_FORMAT(CURRENT DATE, 'MONDDYY') CONCAT 'AJ';
        CALL SYSTOOLS.LPRINTF('Job temp storage threshold exceeded! Per-job threshold: ' CONCAT ACTIVE_JOB_USAGE CONCAT 'MB. Number of jobs exceeding limit: ' CONCAT ACTIVE_JOB_USAGE CONCAT '. See ' CONCAT FILENAME CONCAT ' for details.');
        
        -- Create details file
        SET STMT = 'CREATE TABLE ' CONCAT FILENAME CONCAT ' AS (SELECT * FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) WHERE TEMPORARY_STORAGE > ' CONCAT JOB_WARNING CONCAT ') WITH DATA';
        PREPARE DETAIL_DATA_STMT FROM STMT;
        EXECUTE DETAIL_DATA_STMT;
    END IF;
    
    -- If any limit was hit, get all ended jobs from the previous day
    IF HIT_LIMIT = 1 THEN
        SET FILENAME = 'TMPSTGMON.' CONCAT VARCHAR_FORMAT(CURRENT DATE, 'MONDDYY') CONCAT 'EJ';
        SET STMT = 'CREATE TABLE ' CONCAT FILENAME CONCAT ' AS (
                                    SELECT *
                    FROM TABLE (
                            SYSTOOLS.ENDED_JOB_INFO()
                        ) ORDER BY PEAK_TEMPORARY_STORAGE DESC) WITH DATA';
        PREPARE DETAIL_DATA_STMT FROM STMT;
        EXECUTE DETAIL_DATA_STMT;
    END IF;
    
    RETURN;
END;

CALL TMPSTGMON.TEMP_STORAGE_NOTIFIER();