DB2 10.5 for Linux, UNIX, and Windows

dbheap - Database heap configuration parameter

You can use this parameter to limit the maximum amount of memory allocated for the database heap. Additional memory is automatically added for critical memory requirements.

By default, dbheap is set to AUTOMATIC, meaning that the size of the database heap can increase as needed. This increase is subject to database_memory, instance_memory, and system memory limits.

Configuration type

Database

Parameter type

Configurable online (requires a database connection)

Configurable by a member in a DB2® pureScale® environment or partitioned database environment

Propagation class

Immediate

Default [range]

On Linux and UNIX operating systems

AUTOMATIC [AUTOMATIC, 32 - 524288]

The default value is AUTOMATIC with an underlying value of 1200.

On Windows operating systems

AUTOMATIC [AUTOMATIC, 32 - 524288]

On 32-bit architectures, the default value for a standalone server is AUTOMATIC with an underlying value of 300.
On 64-bit architectures, the default value is AUTOMATIC with an underlying value of 600.

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

The underlying or fixed value of dbheap and an internal memory allowance for critical requirements is reserved from database memory at database activation. The database heap is allocated as required.

When freed

Memory allocations are freed when they are no longer needed. All allocated memory in the database heap is freed when the database is deactivated.

There is one database heap per database, and it is used for a variety of purposes critical to the support of database-wide activities. Many known requirements, such as the log buffer, are evaluated at database activation time. These internal requirements, along with the underlying or fixed configured dbheap value, contribute towards the initial database memory sizing and constitute a reservation of database memory for the database heap. Additional requirements which are not part of the internal calculation include a cache for table metadata, which accumulates as tables are accessed.

The initial database heap reservation includes both the configured value and the internally calculated requirement. This initial reservation is an enforced hard limit if the database heap is set to a fixed value. When the dbheap parameter is left at the default AUTOMATIC setting, the database heap is allowed to grow beyond the initial reservation and use any remaining database memory overflow. In addition, if the database_memory parameter is set to AUTOMATIC, then the database heap can grow further by automatically increasing the database_memory size.

The database heap requirements are dependent on a variety of factors including database design, application activity, and configuration. It is recommended that you leave the setting at AUTOMATIC with a low underlying value, such as the default value. The dbheap setting changes to AUTOMATIC when you migrate to DB2 Version 9.5 or higher from earlier releases, or when you apply the recommendations from the DB2 Configuration Advisor.

Some of the key memory requirements are:

A log buffer of size LOGBUFSZ, which is allocated at database activation. This is part of the internal database heap allowance.
Approximately 100KB is allocated for each compressed table that is accessed. If you are using table partitioning, this amount is per table partition. Up to 100MB is required for each column-based table that is accessed. These memory requirements are not included in the initial database heap sizing, and must be included in any fixed dbheap value. This metadata is cached while the database is active.
If AUDIT_BUF_SIZE is set to a non-zero value, two buffers of the specified size are allocated during database activation. This is included in the internal database heap allowance.
An allowance for two HADR buffers is made for the initial database heap reservation. For example, if DB2_HADR_BUF_SIZE is set to 256000 4KB pages, approximately 2GB is added to the initial database heap reservation from database memory. This is part of the internal database heap allowance, and occurs on both the primary and standby databases to support the resources required for failover operations.

Monitoring

The dbheap value that you configure represents only a portion of the database heap that is allocated. As documented, an extra amount is added to the database heap to account for critical requirements. Therefore, it is normal for database heap memory usage to exceed the user-configured value for the dbheap parameter.

You can monitor your database heap usage by using the MON_GET_MEMORY_POOL table function. For example, the following query:

select memory_pool_used, memory_pool_used_hwm 
from table (mon_get_memory_pool(null,null,null)) 
where memory_pool_type='DATABASE'

Returns the values of memory_pool_used and memory_pool_used_hwm in KB units:

MEMORY_POOL_USED     MEMORY_POOL_USED_HWM
-------------------- --------------------
            140574            140574

  1 record(s) selected.

You can also use the db2pd -db <database_name> -mempools command to monitor database heap usage.