This parameter indicates the maximum amount of space that the catalog cache can use from the database heap (dbheap). The catalog cache is used to store table descriptor information that is used when a table, view or alias is referenced during the compilation of an SQL statement.
Use of this cache can help improve performance of binding SQL statements (including dynamic SQL), if the same tables, views, or aliases have been referenced in previous statements. Descriptor information for declared temporary tables is not stored in the catalog cache; instead the application control heap is used.
Running any DDL statements against a table will purge that table's entry in the catalog cache. Otherwise a table entry is kept in the cache until space is needed for a different table, but it will not be removed from the cache until any units of work referencing that table have completed.
Recommendation: Start with the default value and tune it by using the database system monitor.
See the following for more information:
These database system monitor elements can help you determine whether you should adjust this configuration parameter. When tuning this parameter, you should increase it in small increments, for example, two pages at a time.
| Note: | The catalog cache only exists at the catalog node in a multinode environment. |
In general, more cache space is required if a unit of work contains several dynamic SQL statements or if you are binding packages that contain a lot of static SQL statements.
When you set the size of the catalog cache, also consider the size of the log files (logbufsz), because both catalogcache_sz and logbufsz are allocated from the database heap (dbheap).