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 22, 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:
This statement tells the name and type of table being accessed. It has two formats that are used:
Access Table Name = schema.name ID = ts,n
where:
Note: | Access Table Name could also have been Access Hierarchy Table Name or Access Summary Table Name. |
Access Temp ID = tn
where:
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:
The following statement indicates the number of columns being used from each row of the table:
#Columns = n
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).
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.
One of the following statements will be displayed, indicating how the qualifying rows in the table are being accessed:
Relation Scan | Prefetch: None
Relation Scan | Prefetch: n Pages
Relation Scan | Prefetch: Eligible
Index Scan: Name = schema.name ID = xx | Index Columns:
where:
This will be followed by one row for each column in the index. Each column in the index will be listed in one of the following forms:
n: column_name (Ascending) n: column_name (Descending) n: column_name (Include Column)
The following statements are provided to clarify the type of index scan:
#Key Columns = n | Start Key: xxxxx | Stop Key: xxxxx
Where xxxxx is one of:
An inclusive key value will be included in the index scan. An exclusive key value will not be included in the scan. The value for the key will be given by one of the following rows for each part of the key:
n: 'string' n: nnn n: yyyy-mm-dd n: hh:mm:ss n: yyyy-mm-dd hh:mm:ss.uuuuuu n: NULL n: ?
If a literal string is shown, on the first 20 characters are displayed. If the string is longer than 20 characters, this will be shown by ... at the end of the string. Some keys cannot be determined until the section is executed. This is shown by a ? as the value.
If all the needed columns can be obtained from the index key, this statement will appear and no table data will be accessed.
Index Prefetch: None
Index Prefetch: Eligible
Data Prefetch: None
Data Prefetch: Eligible
Sargable Index Predicate(s) | #Predicates = n
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:
Residual Predicate(s) | #Predicates = n
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:
Single Record
Isolation Level: xxxxA different isolation level may be used for a number of reasons, including:
Keep Rows In Private Memory
Volatile Cardinality
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.
Insert Into Temp Table ID = tn
Insert Into Shared Temp Table ID = tn
Insert Into Sorted Temp Table ID = tn
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 can result from such operations as:
A number of additional statements may follow the original creation statement for a sorted temporary table:
#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)
Sortheap Allocation Parameters: | #Rows = n | Row Width = n
Sort Limited To Estimated Row Count
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.
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.)
Duplicate Elimination
Partial Aggregation Intermediate Aggregation Buffered Partial Aggregation Buffered Intermediate Aggregation
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 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
or
Merge Join
or
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
or
Left Outer Merge Join
or
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:
Early Out: Single Match Per Outer Row
Residual Predicate(s) | #Predicates = n
For a hash join, the following additional statements may appear:
Process Hash Table For Join
Process Probe Table For Hash Join
Estimated Build Size: n
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:
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:
Evaluate at Open Evaluate at Application Forced Evaluate at ApplicationThe 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.
Pipedand
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.
Single Record
When a data stream is accessed, the following statement will appear in the output:
Access Data Stream n
The explain text for these SQL statements is self-explanatory. Possible statement text for these SQL operations can be:
Note: | The Table Name in each case could also have been Hierarchy Table Name or Summary Table Name. |
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:
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.
List Prefetch RID Preparation
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 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:
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
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.
Process Using n Subagents
The distribution of subsections is indicated by the statement:
Distribute Subsection #n
The nodes that receive a subsection can be determined in one of eight ways:
Directed by Hash | #Columns = n | Partition Map ID = n, Nodegroup = ngname, #Nodes = n
Directed by Node Number
Directed by Partition Number | Partition Map ID = n, Nodegroup = ngname, #Nodes = n
Directed by Position
Directed to Single Node | Node Number = n
Directed to Coordinator Node
Broadcast to Node List | Nodes = n1, n2, n3, ...
Directed to Any Node
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
Broadcast to Coordinator NodeAll rows are sent to the coordinator node.
Broadcast to All Nodes of Subsection nAll rows are sent to every database partition that the given subsection is running on.
Hash to Specific NodeEach row is sent to a database partition based on the values in the row.
Send to Specific NodeEach row is sent to a database partition determined while the statement is executing.
Send to Random NodeEach row is sent to a random database partition.
Rows Can Overflow to Temporary Table
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
Access Table Queue ID = qn Access Local Table Queue ID = qnThese messages are always followed by an indication of the number of columns being retrieved.
#Columns = n
Output Sorted Output Sorted and UniqueThese messages are followed by an indication of the number of keys used for the sort operation.
#Key Columns = nFor each column in the sort key, one of the following is displayed:
Key n: (Ascending) Key n: (Descending)
Residual Predicate(s) | #Predicates = n
Jump Back to Start of Subsection
DDL StatementNo additional explain output is provided for DDL statements.
SET StatementNo additional explain output is provided for SET statements.
Distinct Filter #Columns = nwhere 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:
Positioned OperationThis statement would appear for any SQL statement that uses the WHERE CURRENT OF syntax.
Residual Predicate Application | #Predicates = n
UNION
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
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.
Establish Row Position
Return Data to Application | #Columns = nIf the operation was pushed-down into a table access, it will require a completion phase. This phase appears as:
Return Data Completion