Configuring statement heap size

Statement heap size configuration parameter (stmtheap) refers to the limit of the statement heap, which is used as a workspace for the SQL compiler during compilation of an SQL statement. Statement heap size has an impact on the time taken to execute complex queries, for example generating the audit snapshot.

Allocating small statement heap size for complex queries causes the query to be not fully optimized and the execution lakes longer. It also consumes excessive amount of disk space. This scenario occurs in databases that have large amount of data. On the other hand, allocating large statement heap size may cause in longer execution time for complex queries even if the database has small amount of data. It may be helpful to adjust the statement heap size for large environments, if there are queries that run for a long time, or use large amount of disk space.

Use the below command to define the statement heap size:
db2 update db config for TEMADB using stmtheap 81920 automatic
Note: Restart BigFix Inventory application and DB2 database after you change the statement heap size.