Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: DB2DIAG.LOG: Package cache overfow

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    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

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    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

  5. #5
    Join Date
    Sep 2009
    Posts
    66
    How determine package cache high water mark on Version 9.5? Getting overflow messages.

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by grofaty View Post
    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

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by L_DBA_L View Post
    How determine package cache high water mark on Version 9.5? Getting overflow messages.
    You can check LogHWM in db2pd -mempools

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •