IBM Books

Administration Guide

Description of db2expln and dynexpln Output

In the output, the explain information for each package is broken into two parts:

The steps of an access plan, or section, will be presented in the order that the database manager executes them. Each major step will be shown as a left-justified heading with information about that step indented beneath it. The explain output for the access plan has indentation bars provided in the left margin of the output. These bars also provide the "scope" for the operation; operations at a lower (that is, further to the right) level of indentation within the same operation are processed before returning to the previous level of indentation.

It is important to remember that the access plan chosen was based on an augmented version of the original SQL statement (the one shown in the output). For example, the original statement may cause any number of triggers and constraints to be activated. As well, the SQL statement may be rewritten to an equivalent but more efficient format by the Query Rewrite component of the SQL Compiler. All of these factors are included in the information presented to the Optimizer when it determines the most efficient plan to satisfy the statement. Thus, the access plan shown in the explain output may differ substantially from the access plan that one might expect for the original SQL statement. The integrated Explain facility (see Chapter 14. "SQL Explain Facility") shows the actual SQL statement used for optimization in the form of an SQL-like statement which is created by reverse-translating the internal representation of the query.

When comparing output from db2expln or dynexpln to the output of the Explain facility, the operator ID option (-i) can be very useful. Each time db2expln or dynexpln starts processing a new operator from the Explain facility, the operator ID number will be printed to the left of the explained plan. The operator IDs can be used to match up the steps in the different representations of the access plan. Note that there is not always a one-to-one correspondence between the operators in the Explain facility output and the operations shown by db2expln and dynexpln.

The following topics describe the explain text that may be produced by db2expln and dynexpln:

Table Access

This statement tells the name and type of table being accessed. It has two formats that are used:

  1. Regular tables:
       Access Table Name =  ID = n


  2. Temporary tables:
       Access Temp ID = tn


Following the table access statement, additional statements will be provided to further describe the access. These statements will be indented under the table access statement. The possible statements are:

Number of Columns

The following statement indicates the number of columns being used from each row of the table:

   #Columns = n

Parallel Scan

The following statement indicates that the database manager will use several subagents to read from the table in parallel:

   Parallel Scan

If this text is not shown, the table will only be read from by one agent (or subagent).

Scan Direction

The following statement indicates that the database manager will read rows in a reverse order:

   Scan Direction = Reverse

If this text is not shown, the scan direction is forward, which is the default. Note that an index scan can only read data in a forward order.

Row Access Method

One of the following statements will be displayed, indicating how the qualifying rows in the table are being accessed:

Lock Intents

For each table access, the type of lock that will be acquired at the table and row levels is shown with the following statement:

   Lock Intents
   |  Table: xxxx
   |  Row  : xxxx

Possible values for a table lock are:

Possible values for a row lock are:

The explanation of these lock types is found in "Attributes of Locks".


There are two statements that provide information about the predicates used in an access plan:

  1. The following statement indicates the number of predicates that will be evaluated once the data has been returned:
       Residual Predicate(s)
       |  #Predicates = n
  2. The following statement indicates the number of predicates that will be evaluated while the data is being accessed. The count of predicates does not include push-down operations such as aggregation or sort.
       Sargable Predicate(s)
       |  #Predicates = n

The number of predicates shown in the above statements may not reflect the number of predicates provided in the SQL statement because predicates can be:

Miscellaneous Table Statements

Temporary Tables

A temporary table is used by an access plan to store data during its execution in a transient or temporary work table. This table only exists while the access plan is being executed. Generally, temporary tables are used when subqueries need to be evaluated early in the access plan, or when intermediate results will not fit in the available memory.

If a temporary table needs to be created, then one of two possible statements may appear. These statements indicate that a temporary table is to be created and rows inserted into it. The ID is an identifier assigned by db2expln for convenience when referring to the temporary table. This ID is prefixed with the letter 't' to indicate that the table is a temporary table.

  1. The following statement indicates an ordinary temporary table will be created:
       Insert Into Temp Table  ID = tn
  2. The following statement indicates an ordinary temporary table will be created by multiple subagents in parallel:
       Insert Into Shared Temp Table  ID = tn
  3. The following statement indicates a sorted temporary table will be created:
       Insert Into Sorted Temp Table  ID = tn
  4. The following statement indicates a sorted temporary table will be created by multiple subagents in parallel:
       Insert Into Sorted Shared Temp Table  ID = tn

Each of the above statements will be followed by:

   #Columns = n

which indicates how many columns are in each row being inserted into the temporary table.

Sorted Temporary Tables

Sorted temporary tables can result from such operations as:

A number of additional statements may follow the original creation statement for a sorted temporary table:

Temporary Table Completion

After a table access that contains a push-down operation to create a temporary table (that is, a create temporary table that occurs within the scope of a table access), there will be a "completion" statement, which handles end-of-file by getting the temporary table ready to provide rows to subsequent temporary table access. One of the following lines will be displayed:

   Temp Table Completion  ID = tn
   Shared Temp Table Completion  ID = tn
   Sorted Temp Table Completion  ID = tn
   Sorted Shared Temp Table Completion  ID = tn

Table Functions

Table functions are user defined functions (UDFs) that return data to the statement in the form of a table. Refer to the SQL Reference for more information about table functions. Table functions are indicated by the statement:

   Access User Defined Table Function
   |   Name = schema.funcname
   |   Language = xxxx
   |   Fenced   Deterministic   NULL Call   Disallow Parallel

The language (C, OLE, or Java) that the table function is written in is given along with the attributes of the table function.


There are three types of joins (see "Join Concepts" for a description of these joins):

When the time comes in the execution of a section for a join to be performed, one of the following statements is displayed:

   Hash Join


   Merge Join


   Nested Loop Join

It is possible for a left outer join to be performed. A left outer join is indicated by one of the following statements:

   Left Outer Hash Join


   Left Outer Merge Join


   Left Outer Nested Loop Join

For merge and nested loop joins, the outer table of the join will be the table referenced in the previous access statement shown in the output. The inner table of the join will be the table referenced in the access statement that is contained within the scope of the join statement. For hash joins, the access statements are reversed with the outer table contained within the scope of the join and the inner table appearing before the join.

For a hash or merge join, the following additional statements may appear:

For a hash join, the following additional statements may appear:

For a nested loop join, the following additional statement may appear immediately after the join statement:

   Piped Inner

This statement indicates that the inner table of the join is the result of another series of operations. This is also referred to as a composite inner.

If a join involves more than two tables, the explain steps should be read from top to bottom. For example, suppose the explain output has the following flow:

   Access ..... W
   |  Access ..... X
   |  Access ..... Y
   |  Access ..... Z

The steps of execution would be:

  1. Take a row that qualifies from W.
  2. Join row from W with (next) row from X and call the result P1 (for partial join result number 1).
  3. Join P1 with (next) row from Y to create P2.
  4. Join P2 with (next) row from Z to obtain one complete result row.
  5. If there are more rows in Z, go to step 4.
  6. If there are more rows in Y, go to step 3.
  7. If there are more rows in X, go to step 2.
  8. If there are more rows in W, go to step 1.

Data Streams

Within an access plan, there is often a need to control the creation and flow of data from one series of operations to another. The data stream concept allows a group of operations within an access plan to be controlled as a unit. The start of a data stream is indicated by the following statement:

   Data Stream n

where n is a unique identifier assigned by db2expln for ease of reference. The end of a data stream is indicated by:

   End of Data Stream n

All operations between these statements are considered part of the same data stream.

A data stream has a number of characteristics and one or more statements can follow the initial data stream statement to describe these characteristics:

When a data stream is accessed, the following statement will appear in the output:

   Access Data Stream n

Insert, Update, and Delete

The explain text for these SQL statements is self-explanatory. Possible statement text for these SQL operations can be:

Row Identifier (RID) Preparation

For some access plans, it is more efficient if the qualifying row identifiers (RIDs) are sorted and duplicates removed (in the case of index ORing) or that a technique is used to identify RIDs appearing in all indexes being accessed (in the case of index ANDing) before the actual table access is performed. There are three main uses of RID preparation as indicated by the explain statements:

For any type of RID preparation, if list prefect can be performed it will be indicated with the statement:

   Prefetch: Enabled


Aggregation is performed on those rows meeting the specified criteria, if any, provided by the SQL statement predicates. If some sort of aggregate function is to be done, one of the following statements appears:

   Predicate Aggregation
   Partial Aggregation
   Partial Predicate Aggregation
   Intermediate Aggregation
   Intermediate Predicate Aggregation
   Final Aggregation
   Final Predicate Aggregation

Predicate aggregation states that the aggregation operation has been pushed-down to be processed as a predicate when the data is actually accessed.

Beneath either of the above aggregation statements will be a indication of the type of aggregate function being performed:

The specific column function can be derived from the original SQL statement. A single record is fetched from an index to satisfy a MIN or MAX operation.

If predicate aggregation is used, then subsequent to the table access statement in which the aggregation appeared, there will be an aggregation "completion", which carries out any needed processing on completion of each group or on end-of-file. One of the following lines is displayed:

   Aggregation Completion
   Partial Aggregation Completion
   Intermediate Aggregation Completion
   Final Aggregation Completion

Parallel Processing

Executing an SQL statement in parallel (using either intra-partition or inter-partition parallelism) requires some special operations. The operations for parallel plans are described below.

Miscellaneous Statements

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]