Creating an access plan graph

Note:As of Version 6, Visual Explain can no longer be invoked from the command line. It can still, however, be invoked from various database objects in the Control Center. For this version, the documentation continues to use the name Visual Explain.

Overview

Visual Explain uses information from a number of sources in order to produce an access plan graph, as shown in the illustration below. Based on various inputs, the optimizer chooses an access plan, and Visual Explain displays it in an access plan graph. The nodes in the graph represent tables and indexes and each operation on them. The links between the nodes represent the flow of data.

The following list of tasks correspond to those shown in the illustration above. (Broken lines indicate steps that are required for Visual Explain.)

  1. Tune your table design and reorganize table data.
  2. Create appropriate indexes.
  3. Use the runstats command to provide the optimizer with current statistics.
  4. Choose appropriate configuration parameters.
  5. Choose appropriate bind options.
  6. Design queries to retrieve only required data.
  7. Create an access plan.
  8. Create explain snapshots.
  9. Display and use an access plan graph.

For example, to use Visual Explain, first update current statistics using the runstats command on the tables, and indexes used by the statement. These statistics, the configuration parameters, bind options, and the query itself are used by the optimizer to create an access plan and an explain snapshot when the package is bound. Visual Explain uses the resulting explain snapshot to display the access plan graph for the statement.

The following sections provide an overview of how to use Visual Explain to analyze SQL statements. For detailed information, see the DB2 books that are referenced throughout these sections.

  1. Creating an explain snapshot.
  2. Displaying and using an access plan graph.
  3. Improving the access plan.
  4. Performing SQL tuning activities.
Note:
  • The process of tuning queries is iterative, and the tasks described throughout the above sections can be repeated as required.
  • You can also use the Command Center to interactively create access plans.
  • You can use the Control Center to perform database administration tasks.
  • To see a list of Visual Explain tasks, return to the Contents and review the tasks under Analyzing SQL statements (formerly Visual Explain). These tasks include instructions on how to open to each of the Visual Explain windows.