DB2 10.5 for Linux, UNIX, and Windows

Miscellaneous explain information

Output from the db2expln command contains additional useful information that cannot be readily classified.

Sections for data definition language (DDL) statements are indicated in the output with the following statement:
```
   DDL Statement
```
No additional explain output is provided for DDL statements.
Sections for SET statements pertaining to updatable special registers, such as CURRENT EXPLAIN SNAPSHOT, are indicated in the output with the following statement:
```
   SET Statement
```
No additional explain output is provided for SET statements.
If the SQL statement contains a DISTINCT clause, the following statement might 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 first be sorted to eliminate duplicates. 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 of the columns in the index key are part of the DISTINCT operation
- Duplicates can be eliminated during sorting
The following statement appears if a partial early distinct (PED) operation was performed to remove many, if not all, duplicates. This reduces the amount of data that must be processed later in the query evaluation.
```
Hashed partial distinct filter
```
The following statement appears if the next operation is dependent on a specific record identifier:
```
   Positioned Operation
```
If the positioned operation is against a federated data source, the statement becomes:
```
   Distributed Positioned Operation
```
This statement appears for any SQL statement that uses the WHERE CURRENT OF syntax.
The following statement appears 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 appears if the SQL statement contains a UNION operator:
```
   UNION
```
The following statement appears 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 appears:
```
   Access Table Constructor
```
The following statement appears if there is an operation that is only processed under certain conditions:
```
   Conditional Evaluation
   |  Condition #n:
   |  #Predicates = n
   |  Action #n:
```
Conditional evaluation is used to implement such activities as the 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 appears 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 or 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 appears if rows are being returned to the application:
```
   Return Data to Application
   |  #Columns = n
```
If the operation was pushed down into a table access, a completion phase statement appears in the output:
```
   Return Data Completion
```

The following statements appear if a stored procedure is being invoked:

   Call Stored Procedure
   |   Name = schema.funcname
   |   Specific Name = specificname
   |   SQL Access Level = accesslevel
   |   Language = lang
   |   Parameter Style = parmstyle
   |   Expected Result Sets = n
   |   Fenced                       Not Deterministic
   |   Called on NULL Input         Disallow Parallel
   |   Not Federated                Not Threadsafe

The following statement appears if one or more large object (LOB) locators are being freed:
```
   Free LOB Locators
```