catalogcache_sz - Catalog cache size configuration parameter

This parameter specifies the maximum space in pages that the catalog cache can use from the database heap.

Configuration type
Database
Parameter type
  • Configurable online
  • Configurable by member in a Db2® pureScale® environment
Propagation class
Immediate
Default [range]
32-bit platforms
-1 [((maxappls+25)*10), 8 - 524 288]
64-bit platforms
-1 [((maxappls+25)*10), 8 - 2 147 483 647]
Note: The default value is subject to change by the Db2 Configuration Advisor after initial database creation.
Unit of measure
Pages (4 KB)
When allocated
When the database is initialized
When freed
When the database is shut down

This parameter is allocated out of the database shared memory, and is used to cache system catalog information. In a partitioned database system, there is one catalog cache for each database partition.

Caching catalog information at individual database partitions allows the database manager to reduce its processing time by eliminating the need to access the system catalogs (or the catalog node in a partitioned database environment) to obtain information that has previously been retrieved. The use of the catalog cache can help improve the overall performance of:
  • Binding packages and compiling SQL and XQuery statements
  • Operations that involve checking database-level privileges, routine privileges, global variable privileges and role authorizations
  • Applications that are connected to non-catalog nodes in a partitioned database environment

By taking the default (-1) in a server or partitioned database environment, the value used to calculate the page allocation is ten times the value specified for the maxappls configuration parameter incremented by 25.

Recommendation: Start with the default value and tune it by using the database system monitor. 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.

Note: The catalog cache exists on all nodes in a partitioned database environment. Since there is a local database configuration file for each node, each node's catalogcache_sz value defines the size of the local catalog cache. In order to provide efficient caching and avoid overflow scenarios, you need to explicitly set the catalogcache_sz value at each node and consider the feasibility of possibly setting the catalogcache_sz on non-catalog nodes to be smaller than that of the catalog node; keep in mind that information that is required to be cached at non-catalog nodes will be retrieved from the catalog node's cache. Hence, a catalog cache at a non-catalog node is like a subset of the information in the catalog cache at the catalog node.

In general, more cache space is required if a unit of work contains several dynamic SQL or XQuery statements or if you are binding packages that contain a large number of static SQL or XQuery statements.