DB2DIAG.LOG: Package cache overfow

grofaty · #1 (**permalink**) 08-27-08, 05:29

Hi,
on db2 v8.2 fp11 on Linux there are plenty of following warnings in db2diag.log file:

Code:

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 may want to adjust the PCKCACHESZ DB
configuration parameter.

The db cfg parameter PCKCACHESZ (in 4 kB) is set to 975. What is recommended method to increase size of this parameter? Can I just double it or make it 10-times bigger?

Regards,
Grofaty

grofaty · #2 (**permalink**) 08-27-08, 06:26

Hi,
I have executed the following command:
db2 get snapshot for all on mydatabase | grep "Package cache high water mark"
and got return: 4023531 which is 982 KB.

I have update parameter to 5000 to get 19,5 MB of memory reserved for package cache.

Regards,
Grofaty

Marcus_A · #3 (**permalink**) 08-27-08, 21:06

5000 is a reasonable number.

One reason you may be gettng overflow is that the SQL statements are not prepared with parameter markers ("?"), and each one is different because of different literals in the WHERE clause. That means that the compiled SQL is not likely to be in the package cache, and the statement has to be compiled before execution.

grofaty · #4 (**permalink**) 08-28-08, 01:39

Marcus_A,
you are correct in my system 90% of SQL uses no parameter markes, so this explains why there is not enough package cache.
Thanks a lot for you post,
Grofaty

L_DBA_L · #5 (**permalink**) 11-18-09, 19:37

How determine package cache high water mark on Version 9.5? Getting overflow messages.

dr_te_z · #6 (**permalink**) 11-19-09, 06:59

Quote:

Originally Posted by grofaty

90% of SQL uses no parameter markes, so this explains

- WHY db2 performs suboptimal for you
- WHY you should speak to the application programmers, to do a better job and stop mixing DATA with SQL
- WHY you should concider an upgrade to V9.7. You will benefit a lot because 9.7 has smarter package-cache algorithms to distinguish the DATA from SQL and re-use the access-paths afterall