This parameter specifies the limit of the statement heap, which is used as a work space for the SQL or XQuery compiler during compilation of an SQL or XQuery statement.
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.