Apparently it can be an issue if your temporary storage is greater than 7% of your total free storage.
<IBM>
Temporary Storage
The issues outlined here in purple is that the storage needed for the plan cache is potentially being impeded by the lack of storage available. The available storage is less than 7% of total storage available, so the plan cache, being set to *AUTO is not able to grow optimally.
This does not stop the plan cache growing as needed, when new optimization plans are added, but it does mean that the optimizer will need to find space at a slower pace, and could result in plans being cleared to make way for new plans.
</IBM>
Sounds rather confusing to me. But I wonder if this sentence from the graphic explains it better: "Performance situation(s) detected: Temp storage > 7% of free storage in ASP1 all intervals" and "Problem: Temp storage used is potentially too high and may disrupt SQL plan cache auto-sizing"
There's a sentence in the graphic which states "Check if Plan Cache properties Current Plan Cache Size Threshold = *AUTO"
Auxiliary storage:
System ASP . . . . . . : 7829 G
% system ASP used . . : 68.5064
Total . . . . . . . . : 7829 G
Current temporary used : 360257 M
Peak temporary used . : 376484 M
Free disk = 7829 * (1-.685064) = 2465G
360GB temp storage / 2465GB free space = 14.6% of free space
select
sum(bucket_current_size) as sum_bucket_current_size,
sum(bucket_peak_size) as sum_bucket_peak_size
from qsys2.systmpstg;
sum_bucket_current_size = 360253001728
sum_bucket_peak_size = 410155827200
SELECT
int(bucket_current_size/1000000) as bucket_current_sizeMB ,
int(bucket_peak_size/1000000) as bucket_peak_sizeMB,
rtrim(job_number) concat '/'
concat rtrim(job_user_name)
concat '/'
concat rtrim(job_name) as q_job_name,
global_bucket_name
FROM QSYS2.SYSTMPSTG a
ORDER BY bucket_current_size desc
;
256129 273329 - *DATABASE Segment Cache
36908 37364 - *DATABASE DSI SQE MTI
26789 54841 - *MACHINE
11225 11226 - *DATABASE SQE Heap
1204 2832 - *DATABASE DS SQE LOB
1079 1080 - *OS
725 725 029625/QTCP/QTMSSMTPD -
700 798 707053/QLWISVR/ADMIN2 -
669 669 706990/QTCP/QTSMTPCLTD -
476 502 - *NOTRACK System
325 326 706459/QSYS/QYPSPFRCOL -
291 294 707124/QSYSOPR/HALAX1 -
265 349 707048/QWEBADMIN/ADMIN4 -
263 298 707051/QLWISVR/ADMIN3 -
241 361 707047/NSML1/WSERVICE -
203 203 - *CMD
193 195 - *ACTJOB
185 238 - *PASE ...
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
midrange.com
