News
Abstract
The SYSTMPSTG view contains one row for every temporary storage bucket that is tracking some amount of temporary storage across the system.
Temporary storage is application working storage that does not persist across a restart of the operating system. Accounting for all the temporary storage being used on the system is implemented using the concept of temporary storage buckets.
Content
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 allows you to 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
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
Was this topic helpful?
Document Information
Modified date:
19 December 2019
UID
ibm11136110