0

On Db2/Linux_x86_64 in db2diag.log I constantly see warning about CATALOGCACHE_SZ parameter to be too small like:

2022-12-02-14.40.53.536711+060 E5979903E769          LEVEL: Warning
PID     : 4508                 TID : 140629241751296 PROC : db2sysc 0
INSTANCE: prod1                NODE : 000            DB   : MYDB
APPHDL  : 0-33700              APPID: 10.12.3.4.13429.221202134054
AUTHID  : USER1                HOSTNAME: myserver.com
EDUID   : 4958                 EDUNAME: db2agent (DB2P) 0
FUNCTION: DB2 UDB, catcache support, sqlrlc_check_available_memory, probe:100
MESSAGE : ADM4000W  A catalog cache overflow condition has occurred.  There is
          no error but this indicates that the catalog cache has exceeded the
          configured maximum size.  If this condition persists, you may want to
          adjust the CATALOGCACHE_SZ DB configuration parameter.

I have checked the recommendation:

 db2 autoconfigure apply none | grep CATALOGCACHE_SZ
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 1093     1093

and I see both: "current value" and "recommendation" have the same value 1093.

Is there any other method to determinate how big should Catalog cache size be? Or any method to do the increase like double it and see if the problem reappears.

1
  • 1
    The size of this parameter shown here is too small to think about anything different from just doubling its size and look if it helps. Unless you run your db2 on some very cheap smartphone of course :) Commented Dec 2, 2022 at 21:51

1 Answer 1

2

Few things you could try, in no particular order:

  • Ignore the warning, unless it occurs too frequently (e.g. more than few times per hour).

  • Keep doubling the size until the warning goes away, or until you realise memory is better used elsewhere.

  • Increase diaglevel to 4 and see if there are more details printed in the diagnostic log along with the warning.

  • Follow suggestions in the manual, such as

    When tuning this parameter, you should consider whether the extra memory being reserved for the catalog cache might be more effective if it was allocated for another purpose, such as the buffer pool or package cache.

    Tuning this parameter is particularly important if a workload involves many SQL or XQuery compilations for a brief period of time, with few or no compilations thereafter. If the cache is too large, memory might be wasted holding copies of information that will no longer be used.

    In an partitioned database environment, consider if the catalogcache_sz at the catalog node needs to be set larger since catalog information that is required at non-catalog nodes will always first be cached at the catalog node.

    The cat_cache_lookups (catalog cache lookups), cat_cache_inserts (catalog cache inserts), cat_cache_overflows (catalog cache overflows), and cat_cache_size_top (catalog cache high water mark) monitor elements can help you determine whether you should adjust this configuration parameter.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.