Sometimes, the default value of a Db2 ZPARM parameter is changed to reflect new realities of Db2 workloads and of systems on which Db2 for z/OS runs. When that happens, a Db2-using organization may stick with the old default value for the ZPARM, and going that route can have a negative impact on the performance of the Db2 system. Case in point: CACHEPAC, the subject of this blog entry.
I've blogged before about ZPARMs that are regularly set to sub-optimal values. I've also posted entries (part 1, part 2, and part 3) on boosting Db2's use of memory so as to improve CPU efficiency for a Db2 workload. CACHEPAC is related to both of those topics.
What is the purpose if CACHEPAC? It specifies the amount of virtual storage, in the Db2 database services address space (aka DBM1), that will be used to cache package authorization information (like, what authorization IDs have the EXECUTE privilege on package XYZ). Is caching that package authorization information important? Yes. Why? Volume - as in, volume of requests for package execution.
Get yourself a statistics report - long generated by your Db2 monitor (depending on the monitor product used, that might be called a statistics detail report), and check the section of the report that contains figures for EDM pool-related activity (alternatively, look at an online display of EDM pool activity provided by your Db2 monitor). Look for a field with a label like PT REQUESTS. PT is short for package table, and this field shows the frequency of requests to execute packages (or sections of packages). For a production Db2 subsystem, you might see hundreds, or even thousands, of requests to execute package sections per second. Having to check the SYSIBM.SYSPACKAUTH table in the catalog for each and every one of those package execution requests would be a drag on performance, for sure; thus, the in-memory package authorization cache specified via CACHEPAC in ZPARM.
It was with Db2 10 for z/OS that the default value for CACHEPAC changed, and changed pretty dramatically. In a Db2 9 environment, the default value for CACHEPAC was 100 KB. With Db2 10, the CACHEPAC default value changed to 5 MB - 50 times larger than before. Why? I suspect the change was made by the Db2 for z/OS development team for several reasons. For one thing, Db2 systems have ever-larger numbers of users and applications (that's more authorization IDs needing package-related privileges), and ever-larger numbers of packages (further upping space needs for package authorization information). Additionally, z/OS LPAR memory resources are getting bigger all the time (hundreds of GB of memory for one z/OS LPAR is not unusual these days), and with more available memory "real estate," why not use more of it for package authorization information caching, to enhance system performance?
Along with Db2 10's boost of the CACHEPAC default value, the maximum value for the parameter was increased - to 10 MB, from 5 MB.
OK, so it's not uncommon to see a Db2-using organization stick with the old default value for a ZPARM when a new default comes along. This might be due, in some cases, to inertia as much as anything. Trouble is, a 100 KB package authorization cache may prove to be WAY smaller than it should be for a Db2 workload that's large by today's standards. I saw a situation of that nature quite recently, in looking over a Db2 monitor statistics detail report sent to me by the Db2 team at a company with which I was working. The report showed that the package authorization cache for a Db2 system that was having occasional performance problems was severely undersized. How could I see that? Easy. In such a report, find a section with a heading like AUTHORIZATION MANAGEMENT. In that section of the report, find the field labeled something like AUTH UNSUCC-PKG-CACHE. That is where you'll see how often a package authorization check could NOT be successfully performed using the package authorization cache. What would you LIKE to see here? Maybe a few package authorization checks per second (in the single digits, or less, per second) that could not be serviced using information in the package authorization cache. What did I see in that report I just referenced? 1098 package authorization requests per second that were not successfully performed via the package authorization cache. Why was this so? It was so because, on a busy Db2 system doing a lot of work for applications, the package authorization cache size, per CACHEPAC in ZPARM, was too small - probably way too small.
So, here's what you should do: use your Db2 monitor to generate a statistics report - long (or, if you can't do that, check an online display of subsystem statistics provided by the Db2 monitor), and find in that report the AUTHORIZATION MANAGEMENT section. In that section, find the field with the label like PKG-AUTH UNSUCC-CACHE. What's the frequency of package authorization checks NOT accomplished by way of the package authorization cache? If that number is in the low single digits (or less) per second, good. If it's up in the double digits per second or higher, the value of CACHEPAC in ZPARM should be increased. If the frequency of package authorization requests not successfully performed using the package authorization cache is really high, like north of 100 per second, CACHEPAC should probably be substantially increased.
What is the value of CACHEPAC for your production Db2 subsystems? If it is less than the default (since Db2 10) of 5 MB, it should probably be increased to 5 MB. If it is at 5 MB and you still see a lot of package authorization requests NOT successfully performed using the package authorization cache, CACHEPAC should probably have a value greater than 5 MB (remember, that value can go up to 10 MB).
Do you have the memory resources on your z/OS LPAR to support an increase in the CACHEPAC value? Probably (if you can't use a few more MB of memory for Db2 without putting the z/OS LPAR's real storage resource under an undesirably high level of pressure, the LPAR likely has a too-small memory allocation). How can you tell if the LPAR's memory is stressed? Check (or have a z/OS systems programmer check) the LPAR's demand paging rate in an RMF (or equivalent z/OS monitor) CPU Summary Report. If the LPAR's demand paging rate is zero, memory is not overly stressed and you should be able to use more for the Db2 package authorization cache (if a CACHEPAC size increase is seen as being needed). I'd also be comfortable with a CACHEPAC size increase if the associated LPAR's demand paging rate were a very small non-zero value (i.e., less than 1 per second). If the LPAR's demand paging rate is 2-3 per second or higher, I'd like to see more memory allocated to the LPAR before substantially increasing CACHEPAC (or, if more memory can't be provided for the LPAR, consider reducing some of Db2's memory use until the demand paging rate is less than 1 per second, and THEN make CACHEPAC larger if it is too small and failing to satisfy a lot of package authorization requests per second).
And that's that. When most all package authorization checks can be successfully accomplished using information in the Db2 package authorization cache, it's a good thing. If you're not there for one or more of your production Db2 subsystems, try to get there.
