DB2 Version 9.7 for Linux, UNIX, and Windows

query_heap_sz - Query heap size configuration parameter

This parameter specifies the size of the statement heap, which is used as a queue by the query agent while queries are being issued.

Important: This parameter is deprecated in Version 9.5 and might be removed in a future release. This parameter can still be used in pre-Version 9.5 data servers and clients. In Version 9.5 and later releases, the value specified for this configuration parameter is ignored.
Configuration type
Database manager
Applies to
  • Database server with local and remote clients
  • Database server with local clients
  • Partitioned database server with local and remote clients
Parameter type
Default [range]
1 000 [2 - 524 288 ]
Unit of measure
Pages (4 KB)
When allocated
When an application (either local or remote) connects to the database
When freed
When the application disconnects from the database, or detaches from the instance

This parameter specifies the maximum amount of memory that can be allocated for the query heap, ensuring that an application does not consume unnecessarily large amounts of virtual memory within an agent.

A query heap is used to store each query in the agent's private memory. The information for each query consists of the input and output SQLDA, the statement text, the SQLCA, the package name, creator, section number, and consistency token.

The query heap is also used for the memory allocated for blocking cursors. This memory consists of a cursor control block and a fully resolved output SQLDA.

The initial query heap allocated will be the same size as the application support layer heap, as specified by the aslheapsz parameter. The query heap size must be greater than or equal to two (2), and must be greater than or equal to the aslheapsz parameter. If this query heap is not large enough to handle a given request, it will be reallocated to the size required by the request (not exceeding query_heap_sz). If this new query heap is more than 1.5 times larger than aslheapsz, the query heap will be reallocated to the size of aslheapsz when the query ends.

Recommendation: In most cases the default value will be sufficient. As a minimum, you should set query_heap_sz to a value at least five times larger than aslheapsz. This will allow for queries larger than aslheapsz and provide additional memory for three or four blocking cursors to be open at a given time.

If you have very large LOBs, you might need to increase the value of this parameter so the query heap will be large enough to accommodate those LOBs.