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
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. Database heap requirements
beyond the initial reservation are allocated from database memory
overflow. 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. 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.