I wanted to follow up on this thread, because we made some improvements to the way we display the plan cache snapshot data in IBM i 7.6.
In releases prior to IBM i 7.6, as you captured in your post, the Temporary Storage column displays the estimated temporary storage usage for the query. The actual storage used is available, but you have to open up Visual Explain for that query. There you will see Temporary Storage Used (MB) as an attribute in the VE diagram.
We found this as confusing as you did, so in IBM i 7.6, we changed the label of the column to be "Average Temporary Storage (MB)." it will display the average of the temporary storage used across all runs.
It sounds like you found the problematic query, but like others here, I would recommend Query Supervisor for detecting/preventing these kinds of situations in the future.
Original Message:
Sent: Mon December 08, 2025 08:00 AM
From: Robert Berendt
Subject: What was the temporary space fiend?
What the plan cache snapshot says was the biggest temp space user wasn't until the next morning. IDK what UOM temporary storage is but it doesn't seem to reflect experience.
However that long processing time fits right in the time of death.
------------------------------
Robert Berendt IBMChampion
Business Systems Analyst, Lead
Dekko
Fort Wayne
Original Message:
Sent: Mon December 08, 2025 02:01 AM
From: Satid S
Subject: What was the temporary space fiend?
Dear Robert
Your post reminds me of a past case (POWER5 or 6 era) in which a few of my customers' run-away queries filled up all the entire free disk space and brought the entire system down. Those were the days when TB size disk was not yet available.
Based on the info from your chart that total SQL statements peaked at 19:15 period and the peak temp disk consumption peaked at 19:30 period, please use ACS: Database --> SQL Performance Center GUI to create a Plan Cache Snapshot image for statements that ran after 19:00 and took longer than, say, 10-second runtime and then display the snapshot's content and sort the entries by date-time. Then look at Visual Explain graph of each statement with long runtime that ran from 19:00 onwards and look for an icon that indicates temp table/data creation in each statement's access plan.
If you think this may likely happen again in the future, you can make use of DB2i Query Supervisor ( https://www.ibm.com/support/pages/query-supervisor ) by setting a threshold for a Temp Disk Space size and write an Exit Program to leave a bread crump for you on this event.
Query Supervisor Gives Database Engineers New Power: https://www.itjungle.com/2021/04/14/query-supervisor-gives-database-engineers-new-power/
Query Supervisor example exit programs: https://www.ibm.com/docs/en/i/7.4.0?topic=ssw_ibm_i_74/rzajq/qrysuperexitpgmexamples.htm
The old Query Governer is also available but it is not as versatile as the newer Query Supervisor.
Wishing you a good luck in catching the temp disk hogger.
------------------------------
Satid S
Original Message:
Sent: Sun December 07, 2025 09:30 PM
From: Robert Berendt
Subject: What was the temporary space fiend?
Got a message that our system was approaching threshold. By the time I got back in this 11.4TB LPAR was back down to 58% used.
Something ate about 5TB of temp space.
Current temporary used : 528570 M
Peak temporary used . : 5180 G
I tried the RSS sample for SYSTOOLS.ENDED_JOB_INFO() but the SQL, unaltered, was just showing some small potatoes. Perhaps the problem was a system task and not so much a 'job'?
How do I get the details of what was eating this temp space?
I tried the show sql on the chart. It's obviously not showing you all the sql. But enough to get on a trail. It's obviously creating some CREATE ALIAS type stuff to override to particular members.
SELECT MAX(INTNUM) AS INTNUM,
DTECEN || DTETIM AS CSDTETIM,
MAX(ALL_TEMP_STORAGE_K) AS MAX_ALL_TEMP_STORAGE_K,
MAX(SYAJOBTMP) AS SYAJOBTMP,
MAX(SYUSERTMP) AS SYUSERTMP,
MAX(SYEJOBTMP) AS SYEJOBTMP,
MAX(SYDBTMP) AS SYDBTMP,
MAX(SYOSTMP) AS SYOSTMP,
SUM(JBHSQLSTMT) AS JBHSQLSTMT,
DTETIM AS DTETIM,
DTECEN AS DTECEN
FROM (
SELECT QSY.INTNUM AS INTNUM,
QSY.DTETIM AS DTETIM,
QSY.DTECEN AS DTECEN,
(SYOSTMP + SYDBTMP + SYAJOBTMP + SYEJOBTMP + SYUSERTMP) *
0.0039 AS ALL_TEMP_STORAGE_K,
SYAJOBTMP * 0.0039 AS SYAJOBTMP,
SYUSERTMP * 0.0039 AS SYUSERTMP,
SYEJOBTMP * 0.0039 AS SYEJOBTMP,
SYDBTMP * 0.0039 AS SYDBTMP,
SYOSTMP * 0.0039 AS SYOSTMP,
QJB.JBHSQLSTMT AS JBHSQLSTMT
FROM QTEMP.QPFRDATAQAPMJOBMIQ341000002 QJB
INNER JOIN QTEMP.QPFRDATAQAPMSYSTEMQ341000002 QSY
ON (QJB.INTNUM = QSY.INTNUM)
) A
GROUP BY DTETIM,
DTECEN
ORDER BY CSDTETIM
;
I modified it a little but I can't find the particular system task. I think I'm barking up the wrong tree. It seems to flag multiple jobs at the same time but I'm guessing that the logic is taking a total temp space used at the time and not the job/task temp used.
create alias QTEMP.QPFRDATAQAPMJOBMIQ341000002 for qpfrdata.qapmjobmi (q341000002);
create alias qtemp.QPFRDATAQAPMSYSTEMQ341000002 for QPFRDATA.QAPMSYSTEM (Q341000002);
SELECT QSY.INTNUM AS INTNUM,
QSY.DTETIM AS DTETIM,
QSY.DTECEN AS DTECEN,
(SYOSTMP + SYDBTMP + SYAJOBTMP + SYEJOBTMP + SYUSERTMP) *
0.0039 AS ALL_TEMP_STORAGE_K,
SYAJOBTMP * 0.0039 AS SYAJOBTMP,
SYUSERTMP * 0.0039 AS SYUSERTMP,
SYEJOBTMP * 0.0039 AS SYEJOBTMP,
SYDBTMP * 0.0039 AS SYDBTMP,
SYOSTMP * 0.0039 AS SYOSTMP,
QJB.JBHSQLSTMT AS JBHSQLSTMT,
qjb.*, qsy.*
FROM QTEMP.QPFRDATAQAPMJOBMIQ341000002 QJB
INNER JOIN QTEMP.QPFRDATAQAPMSYSTEMQ341000002 QSY
ON (QJB.INTNUM = QSY.INTNUM)
order by 4 desc
;
------------------------------
Robert Berendt IBMChampion
Business Systems Analyst, Lead
Dekko
Fort Wayne
------------------------------