Compare performances of a query on differents db2 environments

If you have a product running on two different environments (i.e. sandbox and pre-production) using db2 as database, and you have different performance in terms of response times and you have the suspect that is related to some query, then my suggestion is first to identify what are the slowest queries analyzing the db2 snapshots (or using the monitor table functions) checking the worst cpu consumption and execution time, then run the db2 explain command on both enviroments and analyze the access plan of the queries.

For example write one of the slowest queries in a file and at the end of the file insert a terminator: @

Access to the db2 instance and run:


 db2expln -g -database DBNAME -stmtfile /tmp/query.sql -terminator @ -terminal -o /tmp/output_explain.txt


The output file contains Estimated Cost and Estimated Cardinality and the Optimizer Plan.
You can compare these values and the differences of the access plan. In case of access plan differences you can identify what are the most expensive operations. Then for example you can check if there are some indexes or views that are missing in one of the environments but already defined in the other one or simply check if you have forgotten to run the maintenance commands: reorg, runstats, rebind.