On collecting the average query execution duration and access plan for the DB2 database
Aleksander Kovač 270004M7HS Visits (2413)
When a client reports a performance issue, or I stumble upon it myself, I sometimes find it necessary to collect specific database related information for the purpose of identifying the problem, or deciding if the DB2 support team should continue to resolve it. Most commonly, I am interested in the average query execution duration and access plan; the first one to confirm that the execution duration really is a repeatable problem, and the second one to examine if there are any obvious optimization possibilities, such as adding an index to avoid table scanning. A template for collecting both is presented below:
To collect the average query execution duration and access plan, the following commands must be executed in sequence (explain data from the first command is extracted in the second command):
db2batch -d <dbname> -f bad_query.sql -c off -iso cs -z bad_query.rpt -o e yes f -1 r 0 p 5
db2exfmt -d <dbname> -1 -o bad_query.exp
bad_query.sql file contains the parameterized query, and parameter values:
select x, z from y where x in (?, ?) and z = ?;
The db2batch command collects detailed performance information (p 5), fetches all the rows (f -1), but does not print them to the output (r 0). The query is executed 5 times (BGBLK & EOBLK), to ensure the response is consistent, using parameter markers in the predicates. I have also added the "e yes" option to prepare the explain data. db2exfmt command formats the contents of the explain tables using the default values (-1) to a file.
Once the commands complete successfully, the query performance data is stored in the bad_query.rpt file, while the access plan and other explain data can be found in the bad_query.exp file.
- Make sure that the data is collected with parameterized query; do not take shortcuts by introducing fixed predicate values otherwise not present in the production query. Fixed predicate values might produce different access plan that would otherwise be used, which will also affect the execution duration.
- Before relying on the data, validate that the "Original Statement" section in the bad_query.exp matches the query in the bad_query.sql file.
Information Center references for the DB2 commands are below: