Performance Tuning for Content Manager

Chapter 8. Tuning DB2 for Content Manager 219

Default values (range)

Automatic (32 - 524,288) 4-KB pages

To update

Run the following DB2 command:

db2 update db cfg for <database name> using DBHEAP <parameter value>

Our recommendation

Use the default values to start with. As you increase related variables such as

CATALOGCACHE_SZ and LOGBUFSZ, you will have to increase DBHEAP. Use

the database system monitor to track the highest amount of memory that was

used for the database heap, using the DB_HEAP_TOP (maximum database

heap allocated) element.

8.6.16 Catalog cache size (CATALOGCACHE_SZ)

Impact

DB - High

Description

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 partitions enables the database

manager to reduce its internal overhead by eliminating the need to access the

system catalogs (or the catalog node in an partitioned database environment) to

obtain information that has previously been retrieved.

The catalog cache is used to store:

 SYSTABLES information (including packed descriptors)

 Authorization information, including SYSDBAUTH information and execute

privileges for routines

 SYSROUTINES information

The use of the catalog cache can help improve the overall performance of:

 Binding packages and compiling SQL statements

 Operations that involve checking database-level privileges

 Operations that involve checking execute privileges for routines

220 Performance Tuning for Content Manager

 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 that is used to calculate the page allocation is four times the MAXAPPLS

configuration parameter. The exception to this occurs if four times MAXAPPLS is

less than 8. In this situation, the default value of -1 will set CATALOGCACHE_SZ

to 8.

Default values (range)

-1 (8 – 524,288) 4-KB pages

To update

Run the following DB2 command:

db2 update db cfg for <database name> using CATALOGCACHE_SZ <parameter

value>

Our recommendation

Start with the default value (-1).

Use the database system monitor (snapshots) to determine whether this number

needs to be increased. The snapshot monitor elements of importance are:

 Catalog cache lookups (cat_cache_lookups): the number of times that the

catalog cache was referenced to obtain table descriptor or authorization

information.

 Catalog cache inserts (cat_cache_inserts): the number of times that the

system tried to insert table descriptor or authorization information into the

catalog cache.

 Catalog cache overflows (cat_cache_overflows): the number of times that

the catalog cache overflowed the bounds of its allocated memory. If the

number of overflows is large, the catalog cache might be too small for the

workload and should be increased.

The catalog cache hit ratio indicates how well the catalog cache is avoiding

catalog accesses. A ratio of above 80% indicates that the cache is performing

well. The formula to calculate the catalog cache hit ratio is:

(1 - (cat_cache_inserts / cat_cache_lookups)) * 100

Figure 8-2 on page 221 shows an example of a Lotus 1-2-3 spreadsheet using

the snapshot elements to compute catalog cache and package size hit ratio.