Obtain information about temporary storage on IBM i

TemporaryStorage.sql

-- Purpose: This query returns the temporary storage's bucket ordered by size, sometimes is possibile to use it to understand what job is causing issues with temporary storage
-- Version: 1.0
-- Date 18/01/2026
-- Author: Andrea Buzzi
-- Docs: https://www.ibm.com/docs/en/i/7.6.0?topic=services-systmpstg-view

SELECT
       CASE
           WHEN GLOBAL_BUCKET_NAME IS null THEN JOB_NUMBER CONCAT '/' CONCAT JOB_USER_NAME CONCAT '/' CONCAT JOB_NAME
           ELSE GLOBAL_BUCKET_NAME
       END AS ACTIVITY,
       JOB_STATUS,
       BUCKET_CURRENT_SIZE / 1024 / 1024 AS BUCKET_CURRENT_SIZE_MB,
       BUCKET_PEAK_SIZE / 1024 / 1024 AS BUCKET_PEAK_SIZE_MB,
       (BUCKET_PEAK_SIZE-BUCKET_CURRENT_SIZE) / 1024 / 1024 AS BUCKET_SIZE_DIFFERENCE_MB
    FROM QSYS2.SYSTMPSTG
    ORDER BY BUCKET_CURRENT_SIZE DESC;
    
    stop;
    
    
    
-- Purpose: SYSTMPSTG is not always accurate; in fact, sometimes it also counts the memory used by individual jobs in the database buckets (this often happens when LOBs are involved).
--          QSYS2.ACTIVE_QUERY_INFO can help us to find SQL jobs that are consuming temporary storage, this is because this table function correctly attributes usage to individual jobs
-- Version: 1.0
-- Date 18/01/2026
-- Author: Andrea Buzzi
-- PTF Requirements: IBM i 7.4 SF99704 level 15
-- Docs: https://www.ibm.com/docs/en/i/7.6.0?topic=services-active-query-info-table-function

SELECT QUALIFIED_JOB_NAME,
                   CURRENT_TEMPORARY_STORAGE
                FROM TABLE (
                        QSYS2.ACTIVE_QUERY_INFO()
                    )
                ORDER BY CURRENT_TEMPORARY_STORAGE DESC
                LIMIT 10;
    
    stop;
    
    
    
-- Purpose: However, we are IBM i systems engineers, and we don't like to settle for less when we could have a wealth of useful information at our disposal.
--          So let's connect the dots and join QSYS.ACTIVE_QUERY_INFO with QSYS2.ACTIVE_JOB_INFO. From here, we can extract information about the user, the statement being executed, and the duration of the execution. 
--          PLEASE NOTE: this query can be very heavy and may take a long time to execute. If possible, try to set filters (e.g. subsystem) to improve execution time.
--          If you are with recent PTF, consider using FULL instead of ALL
-- Version: 1.0
-- Date 18/01/2026
-- Author: Andrea Buzzi
-- PTF Requirements: IBM i 7.6 SF99960 level 2 or IBM i 7.6 SF99950 level 11
-- Docs: https://www.ibm.com/docs/en/i/7.6.0?topic=services-active-job-info-table-function
--       https://www.ibm.com/docs/en/i/7.6.0?topic=services-active-query-info-table-function

WITH MOST_TMPSTG AS (
            SELECT QUALIFIED_JOB_NAME,
                   CURRENT_TEMPORARY_STORAGE
                FROM TABLE (
                        QSYS2.ACTIVE_QUERY_INFO()
                    )
                ORDER BY CURRENT_TEMPORARY_STORAGE DESC
                LIMIT 10)
    SELECT JOB_NAME,
           AUTHORIZATION_NAME,
           CLIENT_IP_ADDRESS,
           CURRENT_TEMPORARY_STORAGE,
           SQL_STATEMENT_TEXT,
           TO_CHAR(TIMESTAMPDIFF(4, CHAR(CURRENT_TIMESTAMP - SQL_STATEMENT_START_TIMESTAMP))) AS RUNNING_MINUTES
        FROM TABLE (
                 QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')
             )
             INNER JOIN MOST_TMPSTG
                 ON JOB_NAME = QUALIFIED_JOB_NAME
        ORDER BY CURRENT_TEMPORARY_STORAGE DESC;
        
        stop;