Interpreting data access by using EXPLAIN

You can use the values captured in EXPLAIN tables, and instances of PLAN_TABLE in particular, to analyze how DB2® accesses data when it processes a particular SQL statement.

Tip: Various query optimization and administration tools, such as IBM® Data Studio or IBM Data Server Manager and DB2 Query Workload Tuner for z/OS®, provide a feature called Visual Explain that enables you to create visual diagrams of the access paths for your SQL statements. To learn more about using this feature, see Generating visual representations of access plans (IBM Data Studio).

Questions for investigating data access
You can focus your investigation of access paths that DB2 uses to process SQL statements by using the PLAN_TABLE data to answer certain questions.
Table space scan access (ACCESSTYPE='R' and PREFETCH='S')
DB2 sometimes needs to use a table space scan to access the data, usually because index access is not available for some reason.
Aggregate function access (COLUMN_FN_EVAL)
The access path that is chosen for the SQL statement determines when DB2 evaluates aggregate functions.
Hash access (ACCESSTYPE='H', 'HN', or 'MH')
The ACCESSTYPE column in the plan table has a value of 'H', 'HN', or 'MH', when the table being accessed is organized by hash, and hash access is used to access the data.
Index access (ACCESSTYPE is 'I', 'IN', 'I1', 'N', 'MX', or 'DX')
If the ACCESSTYPE column in the plan table has a value of 'I', 'I1', 'IN', 'N', 'MX', or 'DX', DB2 uses an index to access the table that is named in column TNAME.
IN-list access (ACCESSTYPE='N' or 'IN')
DB2 might choose a type of IN-list access for queries that contain IN predicates.
Direct row access (PRIMARY_ACCESSTYPE='D')
If an application selects a row from a table that contains a ROWID column, the row ID value implicitly contains the location of the row. If you use that row ID value in the search condition of subsequent SELECT, DELETE, or UPDATE operations, DB2 might be able to use direct row access navigate directly to the row.
Page range screening (PAGE_RANGE='Y')
DB2 can use page range screening to limit a scan of data in a partitioned table space to one or more partitions. This access method is also sometimes called a limited partition scan.
Parallel processing access (PARALLELISM_MODE='I', 'C', or 'X')
DB2 can use parallel processing for read-only queries.
Complex trigger WHEN clause access (QBLOCKTYPE='TRIGGR')
The plan table does not report simple trigger WHEN clauses, such as WHEN (N.C1 < 5). However, the plan table does report complex trigger WHEN clauses, which are clauses that involve other base tables and transition tables.
Prefetch access paths (PREFETCH='D', 'S', 'L', or 'U')
Prefetch is a mechanism for reading a set of pages into the buffer pool with only one asynchronous I/O operation.
Sort access
DB2 can use two general types of sorts that DB2 can use when accessing data. One is a sort of data rows; the other is a sort of row identifiers (RIDs) in a RID list. You can use DB2 EXPLAIN to determine whether a SQL statement requires sort operations.
Investigating join operations
A join operation retrieves rows from more than one table and combines them. The operation specifies at least two tables, but the two tables need not be distinct.
Subquery access
The EXPLAIN output in the PLAN_TABLE sometimes shows the position and order in which subqueries are executed.
View and nested table expression access
A nested table expression is the specification of a subquery in the FROM clause of an SQL SELECT statement. The processing of table expressions is similar that for a view.
Interpreting query parallelism
You can examine plan table data to determine whether DB2 chooses access paths that take advantage of parallel processing.
Estimating the cost of SQL statements
You can use EXPLAIN to populate a statement table, owner. DSN_STATEMNT_TABLE, at the same time as your PLAN_TABLE is being populated.

Parent topic: Investigating SQL performance by using EXPLAIN

Related concepts:

Investigating SQL performance by using EXPLAIN

Related tasks:

Investigating access path problems

Generating visual representations of access plans (IBM Data Studio)

Related reference:

EXPLAIN tables

EXPLAIN

EXPLAIN bind option