DB2 Version This Was Written For
Where This Parameter Lives
How To Check Value
> db2 get db cfg for sample |grep PCKCACHESZ Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(250509)
> db2 "select name, substr(value,1,16) value, value_flags, substr(deferred_value,1,16) deferred_value, deferred_value_flags, substr(datatype,1,16) datatype from SYSIBMADM.DBCFG where name='pckcachesz' with ur" NAME VALUE VALUE_FLAGS DEFERRED_VALUE DEFERRED_VALUE_FLAGS DATATYPE -------------------------------- ---------------- ----------- ---------------- -------------------- ---------------- pckcachesz 250509 AUTOMATIC 250509 AUTOMATIC BIGINT
Specifies size (in 4KB pages) of the area of memory used for caching static and dynamic SQL statements and information about those statements that is summarized across executions.
This is a paramter with a high possibility for performance impact. Also in setting it too high, you may get unusually large dynamic sql snapshots or output from MON_GET_PKG_CACHE_STMT
32 – 2,147,483,646
Recycle Required To Take Effect?
No – changes to this parameter take effect immediately if there is space in database shared memory.
Can It Be Set To AUTOMATIC?
Yes, this can be set to AUTOMATIC, however it is one that I’m most likely not to set at AUTOMATIC.
How To Change It
db2 update db cfg for dbname using PCKCACHESZ 120
Rule of Thumb
If you really have no clue, Automatic will work as long as you’re not storing snapshot information on disk – if you are, then you may need to consider setting this to something else. If you want to use a hard value, 8192 isn’t a bad place to start.
This is a parameter that assuming you’re not going with AUTOMATIC, you really do need to observe over time and set based on the activity in your environment. This actually falls into my top 10 physical performance tuning areas. When manually tuning this, you want to look at the following:
> db2 get snapshot for database on sample |grep "Package cache" Package cache lookups = 2326089 Package cache inserts = 32733 Package cache overflows = 0 Package cache high water mark (Bytes) = 688635351
1-Package cache inserts/Package cache lookups
So in this case, that would be:
That’s also called your package cache hit ratio. To calculate it in one step, based on the mon_get functions, use:
> db2 "select decimal(PKG_CACHE_INSERTS,10,3) as PKG_CACHE_INSERTS, decimal(PKG_CACHE_LOOKUPS,10,3) as PKG_CACHE_LOOKUPS, decimal(1-decimal(PKG_CACHE_INSERTS,10,3)/decimal(PKG_CACHE_LOOKUPS,10,3),10,3) as pkg_cache_hit_ratio from table(SYSPROC.MON_GET_WORKLOAD('SYSDEFAULTUSERWORKLOAD', -2)) as t with ur" PKG_CACHE_INSERTS PKG_CACHE_LOOKUPS PKG_CACHE_HIT_RATIO ----------------- ----------------- ------------------- 32750.000 2329102.000 0.985
Obviously if you’re using WLM, you might have to tweak the above, but if you’re not (and it’s a pay-for-use feature), then the above should encompass essentially all database activity.
As with many hit ratios, we’d love to see this above 95%, and can usually be happy with it above 90% and may accept it as low as 80%
Related Error Messages
War Stories From The Real World
Man, am I the only one who can never spell this one right? Seriously, “pck”? I always go for “pkg”, plus can never remember whether there’s an underscore before the “sz”.
On a more serious note, this is one I’m likely to tune. On many of our new installations, I’ve been going with the default of AUTOMATIC, but have been questioning that lately and playing with it. The reason? Often DB2 makes this so big that I gather so much information in my hourly dynamic sql snapshots that I fill up 5GB in less than two days. And the impact between this rather large size and and a much more reasonable one seems to be 1% or less in the package cache hit ratio. I’m probably going to start setting this one to a hard value on new installations going forward – I just don’t see enough benefit from the gigundo size STMM seems to be fond of.
This is one of the parameters for which the setting is a soft limit, so db2 can use more than the number you specify (instead of giving you an error). If you’re concerned this may be occuring, you can look at “Package cache high water mark” in a database snapshot.
I’ve always found the package cache to be just fine for following SQL – mostly because I work with e-commerce databases where most of the SQL is canned, and my problem SQL always involves multiple executions – so is likely to stay in the package cache. If this is not true for you, you may want to consider using the new event monitor for the package cache (http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.mon.doc/doc/c0056443.html). It apparently catches information as it leaves the package cache.
Link To Info Center
My package cache hit ratio is coming out fine to 99 % . It is not set to automatic . Package cache size (4KB) (PCKCACHESZ) = 15000
db2 get snapshot for database on xxxx| grep “Package cache”
Package cache lookups = 63917060
Package cache inserts = 159813
Package cache overflows = 132
Package cache high water mark (Bytes) = 613886467
But still in db2diag.log it is giveing overflow error as below. Overflows 132 times. Could this lead the performace issue.. or Is there any issues.
MESSAGE : ADM4500W A package cache overflow condition has occurred. There is
no error but this indicates that the package cache has exceeded the
configured maximum size. If this condition persists, you should
perform additional monitoring to determine if you need to change the
PCKCACHESZ DB configuration parameter. You could also set it to
REPORT : APM : Package Cache : info
IMPACT : Unlikely
DATA #1 : String, 274 bytes
Package Cache Overflow
memory needed : 817
current used size (OSS) : 61403058
maximum cache size (APM) : 59596800
maximum logical size (OSS): 613885331
maximum used size (OSS) : 629473280
owned size (OSS) : 75300864
number of overflows : 132
Please help thanks and Regards
hi pls look at command you mentioned for updating pckcachesz parameter. you have written dbm instead of db,
Thank you for the correction. I have updated it.