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://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.mon.doc/doc/c0056443.html). It apparently catches information as it leaves the package cache.