Complex SQL statements are running very slow

If the Db2 database configuration parameter STMTHEAP SZ is set to high, this can have a severe impact on total query runtime especially for complex SQL statements in both Product Master or Global Data Synchronization.

Symptoms

The Db2 database configuration value found in the database configuration file for the parameter STMTHEAP SZ (Statement Heap size) was set to high, which causes the Db2 Optimizer to spend too much time on query preparation, resulting in a high preparation time, especially if the SQL statement is complex such as using many subselects or joins.

Resolving the problem

The following steps are based on the assumption that the slow return of the SQL query result set in the UI is caused by a slow SQL statement. Furthermore it is assumed, that the database statistics are all updated.

Once the slow SQL statement is there, you need to check the query execution and query preparation time values.

Only if the preparation time shows a considerably larger value that is compared to the execution time, then there is a strong indication that the Db2 query optimizer is spending too much time to prepare the SQL statement and that there is a good chance that this can be reduced considerably by decreasing the value for the DB configuration parameter for the Statement Heap Size (STMTHEAP SZ). To determine which time value, "prepare" or "run", of the SQL query is slow, put the SQL statement into a file (for example, slow.sql) and perform the db2batch command as following:

db2batch -d <dbname> -a <userid>/ <userpasswd>  -f  slow.sql -i complete -o  f -1 p 2 o 5

To resolve the problem, it is useful to iteratively change the STMTHEAP SZ parameter and test the query performance after each iteration with the db2batch command as shown previously. So, whenever there are slow and complex SQL statements found, and database statistics, network performance, and so on, have been verified, it is worth to use this approach to tune complex SQL statements.