Do you know how important it is to check the ZPARM EDM SKELETON POOL size to improve your I/O rate and lower your CPU?
Do you know how to set it?
From the frontline
I was checking and verifying our WorkLoadExpert (WLX) system at a customer’s site recently, and was struck by the size of the EDMPOOL Static SQL Cache.
SSC to you
I call this the “SSC” (Static Statement Cache). What was *really* weird, is that it contained only about 450 statements – even though the EDMSTMTC was set to 1,500,000 KB !!! The Dynamic Statement Cache (DSC) contained about 38,000 Statements.
Different Horses, Different Courses
Now at the same time I happened to get an e-mail from our technical support, which contained a summary of a DSC and a SSC Snap at another customer site. They were getting 50,000 SSC statements, however the DSC size was 4,000,000 KB. This got me seriously wondering about why the SSC was so low at my current site…
Time to upgrade your EDMPOOL!
As luck would have it, an IBM Technical guy was also on site, and so we both peered into the innards of Db2. I noticed right away that the I/O rate for the EDM SKELETON POOL was *crazy* high! The ZPARM EDM_SKELETON_POOL was set to 10,000 KB which is *crazy* low! (The default is 10,240 KB). We agreed to raise it to 150,000 KB on one member and, if all ok, roll out the change to all the other members in the Group.
Changeable online ZPARM and instantly used
It is an online changeable ZPARM that is instantly used, and as we watched, we saw the I/O to the EDM_SKELETON_POOL dropping and dropping and dropping until it flat lined! Yep – we got the I/O down to zero. Just think of the pay back saving Db2 from searching for free chains, externalizing IFCIDs etc.
Not just SSC got a boost
What I then noticed was that the DSC usage improved too! Why? Well, remember that all SQL needs to be “attached” to a package? Normally a “dummy” like SYSLH… is one of many. But even these packages must be in the pool. When they are cast out then the related DSC entries are *also* cast out!
Big Bottom Line
End of the lesson was:
- DSC now contains 78,000 SQLs
- SSC now contains 70,000 SQLs
- I/O rate to EDM_SKELETON_POOL effectively Zero
- IFCID 401 (Flushed static SQL) from 3,200,000 per hour down to Zero
- IFCID 316 (Flushed dynamic SQL) from 36,000 per hour down to 4,000
Just stop to think what this means for the System-wide CPU and I/O rates…
Check it now!
Set the Db2 ZPARM EDM_SKELETON_POOL size
Please check your EDM_SKELETON_POOL size now, introduced in Db2 9, with a default value of 10,240
– Increase it to at least 150,000 if you can! –
As usual, if you have any comments or queries please feel free to drop me a line!
TTFN
Roy Boxwell
About Db2 SQL Workload Analysis
SQL Workload Expert for Db2 z/OS offers a complete review of all KPIs (CPU, elapsed, IO etc.) from all SQL (Dynamic and Static) that have executed on the entire system.