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.