Db2 SQL Performance Analyzer cost summary report

The SQL PA cost summary report provides a quick summary of costs.

Each SQL PA operation produces a minimal set of results, which contain costing information about each query examined by SQL PA. In Batch mode, these results are printed to SYSPRINT and appear on your output listing. In TSO mode, the results are written to the standard file userid.ANLCOST.LOG, always allocated by the ANLCANLI CLIST during processing. Under QMF, the interceptor program returns the same cost summary report format to the user, for all queries or only the queries that exceed the resource thresholds of the host system. The Db2 SQL Performance Analyzer stored procedure returns the same information in its output parameters.

The Db2 SQL Performance Analyzer cost summary report contains three cost descriptions to accommodate your needs:

  1. The cost in resource consumption terms (CPU time)
  2. The cost in QUNITS (Query Units)
  3. The cost in monetary terms

Technical users might want CPU time (TCB and SRB) and I/O counts, to fine-tune queries by costs. Other users might prefer a single QUNITS number that sums up the total cost of each query. Or you might be interested in budgets, and prefer to see the costing in monetary terms. All three forms are always available through Db2 SQL Performance Analyzer, so the choice and preference is up to you.

A sample of the Cost Summary Report is shown in the following figure.

Figure 1. SQL PA cost summary report
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* 
 * Query:  100000292 has been estimated by the Db2 Optimizer * 
 * to consume     0.02400 Seconds of central processing time * 
 * which is equivalent to       2585 resource service units. * 
 * Estimated cost of CPU processing: $       0.0033 DOLLARS  * 
 *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
 * SQL is Category B (NO STATS), so costs may be incomplete. *
 * Category B indicates that some condition exists for which *
 * Db2 was forced to use default values for its estimate.    *
 * In these cases, the Db2 optimizer cannot give a complete  *
 * assessment of cost.                                       *
 *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*