Administration Guide

Description of db2expln and dynexpln Output

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

Package information such as date of bind and relevant bind options
Section information such as the section number followed by the SQL statement being explained. Beneath the section information will be the explain output of the access plan chosen for the SQL statement shown.

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"
"Temporary Tables"
"Joins"
"Data Streams"
"Insert, Update, and Delete"
"Row Identifier (RID) Preparation"
"Aggregation"
"Parallel Processing".
"Miscellaneous Statements".

Table Access

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

Regular tables:
```
   Access Table Name = schema.name  ID = n
```
where:
- schema.name is the fully-qualified name of the table being accessed
- ID is the corresponding TABLEID column in the SYSCAT.TABLES catalog for a table
Temporary tables:
```
   Access Temp ID = tn
```
where:
- ID is the corresponding identifier assigned by db2expln

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"
"Parallel Scan"
"Scan Direction"
"Row Access Method"
"Lock Intents"
"Predicates"
"Miscellaneous Table Statements".

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:

The Relation Scan statement indicates that the table is being sequentially scanned to find the qualifying rows.
- The following statement indicates that no prefetching of data will be done:
```
   Relation Scan
   |  Prefetch: None
```
- The following statement indicates that the optimizer has predetermined the number of pages that will be prefetched:
```
   Relation Scan
   |  Prefetch: n Pages
```
- The following statement indicates that data should be prefetched:
```
   Relation Scan
   |  Prefetch: Eligible
```
- The following statement indicates that the qualifying rows are being identified and accessed through an index:
```
   Index Scan:  Name = schema.name  ID = xx
```
  where:
  - schema.name is the fully-qualified name of the index being scanned
  - ID is the corresponding IID column in the SYSCAT.INDEXES catalog view.
  The following statements are provided to clarify the type of index scan:
  - #Key Columns = n
    This statement shows the number of range-delimiting predicates, that is, the number of columns in the index key (from left to right) being used to delimit the index scan range. If #Key Columns equals zero, a full scan of the index is being performed. This might be done if the database manager decides that an index scan is cheaper than a relation scan for evaluating some of the predicates, or if the index is just being used to order the output.
  - If there are more predicates specifying where to start scanning the index than there are predicates specifying where to stop scanning the index, then the following statement will appear:
```
#Start Keys = n1
#Stop Keys = n2
```
  - Index-Only Access
    If all the needed columns can be obtained from the index key, this statement will appear and no table data will be accessed.
  - The following statement indicates that no prefetching of index pages will be done:
```
   Index Prefetch: None
```
  - The following statement indicates that index pages should be prefetched:
```
   Index Prefetch: Eligible
```
  - The following statement indicates that no prefetching of data pages will be done:
```
   Data Prefetch: None
```
  - The following statement indicates that data pages should be prefetched:
```
   Data Prefetch: Eligible
```
  - If there are predicates that can be passed to the Index Manager to help qualify index entries, the following statement is used to show the number of predicates:
```
   Sargable Index Predicate(s)
   |  #Predicates = n
```
- The Fetch Direct statement indicates that the qualifying rows are being accessed by using row IDs (RIDs) that were prepared earlier in the access plan.

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:

Exclusive
Intent Exclusive
Intent None
Intent Share
Share
Share Intent Exclusive
Super Exclusive
Update

Possible values for a row lock are:

Exclusive
Next Key Exclusive (does not appear in db2expln output)
None
Share
Next Key Share
Update
Next Key Weak Exclusive
Weak Exclusive

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

Predicates

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

The following statement indicates the number of predicates that will be evaluated once the data has been returned:
```
   Residual Predicate(s)
   |  #Predicates = n
```
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:

Applied more than once within the same query
Transformed and extended with the addition of implicit predicates during the query optimization process
Transformed and condensed into fewer predicates during the query optimization process.

Miscellaneous Table Statements

The following statement indicates that only one row will be accessed:
```
   Single Record
```
The following statement appears when the isolation level used for this table access uses a different isolation level than the package:
```
   Isolation Level: xxxx
```
A different isolation level may be used for a number of reasons, including:
- A package was bound with Repeatable Read and affects referential integrity constraints; the access of the parent table to check referential integrity constraints is downgraded to an isolation level of Cursor Stability to avoid holding unnecessary locks on this table.
- A package bound with Uncommitted Read issues a DELETE or UPDATE statement; the table access for the actual delete is upgraded to Cursor Stability.
The following statement indicates that some or all of the rows read from the temporary table will be cached outside the buffer pool if sufficient sortheap memory is available:
```
   Keep Rows In Private Memory
```

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.

The following statement indicates an ordinary temporary table will be created:
```
   Insert Into Temp Table  ID = tn
```
The following statement indicates an ordinary temporary table will be created by multiple subagents in parallel:
```
   Insert Into Shared Temp Table  ID = tn
```
The following statement indicates a sorted temporary table will be created:
```
   Insert Into Sorted Temp Table  ID = tn
```
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:

ORDER BY
DISTINCT
GROUP BY
Merge Join
'= ANY' subquery
'<> ALL' subquery
INTERSECT or EXCEPT
UNION (without the ALL keyword)

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

The following statement indicates the number of key columns used in the sort:
```
   #Sort Key Columns = n
```
For each column in the sort key, one of the following lines will be displayed:
```
   Key n: column_name (Ascending)
   Key n: column_name (Descending)
   Key n: (Ascending)
   Key n: (Descending)
```
The following statements provide estimates of the number of rows and the row size so that the optimal sort heap can be allocated at run time.
```
   Sortheap Allocation Parameters:
   |  #Rows     = n
   |  Row Width = n
```
If only the first rows of the sorted result are needed, the following is displayed:
```
   Sort Limited To Estimated Row Count
```
For sorts in a symmetric multiprocessor (SMP) environment, the type of sort to be performed is indicated by one of the following statements:
```
   Use Partitioned Sort
   Use Shared Sort
   Use Replicated Sort
   Use Round-Robin Sort
```
For a description of the different sorting techniques, see "Parallel Sort Strategies".
The following statements indicate whether or not the result from the sort will be left in the sort heap:
```
   Piped
```
and
```
   Not Piped
```
If a piped sort is indicated, the database manager will keep the sorted output in memory, rather than placing the sorted result in another temporary table. (For a description of piped versus non-piped sorts, see "Influence of Sorting on the Optimizer".)
The following statement indicates that duplicate values will be removed during the sort:
```
   Duplicate Elimination
```

If aggregation is being performed in the sort, it will be indicated by one of the following statements:

   Partial Aggregation
   Intermediate Aggregation
   Buffered Partial Aggregation
   Buffered Intermediate Aggregation

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.

Joins

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

Hash join
Merge join
Nested loop join.

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:

In some circumstances, a join simply needs to determine if any row in the inner table matches the current row in the outer. This is indicated with the statement:
```
Early Out: Single Match Per Outer Row
```
It is possible to apply predicates after the join has completed. The number of predicates being applied will be indicated as follows:
```
Residual Predicate(s)
|  #Predicates = n
```

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

The hash table is built from the inner table. If the hash table build was pushed down into a predicate on the inner table access, it is indicated by the following statement in the access of the inner table:
```
   Process Hash Table For Join
```
While accessing the outer table, a probe table can be built to improve the perfromance of the join. The probe table build is indicated by the following statement in the access of the outer table:
```
   Process Probe Table For Hash Join
```
The estimated number of bytes needed to build the hash table is represented by:
```
   Estimated Build Size: n
```
The estimated number of bytes needed for the probe table is represented by:
```
   Estimated Probe Size: n
```

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
   Join
   |  Access ..... X
   Join
   |  Access ..... Y
   Join
   |  Access ..... Z

The steps of execution would be:

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

The following statements indicate when and how the data stream is created:
```
   Evaluate at Open
   Evaluate at Application
   Forced Evaluate at Application
```
The data stream is either fully created once when it is first opened (Evaluate at Open) or each time it is accessed (Evaluate at Application). If the data stream is evaluated at application, it can be forced to be fully evaluated with each access or it can be allowed to be evaluated as required by the particular access.
Similar to a sorted temporary table, the following statements indicate whether or not the results of the data stream will be kept in memory:
```
   Piped
```
and
```
   Not Piped
```
As was the case with temporary tables, a piped data stream may be written to disk, if insufficient memory exists at execution time. The access plan will provide for both possibilities.
The following statement indicates that only a single record is required from this data stream:
```
   Single Record
```

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:

Insert: Table Name = schema.name
Update: Table Name = schema.name
Delete: Table Name = schema.name

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:

The following statement indicates that "Index ORing" is used to prepare the list of qualifying RIDs:
```
   Index ORing RID Preparation
```
Index ORing refers to the technique of making more than one index access and combining the results to include the distinct RIDs that appear in any of the indexes accessed. The optimizer will consider index ORing when predicates are connected by OR keywords or there is an IN predicate. The index accesses can be on the same index or different indexes.
Another use of RID preparation is to prepare the input data to be used during list prefetch, as indicated by the following:
```
   List Prefetch RID Preparation
```
Index ANDing refers to the technique of making more than one index access and combining the results to include RIDs that appear in all of the indexes accessed. Index ANDing processing is started with the statement:
```
   Index ANDing
```
If the optimizer has estimated the size of the result set, the estimate is shown with the following statement:
```
   Optimizer Estimate of Set Size: n
```
Index ANDing filter operations process RIDs and use bit filter techniques to determine the RIDs which appear in every index accessed. The following statements indicate that RIDs are being processed for index ANDing:
```
   Index ANDing Bitmap Build
   Index ANDing Bitmap Probe
   Index ANDing Bitmap Build and Probe
```
If the optimizer has estimated the size of the result set for a bitmap, the estimate is shown with the following statement:
```
   Optimizer Estimate of Set Size: n
```

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

   Prefetch: Enabled

Aggregation

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:

   Aggregation
   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:

Group By
Column Function(s)
Single Record.

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.

When running an intra-partition parallel plan, portions of the plan will be executed simultaneously using several subagents. The creation of the subagents is indicated by the statement:
```
Process Using n Subagents
```
When running an inter-partition parallel plan, the section is broken into several subsections. Each subsection is sent to one or more nodes to be run. An important subsection is the coordinator subsection. The coordinator subsection is the first subsection in every plan. It gets control first and is responsible for distributing the other subsections and returning results to the calling application.
The distribution of subsections is indicated by the statement:
```
Distribute Subsection #n
```
The details of how a subsection is distributed:
- Under certain circumstances, it is possible for a subsection that would normally be sent to the coordinator node to be executed directly by the coordinator subsection. If this is potentially possible, it will be indicated by:
```
Locally Bypassable
```
- The nodes that receive a subsection can be determined in one of seven ways:
```
   Directed by Hash
   |  #Columns = n
   |  Partition Map ID = n, Nodegroup = ngname, #Nodes = n
```
  This indicates that the subsection will be sent to a node within the nodegroup based on the value of the columns.
```
   Directed by Node Number
```
  This indicates that the subsection will be sent to a predetermined node. (This is frequently seen when the statement uses the NODENUMBER() function.)
```
   Directed by Partition Number
   |  Partition Map ID = n, Nodegroup = ngname, #Nodes = n
```
  This indicates that the subsection will be sent to the node corresponding to a predetermined partition number in the given nodegroup. (This is frequently seen when the statement uses the PARTITION() function.)
```
   Directed by Position
```
  This indicates that the subsection will be sent to the node that provided the current row for the application's cursor.
```
   Directed to Single Node
   |  Node Number = n
```
  This indicates that only one node, determined when the statement was compiled, will receive the subsection.
```
   Directed to Coordinator Node
```
  The subsection will be executed on the coordinator node.
```
   Broadcast to Node List
   |  Nodes = n1, n2, n3, ...
```
  This indicates that the subsection will be sent to all the nodes listed.
Table queues are used to move data between subsections in a partitioned database environment or between subagents in a symmetric multiprocessor (SMP) environment. Table queues are described as follows:
- The following statements indicate that data is being inserted into a table queue:
```
   Insert Into Synchronous Table Queue  ID = qn
   Insert Into Asynchronous Table Queue  ID = qn
   Insert Into Synchronous Local Table Queue  ID = qn
   Insert Into Asynchronous Local Table Queue  ID = qn
```
- For database partition table queues, the destination for rows inserted into the table queue is described by one of the following:
```
   Broadcast to Coordinator Node
```
  All rows are sent to the coordinator node.
```
   Broadcast to All Nodes of Subsection n
```
  All rows are sent to every database partition that the given subsection is running on.
```
   Hash to Specific Node
```
  Each row is sent to a database partition based on the values in the row.
```
   Send to Specific Node
```
  Each row is sent to a database partition determined while the statement is executing.
```
   Send to Random Node
```
  Each row is sent to a random database partition.
- In some situations, a database partition table queue will have to temporarily overflow some rows to a temporary table. This possibility is identified by the statement:
```
   Rows Can Overflow to Temporary Table
```
- After a table access that contains a push-down operation to insert rows into a table queue, there will be a "completion" statement which handles rows that could not be immediately sent. One of the following lines is displayed:
```
   Insert Into Synchronous Table Queue Completion  ID = qn
   Insert Into Asynchronous Table Queue Completion  ID = qn
   Insert Into Synchronous Local Table Queue Completion  ID = qn
   Insert Into Asynchronous Local Table Queue Completion  ID = qn
```
- The following statements indicate that data is being retrieved from a table queue:
```
   Access Table Queue  ID = qn
   Access Local Table Queue  ID = qn
```
  These messages are always followed by an indication of the number of columns being retrieved.
```
   #Columns = n
```
- If the table queue sorts the rows at the receiving end, the table queue access will also have one of the following messages:
```
   Output Sorted
   Output Sorted and Unique
```
  These messages are followed by an indication of the number of keys used for the sort operation.
```
   #Key Columns = n
```
  For each column in the sort key, one of the following is displayed:
```
   Key n: (Ascending)
   Key n: (Descending)
```
- If predicates will be applied to rows by the receiving end of the table queue, the following message is shown:
```
   Residual Predicate(s)
   |  #Predicates = n
```
Some subsections in a partitioned database environment explicitly loop back to the start of the subsection with the statement:
```
   Jump Back to Start of Subsection
```

Miscellaneous Statements

Sections for data definition language statements will be indicated in the output with the following:
```
   DDL Statement
```
No additional explain output is provided for DDL statements.
Sections for SET statements for the updatable special registers such as CURRENT EXPLAIN SNAPSHOT will be indicated in the output with the following:
```
   SET Statement
```
No additional explain output is provided for SET statements.
If the SQL statement contains the DISTINCT clause, the following text may appear in the output:
```
   Distinct Filter  #Columns = n
```
where n is the number of columns involved in obtaining distinct rows. To retrieve distinct row values, the rows must be ordered so that duplicates can be skipped. This statement will not appear if the database manager does not have to explicitly eliminate duplicates, as in the following cases:
- A unique index exists and all the columns in the index key are part of the DISTINCT operation
- Duplicates that can be eliminated during sorting.
The following statement will appear if the next operation is dependent on a specific record identifier:
```
   Positioned Operation
```
This statement would appear for any SQL statement that uses the WHERE CURRENT OF syntax.
The following statement will appear if there are predicates that must be applied to the result but that could not be applied as part of another operation:
```
   Residual Predicate Application
   |  #Predicates = n
```
The following statement will appear if there is a UNION operator in the SQL statement:
```
   UNION
```
The following statement will appear if there is an operation in the access plan, whose sole purpose is to produce row values for use by subsequent operations:
```
   Table Constructor
   |  n-Row(s)
```
Table constructors can be used for transforming values in a set into a series of rows that are then passed to subsequent operations. When a table constructor is prompted for the next row, the following statement will appear:
```
   Access Table Constructor
```
The following statement will appear if there is an operation which is only processed under certain conditions:
```
   Conditional Evaluation
   |  Condition #n:
   |  |  #Predicates = n
   |  Action #n:
```
Conditional evaluation is used to implement such activities as the SQL CASE statement or internal mechanisms such as referential integrity constraints or triggers. If no action is shown, then only data manipulation operations are processed when the condition is true.
One of the following statements will appear if an ALL, ANY, or EXISTS subquery is being processed in the access plan:
- ANY/ALL Subquery
- EXISTS Subquery
- EXISTS SINGLE Subquery
Prior to certain UPDATE and DELETE operations, it is necessary to establish the position of a specific row within the table. This is indicated by the following statement:
```
   Establish Row Position
```
The following statement will appear if there are rows being returned to the application:
```
   Return Data to Application
   |  #Columns = n
```

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

[ DB2 List of Books | Search the DB2 Books ]