DB2 10.5 for Linux, UNIX, and Windows

Parallel processing information

Executing an SQL statement in parallel (using either intrapartition or interpartition parallelism) requires some special access plan operations.

When running an intrapartition parallel plan, portions of the plan are executed simultaneously using several subagents. The creation of these subagents is indicated by the statement in output from the db2expln command:
```
   Process Using n Subagents
```
When running an interpartition parallel plan, the section is broken into several subsections. Each subsection is sent to one or more database partitions to be run. An important subsection is the coordinator subsection. The coordinator subsection is the first subsection in every plan. It acquires 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 following statement:
```
   Distribute Subsection #n
```
- The following statement indicates that the subsection will be sent to a database partition within the database partition group, based on the value of the columns.
```
   Directed by Hash
   |  #Columns = n
   |  Partition Map ID = n, Nodegroup = ngname, #Nodes = n
```
- The following statement indicates that the subsection will be sent to a predetermined database partition. (This is common when the statement uses the DBPARTITIONNUM() scalar function.)
```
   Directed by Node Number
```
- The following statement indicates that the subsection will be sent to the database partition that corresponds to a predetermined database partition number in the database partition group. (This is common when the statement uses the HASHEDVALUE scalar function.)
```
   Directed by Partition Number
   |  Partition Map ID = n, Nodegroup = ngname, #Nodes = n
```
- The following statement indicates that the subsection will be sent to the database partition that provided the current row for the application's cursor.
```
   Directed by Position
```
- The following statement indicates that only one database partition, determined when the statement was compiled, will receive the subsection.
```
   Directed to Single Node
   |  Node Number = n
```
- Either of the following statements indicates that the subsection will be executed on the coordinator database partition.
```
   Directed to Application Coordinator Node
   Directed to Local Coordinator Node
```
- The following statement indicates that the subsection will be sent to all of the listed database partitions.
```
   Broadcast to Node List
   |  Nodes = n1, n2, n3, ...
```
- The following statement indicates that only one database partition, determined as the statement is executing, will receive the subsection.
```
   Directed to Any Node
```
Table queues are used to move data between subsections in a partitioned database environment or between subagents in a symmetric multiprocessor (SMP) environment.
- 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 that are inserted into the table queue is described by one of the following statements:
  Each row is sent to the coordinator database partition:
```
   Broadcast to Coordinator Node
```
  Each row is sent to every database partition on which the given subsection is running:
```
   Broadcast to All Nodes of Subsection n
```
  Each row is sent to a database partition that is based on the values in the row:
```
   Hash to Specific Node
```
  Each row is sent to a database partition that is determined while the statement is executing:
```
   Send to Specific Node
```
  Each row is sent to a randomly determined database partition:
```
   Send to Random Node
```
- In some situations, a database partition table queue will have to overflow some rows to a temporary table. This possibility is identified by the following statement:
```
   Rows Can Overflow to Temporary Table
```
- After a table access that includes a pushdown operation to insert rows into a table queue, there is a "completion" statement that handles rows that could not be sent immediately. In this case, 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 statements are always followed by the number of columns being retrieved.
```
   #Columns = n
```
- If the table queue sorts the rows at the receiving end, one of the following statements appears:
```
   Output Sorted
   Output Sorted and Unique
```
  These statements are followed by the number of keys being used for the sort operation.
```
   #Key Columns = n
```
  For each column in the sort key, one of the following statements is displayed:
```
   Key n: (Ascending)
   Key n: (Descending)
```
- If predicates will be applied to rows at the receiving end of the table queue, the following statement appears:
```
   Residual Predicate(s)
   |  #Predicates = n
```
Some subsections in a partitioned database environment explicitly loop back to the start of the subsection, and the following statement is displayed:
```
   Jump Back to Start of Subsection
```