Increasing IBM Db2 STMTHEAP size

Edit online

In a complex ELM environment with configuration management enabled, Db2 might display a warning while executing a complex query. Note that this is not a failure condition, but just a warning. Db2 executed the query successfully. However, if you want to avoid this warning, you can change the Db2 STMTHEAP configuration for the application.

Example

The following is an example error:

CLI warning in preparing the SQL statement: (437): [IBM][CLI Driver][DB2/LINUXX8664] SQL0437W Performance of this complex query might be sub-optimal. Reason code: "1". SQLSTATE=01602

What to do next

You can increase the Db2 STMTHEAP size to avoid getting the warning.

For more information, see the Db2 reference document for STMTHEAP: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.admin.config.doc/doc/r0000277.html

Perform the following steps to increase the Db2 STMTHEAP size:
  1. Ensure that STMTHEAP is set to AUTOMATIC.
  2. Change the STMTHEAP value from the default (8192 pages of 4k each) to a higher number as per your environment and depending on your system’s overall memory and the application’s heap size.
    Note: The limit for 32-bit systems is 5,24,288. The limit for 64-bit systems is 2,147,483,647.
  3. Restart Db2 after updating this value.

    Note that the value being set here is the limit and not the initial value for statement heap.

    For example:
    db2 update db cfg for SAMPLE using STMTHEAP 81920 AUTOMATIC
db2stop
db2start