Troubleshooting
Problem
If the DB2 DB CFG parameter STMTHEAP SZ is set to high, this can have a severe impact on total query runtime especially for complex SQL statements in both WebSphere Product Center or Global Data Synchronization.
Symptom
The query result set is not displayed in the Global Data Synchronization or WebSphere Product Center UI for a long time or times out.
Cause
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 sub-selects or joins.
Diagnosing 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.
1.) The slow SQL statement needs to be extracted from the logs:
In order to do this please find the details described in Technote 1383906 (http://www-01.ibm.com/support/docview.wss?uid=swg21383906).
2.) Once the slow SQL statement has been found, the next step is to check the query execution and query preparation time values.
Only if the preparation time shows a considerably larger value 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 lowering the value for the DB CFG parameter for the Statement Heap Size (STMTHEAP SZ).
To determine which time value, "prepare" or "execute", of the SQL query is slow, put the SQL statement into a file (for example, slow.sql) and perform the db2batch command as shown below:
db2batch -d <dbname> -a <userid>/ <userpasswd> -f slow.sql -i complete -o
f -1 p 2 o 5
The output of the db2batch command should be something like this:
Prepare Time is: 36.566 seconds
Execute Time is: 0.300 seconds
Fetch Time is: 0.000 seconds
Elapsed Time is: 36.866 seconds
Result:
The Prepare Time is considerably slow and much slower than the Execute Time.
- There is a 99% to 1% relationship between both values.
- The Execution Time is fast enough that the Prepare Time can be lowered without impacting the execution time too much.
Resolving The Problem
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 above.
The following test series shows the impact of the STMTHEAP SZ parameter in a real life example.
In a series with 9 different parameter values for STMTHEAP SZ, the Prepare, Execute and Fetch times have been analyzed.
STMTHEAP
1.) db2 update db cfg for pdgd1 using STMTHEAP 2048
2.) db2 update db cfg for pdgd1 using STMTHEAP 4096
3.) db2 update db cfg for pdgd1 using STMTHEAP 8192
4.) db2 update db cfg for pdgd1 using STMTHEAP 16384
5.) db2 update db cfg for pdgd1 using STMTHEAP 32768
6.) db2 update db cfg for pdgd1 using STMTHEAP 65536
7.) db2 update db cfg for pdgd1 using STMTHEAP 131072
8.) db2 update db cfg for pdgd1 using STMTHEAP 262144
9.) db2 update db cfg for pdgd1 using STMTHEAP 524288
=> Prepare Execute Fetch Elapsed
1.) 8,971 0,009 0,000 8,980
2.) 27,140 0,010 0,000 27,150
3.) 33,847 0,140 0,000 33,987
4.) 33,812 0,059 0,000 33,871
5.) 33,805 0,058 0,000 33,863
6.) 33,689 0,050 0,000 33,739
7.) 33,619 0,052 0,000 33,671
8.) 33,565 0,083 0,000 33,648
9.) 33,603 0,064 0,000 33,667
Result:
The total SQL query performance could be improved from a 33 seconds total runtime to 9 seconds.
The optimum size for the parameter has been identified to be as low as 2048 4k pages.
Conclusion:
Whenever there are slow and complex SQL statements found, and database statistics, network performance, etc. have been verified, it is worth to use this approach to tune complex SQL statements.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21388827