Increasing IBM Db2 STMTHEAP size
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=01602What to do next
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:
- Ensure that STMTHEAP is set to AUTOMATIC.
- 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.
- 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