DB2 Version 9.7 for Linux, UNIX, and Windows

pckcachesz - Package cache size configuration parameter

This parameter is allocated out of the database shared memory, and is used for caching of sections for static and dynamic SQL and XQuery statements on a database.

Configuration type

Database

Parameter type

Configurable online

Propagation class

Immediate

Default [range]

32-bit operating systems: Automatic [-1, 32 - 128 000]
64-bit operating systems: Automatic [-1, 32 - 2 147 483 646]

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

In a partitioned database system, there is one package cache for each database partition.

Caching packages allows the database manager to reduce its internal overhead by eliminating the need to access the system catalogs when reloading a package; or, in the case of dynamic SQL or XQuery statements, eliminating the need for compilation. Sections are kept in the package cache until one of the following occurs:

The database is shut down
The package or dynamic SQL or XQuery statement is invalidated
The cache runs out of space.

This caching of the section for a static or dynamic SQL or XQuery statement can improve performance, especially when the same statement is used multiple times by applications connected to a database. This is particularly important in a transaction processing environment.

When this parameter is set to AUTOMATIC, it is enabled for self tuning. When self_tuning_mem is set to ON, the memory tuner will dynamically size the memory area controlled by pckcachesz as the workload requirements change. Because the memory tuner trades memory resources between different memory consumers, there must be at least two memory consumers enabled for self tuning in order for self tuning to be active.

Automatic tuning of this configuration parameter will only occur when self tuning memory is enabled for the database (the self_tuning_mem configuration parameter is set to "ON.")

When this parameter is set to -1, the value used to calculate the page allocation is eight times the value specified for the maxappls configuration parameter. The exception to this occurs if eight times maxappls is less than 32. In this situation, the default value of -1 will set pckcachesz to 32.

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

Tuning this parameter is particularly important when several sections are used initially and then only a few are run repeatedly. If the cache is too large, memory is wasted holding copies of the initial sections.

The following monitor elements can help you determine whether you should adjust this configuration parameter:

pkg_cache_lookups (package cache lookups)
pkg_cache_inserts (package cache inserts)
pkg_cache_size_top (package cache high water mark)
pkg_cache_num_overflows (package cache overflows)

Note: The package cache is a working cache, so you cannot set this parameter to zero. There must be sufficient memory allocated in this cache to hold all sections of the SQL or XQuery statements currently being executed. If there is more space allocated than currently needed, then sections are cached. These sections can simply be executed the next time they are needed without having to load or compile them.

The limit specified by the pckcachesz parameter is a soft limit. This limit can be exceeded, if required, if memory is still available in the database shared set. You can use the pkg_cache_size_top monitor element to determine the largest that the package cache has grown, and the pkg_cache_num_overflows monitor element to determine how many times the limit specified by the pckcachesz parameter has been exceeded.