What to look for in Explain information

When an SQL statement is explained, it is important that the explaining takes place on the production DB2® subsystem, or at least on a Db2 subsystem where the catalog statistics have been updated to reflect the real production system in terms of table size, available indexes, and other key values. You can specify the “current server” on which Explain is to be executed. This option makes it possible that you are connected to a Db2 test subsystem while you execute the Explain on the remote production system.

The following Explain information can be useful in determining why an application does not perform as expected:
  • Access path chosen

    Table space scans and nonmatching index scans should be avoided, unless you intend to access all rows in a given table or the table is very small. If the table has one or more indexes, try to reconstruct the SQL statement in such a way that Db2 chooses a better access path. If there is no index, consider creating one.

  • Index-Only-Access

    When you only select a few column values, consider the possibility of including these few columns in the column list of one of the indexes. In this way, all requested data can be found in the index. The access path message informs you if you succeed in doing so. Likewise, if you select a maximum value, consider building a descending index on that column (or an ascending index, if you select a minimum value). In this way, you can even avoid the scanning of leaf pages in the index structure.

  • Clustering versus clustered

    If a clustering index has been chosen by Db2, ensure that the actual index is clustered. In the Index Information window, if the clustered value is NO, or if the cluster ratio is less than 95%, the table space might need a reorganization to bring the data rows into clustering sequence.

  • Number of matching columns

    On the Plan Table Data panel, if Db2 has selected a matching index scan, you should verify in the Index Information window that the number of columns used in the index is what you expect.

  • Active pages versus pages with rows

    Verify that the number of pages with rows is approximately the same as active pages, especially if you are performing table space scans. The value shown in the 1 field in the Table Information window should be as close as possible to 100 percent.

  • Number of tables per table space

    On the Table Space Information window, you should monitor the Tables field. This field shows the number of tables located in the table space. If the access path is Tablespace scan and the table space is not segmented, there should be only one table in the table space. In a nonsegmented table space, all tables are scanned, not only the selected table.

  • Host variable definitions versus column definitions

    An inconsistent definition of host variables shown in the Host Variable Definition window, compared to the corresponding column definitions shown in the Key Column Selection window, can indicate an inefficient access path selection, resulting from a possible disqualification of index usage. If, for example, an index column is defined as 3 characters, and that column is being compared in a WHERE-clause with a host variable defined as 4 characters, then Db2 does not base its access path selection on the mentioned index. You should verify that a column and a host variable being compared in a WHERE-clause have compatible definitions.