QSYS2.SYSTMPSTG

Db2 for i can be used to examine the IBM i temporary storage pool detail.

While this data can also be seen through IBM i Navigator, Collection Services, and elsewhere, SQL provides a unique and useful approach to be considered.

The following query lets you easily identify the top temporary storage consumers related to active database server connections.
 

Example 1.  Review the active database server connections and return the top 10 temporary storage consumers
Note: SQL Query Engine (SQE) temporary storage is not reflected within the following job-specific temporary storage buckets.

WITH TOP_TMP_STG (bucket_current_size, bucket_peak_size, q_job_name) AS (
SELECT bucket_current_size, bucket_peak_size, rtrim(job_number) concat '/' concat rtrim(job_user_name) concat '/' concat rtrim(job_name) as q_job_name
  FROM QSYS2.SYSTMPSTG
    WHERE job_status = '*ACTIVE' AND
          JOB_NAME IN ('QZDASOINIT', 'QZDASSINIT', 'QRWTSRVR', 'QSQSRVR')
               ORDER BY bucket_current_size desc fetch first 10 rows only
)
SELECT bucket_current_size, bucket_peak_size, q_job_name, V_SQL_STATEMENT_TEXT, B.* 
  FROM TOP_TMP_STG, TABLE(QSYS2.GET_JOB_INFO(q_job_name)) B;

Example 2. Review SQL Query Engine (SQE) temporary storage buckets

SELECT CASE
   WHEN global_bucket_name = '*DATABASE Segment Cache' THEN 'SQE Temporary Runtime Objects'
   WHEN global_bucket_name = '*DATABASE DSI SQE MTI' THEN 'SQE Temporary Indexes'
   WHEN global_bucket_name = '*DATABASE SQE Heap' THEN 'SQE Plan Cache & Work Areas'
   ELSE global_bucket_name END as system_storage_bucket,
   bucket_current_size, bucket_peak_size FROM qsys2.systmpstg
WHERE global_bucket_name is NOT NULL ORDER BY bucket_current_size desc;