stmtheap - Statement heap size configuration parameter

Configuration type

Database

Parameter type

Configurable Online

Configurable by member in a Db2® pureScale® environment

Propagation class

Statement boundary

Default [range]

For 32-bit platforms

AUTOMATIC [128 - 524288]

• Database server with local and remote clients: the default value is AUTOMATIC with an underlying value of 2048.
• This parameter can also be set to a fixed value only, without the AUTOMATIC attribute.

For 64-bit platforms

AUTOMATIC [128 - 2 147 483 647]

• Database server with local and remote clients: the default value is AUTOMATIC with an underlying value of 8192.
• This parameter can be set to a fixed value only, without the AUTOMATIC attribute.

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

As required for each statement during precompiling or binding

When freed

When precompiling or binding of a statement is complete

The statement heap does not stay permanently allocated. It is allocated and released for every SQL or XQuery statement handled. For dynamic SQL or XQuery statements, the statement heap is used during the execution of your program. For static SQL or XQuery statements, the statement heap is used during the bind process, but not during program execution.

The stmtheap parameter can be set to AUTOMATIC with an underlying value or a fixed value.

When the stmtheap parameter is set to AUTOMATIC, the underlying value enforces a limit on the amount of memory allocated for a single compilation using dynamic join enumeration. If a memory limit is encountered, the statement compilation restarts using greedy join enumeration and an unlimited statement heap. With the AUTOMATIC option, while the greedy join enumeration is performed, statement compilation is only limited by the amount of remaining application memory (APPL_MEMORY), instance memory (INSTANCE_MEMORY), or system memory. If greedy join enumeration is successfully completed, an SQL0437W warning is returned to the application. If a greedy join enumeration encounters a memory limit, the statement preparation fails with SQL0101N.

For example, db2 update db cfg for SAMPLE using STMTHEAP 8192 AUTOMATIC creates a statement heap limit of 8192 * 4K (32MB) for dynamic join enumeration and no limit for greedy join enumeration.

When the stmtheap parameter is set to a fixed value, the limit applies to both dynamic and greedy join enumeration. If dynamic join enumeration encounters a memory limit, a greedy join enumeration is attempted with the same fixed statement heap limit. If greedy join enumeration is successfully completed, an SQL0437W warning is returned to the application. If a greedy join enumeration encounters a memory limit, the statement preparation fails with SQL0101N.

For example, db2 update db cfg for SAMPLE using STMTHEAP 8192 creates a statement heap limit of 8192 * 4K (32MB) for both dynamic and greedy join enumeration.

If the runtime performance of your query is not sufficient, consider increasing thestmtheap configuration parameter value (either the value underlying AUTOMATIC or a fixed value) to ensure that dynamic programming join enumeration is successful. If you update the stmtheap configuration parameter to improve the performance of a query, force the statement to recompiled. Forcing a statement to recompile might cause the query optimizer to create a new access plan that takes advantage of the new statement heap size.