dbheap - Database heap configuration parameter
- Configuration type
- Parameter type
- Configurable online (requires a database connection)
- Configurable by a member in a Db2® pureScale® environment or partitioned database environment
- Propagation class
- Default [range]
- On Linux and UNIX operating systems
- AUTOMATIC [AUTOMATIC, 32 - 2 147 483 647]
- The default value is AUTOMATIC with an underlying value of 1200.
- On Windows operating systems
- AUTOMATIC [AUTOMATIC, 32 - 2 147 483 647]
- 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.
- 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.
AUDIT_BUF_SIZEis 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_SIZEis 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.
- Approximately 100MB is allocated up front in order to support diagnostics tooling. This is included in the internal database heap allowance.
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.
Returns the values of memory_pool_used and memory_pool_used_hwm in KB units:
select memory_pool_used, memory_pool_used_hwm from table (mon_get_memory_pool(null,null,null)) where memory_pool_type='DATABASE'
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.