DB2 10.5 for Linux, UNIX, and Windows

util_heap_sz - Utility heap size configuration parameter

This parameter guides the amount of memory that is allocated by the database utilities.

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]

AUTOMATIC [16 - 2147483647]

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

The default value is subject to change by the DB2 Configuration Advisor as part of database creation, which is run by default in a nonpartitioned database environment. The DB2 Configuration Advisor sets the UTIL_HEAP_SZ parameter to AUTOMATIC (minimum value of 5000) unless DB2_WORKLOAD=ANALYTICS, in which case it is set to AUTOMATIC (minimum value of 1000000).

Unit of measure

Pages (4 KB)

When allocated

As required by the database manager utilities

When freed

When the utility no longer needs the memory

Utility heap usage is allowed to expand as needed beyond the configured value subject to database_memory, instance_memory, and system memory limits.

Utilities can calculate their memory usage target based on the needs of the utility and the amount of available utility heap. This target can be overridden for some utilities by specifying the amount of memory to use. For example, you can specify the amount of memory to use by providing a value for the DATA BUFFER option of the LOAD utility.

By default, the util_heap_sz parameter is set to AUTOMATIC. This AUTOMATIC setting allows utilities to consider database memory overflow as part of the available utility heap in addition to the underlying configured value. The underlying configured value represents a reservation of database memory. When the reserved amount is fully used, more utility heap memory is taken from database memory overflow.

When the util_heap_sz parameter is set to a fixed value, utilities do not consider database memory overflow as part of the available utility heap. The fixed value represents a reservation of database memory and normally guides the maximum amount of memory that is used by utilities, but does not represent a hard limit. When the reserved amount is fully used, more utility heap memory is taken from the database memory overflow. Typically the reserved amount is never fully used unless the default behavior of utilities is overridden. For example, the default behavior of utilities is overridden when BUFFER sizes are provided for the LOAD or BACKUP commands.

Recommendation: It is recommended to use the default setting of AUTOMATIC. The availability of database memory overflow under the AUTOMATIC setting satisfies the temporary needs of utilities while avoiding the need for excessive memory reservations when utilities are not running. In addition, when utilities' memory needs increase, the self-tuning memory manager (STMM) can make extra database memory overflow available to the utility heap by tuning the overall database memory configuration.

Monitoring

You can monitor your utility heap usage by using the MON_GET_MEMORY_POOL table function. For example, the following query returns the values of the memory_pool_used and memory_pool_used_hwm monitor elements:

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

The following is a sample output that is generated by running the query:

MEMORY_POOL_USED     MEMORY_POOL_USED_HWM
-------------------- --------------------
           137280            137280

  1 record(s) selected.

You can also use the db2pd command with the -db database_name and -mempools parameters to monitor utility heap usage.