Db2 SQL Performance Analyzer architecture and process flow

Db2 SQL Performance Analyzer (SQL PA) processes information from various input sources based on user and system parameters that you specify and then produces a series of reports that you use in your analysis.

SQL PA firsts reads and sets user and system-related parameters, and then reads SQL statements. The source of the SQL statements can be any of the supported input sources, such as a single DBRM or a whole DBRM library, a PLAN or PACKAGE from the system catalog, SQL that is contained in a sequential or partitioned data set, or SQL that is embedded in source code.

For each of the SQL statements, SQL PA determines if a plan exists. If a plan does not exist, SQL PA completes the following steps:
  1. Performs a Db2® PREPARE and EXPLAIN to create the necessary plan information.
  2. Fetches the rows from various EXPLAIN tables for analysis.
  3. Fetches the system catalog statistics that are associated with Db2 objects that are being analyzed.
  4. Analyzes the information collected.
  5. Produces a number of reports.
The following figure shows the basic process flow for Db2 SQL Performance Analyzer.
Figure 1. The Db2 SQL Performance Analyzer process

The Db2 SQL Performance Analyzer process