Temporary table information

A temporary table is used as a work table during access plan execution. Generally, temporary tables are used when subqueries need to be evaluated early in the access plan, or when intermediate results will not fit into the available memory.

If a temporary table is needed, one of the following statements will appear in db2expln command output.

   Insert Into Temp Table  ID = tn     --> ordinary temporary table
   Insert Into Shared Temp Table  ID = tn     --> ordinary temporary table will be created
                                                  by multiple subagents in parallel
   Insert Into Sorted Temp Table  ID = tn     --> sorted temporary table
   Insert Into Sorted Shared Temp Table  ID = tn     --> sorted temporary table will be created
                                                         by multiple subagents in parallel

   Insert Into Global Temp Table  ID = ts,tn     --> declared global temporary table
   Insert Into Shared Global Temp Table  ID = ts,tn     --> declared global temporary table
                                                            will be created by multiple subagents
                                                            in parallel
   Insert Into Sorted Global Temp Table  ID = ts,tn     --> sorted declared global temporary table
   Insert Into Sorted Shared Global Temp Table  ID = ts,tn     --> sorted declared global temporary
                                                                   table will be created by
                                                                   multiple subagents in parallel

The ID is an identifier that is 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.

Each of these statements is followed by:

   #Columns = n

which indicates how many columns there are in each row that is 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 statements that are associated with a sorted temporary table can appear in db2expln command output.

The following statement indicates the number of key columns that are used in the sort:
```
   #Sort Key Columns = n
```
One of the following lines is displayed for each column in the sort key:
```
   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
```
The following statement is displayed if only the first rows of the sorted result are needed:
```
   Sort Limited To Estimated Row Count
```
For sorts that are performed in a symmetric multiprocessor (SMP) environment, the type of sort that is to be performed is indicated by one of the following statements:
```
   Use Partitioned Sort
   Use Shared Sort
   Use Replicated Sort
   Use Round-Robin Sort
```
The following statements indicate whether or not the sorted result will be left in the sort heap:
```
   Piped
   Not Piped
```
If a piped sort is indicated, the database manager will keep the sorted output in memory, rather than placing it in another temporary table.
The following statement indicates that duplicate values will be removed during the sort operation:
```
   Duplicate Elimination
```

If aggregation is being performed during the sort operation, one of the following statements is displayed:

   Partial Aggregation
   Intermediate Aggregation
   Buffered Partial Aggregation
   Buffered Intermediate Aggregation

Temporary table completion

A completion statement is displayed whenever a temporary table is created within the scope of a table access. This statement can be one of the following:

   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. A table function is indicated by the following statements, which detail the attributes of the function. The specific name uniquely identifies the table function that is invoked.

   Access User Defined Table Function
   |  Name = schema.funcname
   |  Specific Name = specificname
   |  SQL Access Level = accesslevel
   |  Language = lang
   |  Parameter Style = parmstyle
   |  Fenced                       Not Deterministic
   |  Called on NULL Input         Disallow Parallel
   |  Not Federated                Not Threadsafe