Administration Guide

Examples of db2expln and dynexpln Output

Four examples are shown here to help understand the layout and format of the output from db2expln and dynexpln. These examples were run against the SAMPLE database as provided with DB2. A brief discussion is provided for each example. Significant differences from one example to the next have been shown in bold.

Example One: No Parallelism Plan

This example is simply requesting a list of all employee names, their jobs, department name and location, and the project name(s) on which they are working. The essence of this access plan is that merge joins are used to join the relevant data from each of the specified tables. Since no indexes are available, the access plan does a relation scan of each table, and each table must be sorted before it can be joined.

******************** PACKAGE ***************************************
 
Package Name = QUERY.DYNEXPLN
	Prep Date = 1999/03/12
	Prep Time = 11:36:00:054
 
	Bind Timestamp = 1999-03-12-11.36.00.546992
 
	Isolation Level          = Cursor Stability
	Blocking                 = Block Unambiguous Cursors
	Query Optimization Class = 5
 
	Partition Parallel       = No
	Intra-Partition Parallel = No
 
	Function Path            = "SYSIBM", "SYSFUN", "QUERY"
 
-------------------- SECTION ---------------------------------------
Section = 1
 
 
SQL Statement:  
  
  SELECT x.lastname, x.job, y.deptname, y.location, z.projname 
  FROM employee AS x, department AS y, project AS z 
  WHERE x.workdept = y.deptno AND x.workdept = z.deptno AND y.deptno 
          = z.deptno 
 
 
Estimated Cost        = 126
Estimated Cardinality = 153
 
Access Table Name = QUERY.DEPARTMENT  ID = 2,4
|  #Columns = 3
|  Relation Scan
|  |  Prefetch: Eligible
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Insert Into Sorted Temp Table  ID = t1
|  |  #Columns = 3
|  |  #Sort Key Columns = 1
|  |  |  Key 1: DEPTNO (Ascending)
|  |  Sortheap Allocation Parameters:
|  |  |  #Rows     = 40
|  |  |  Row Width = 48
|  |  Piped
Sorted Temp Table Completion  ID = t1
Access Temp Table  ID = t1
|  #Columns = 3
|  Relation Scan
|  |  Prefetch: Eligible
Merge Join
|  Access Table Name = QUERY.PROJECT  ID = 2,7
|  |  #Columns = 2
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Insert Into Sorted Temp Table  ID = t2
|  |  |  #Columns = 2
|  |  |  #Sort Key Columns = 1
|  |  |  |  Key 1: DEPTNO (Ascending)
|  |  |  Sortheap Allocation Parameters:
|  |  |  |  #Rows     = 38
|  |  |  |  Row Width = 28
|  |  |  Piped
|  Sorted Temp Table Completion  ID = t2
|  Access Temp Table  ID = t2
|  |  #Columns = 2
|  |  Relation Scan
|  |  |  Prefetch: Eligible
Merge Join
|  Access Table Name = QUERY.EMPLOYEE  ID = 2,5
|  |  #Columns = 3
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Insert Into Sorted Temp Table  ID = t3
|  |  |  #Columns = 3
|  |  |  #Sort Key Columns = 1
|  |  |  |  Key 1: WORKDEPT (Ascending)
|  |  |  Sortheap Allocation Parameters:
|  |  |  |  #Rows     = 63
|  |  |  |  Row Width = 32
|  |  |  Piped
|  Sorted Temp Table Completion  ID = t3
|  Access Temp Table  ID = t3
|  |  #Columns = 3
|  |  Relation Scan
|  |  |  Prefetch: Eligible
Return Data to Application
|  #Columns = 5
 
End of section
 
 
Optimizer Plan:
 
               RETURN 
               (   1) 
                 |    
               MSJOIN 
               (   2) 
              /      \
        MSJOIN         TBSCAN 
        (   3)         (  12) 
       /      \          |    
   TBSCAN    TBSCAN     SORT  
   (   4)    (   8)    (  13) 
     |         |         |    
    SORT      SORT     TBSCAN 
   (   5)    (   9)    (  14) 
     |         |         |    
   TBSCAN    TBSCAN   Table:   
   (   6)    (  10)   QUERY   
     |         |      EMPLOYEE 
 Table:      Table:  
 QUERY       QUERY  
 DEPARTMENT  PROJECT

The first part of the plan accesses the DEPARTMENT and PROJECT tables and uses a merge join to join them. The result of this join is joined to the EMPLOYEE table. The resulting rows are returned to the application.

Example Two: Single-Partition Database Plan with Intra-Partition Parallelism

This example shows the same SQL statement as Example One: No Parallelism Plan, but this query has been compiled for a 4-way SMP machine.

******************** PACKAGE ***************************************
 
Package Name = QUERY.DYNEXPLN
	Prep Date = 1999/03/12
	Prep Time = 11:41:30:024
 
	Bind Timestamp = 1999-03-12-11.41.30.249850
 
	Isolation Level          = Cursor Stability
	Blocking                 = Block Unambiguous Cursors
	Query Optimization Class = 5
 
	Partition Parallel       = No
	Intra-Partition Parallel = Yes (Bind Degree = 4)
 
	Function Path            = "SYSIBM", "SYSFUN", "QUERY"
 
-------------------- SECTION ---------------------------------------
Section = 1
 
 
SQL Statement:  
  
  SELECT x.lastname, x.job, y.deptname, y.location, z.projname 
  FROM employee AS x, department AS y, project AS z 
  WHERE x.workdept = y.deptno AND x.workdept = z.deptno AND y.deptno 
          = z.deptno 
 
Intra-Partition Parallelism Degree = 4
 
Estimated Cost        = 142
Estimated Cardinality = 153
 
Process Using 4 Subagents
|  Access Table Name = QUERY.DEPARTMENT  ID = 2,4
|  |  #Columns = 3
|  |  Parallel Scan
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Insert Into Sorted Shared Temp Table  ID = t1
|  |  |  #Columns = 3
|  |  |  #Sort Key Columns = 1
|  |  |  |  Key 1: DEPTNO (Ascending)
|  |  |  Use Round-Robin Sort
|  |  |  Sortheap Allocation Parameters:
|  |  |  |  #Rows     = 40
|  |  |  |  Row Width = 48
|  |  |  Piped
|  Sorted Shared Temp Table Completion  ID = t1
|  Access Temp Table  ID = t1
|  |  #Columns = 3
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  Merge Join
|  |  Access Table Name = QUERY.PROJECT  ID = 2,7
|  |  |  #Columns = 2
|  |  |  Parallel Scan
|  |  |  Relation Scan
|  |  |  |  Prefetch: Eligible
|  |  |  Lock Intents
|  |  |  |  Table: Intent Share
|  |  |  |  Row  : Next Key Share
|  |  |  Insert Into Sorted Shared Temp Table  ID = t2
|  |  |  |  #Columns = 2
|  |  |  |  #Sort Key Columns = 1
|  |  |  |  |  Key 1: DEPTNO (Ascending)
|  |  |  |  Use Replicated Sort
|  |  |  |  Sortheap Allocation Parameters:
|  |  |  |  |  #Rows     = 38
|  |  |  |  |  Row Width = 28
|  |  |  |  Piped
|  |  Sorted Shared Temp Table Completion  ID = t2
|  |  Access Temp Table  ID = t2
|  |  |  #Columns = 2
|  |  |  Relation Scan
|  |  |  |  Prefetch: Eligible
|  Insert Into Sorted Shared Temp Table  ID = t3
|  |  #Columns = 5
|  |  #Sort Key Columns = 1
|  |  |  Key 1: (Ascending)
|  |  Use Partitioned Sort
|  |  Sortheap Allocation Parameters:
|  |  |  #Rows     = 61
|  |  |  Row Width = 72
|  |  Piped
|  Access Temp Table  ID = t3
|  |  #Columns = 5
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  Merge Join
|  |  Access Table Name = QUERY.EMPLOYEE  ID = 2,5
|  |  |  #Columns = 3
|  |  |  Parallel Scan
|  |  |  Relation Scan
|  |  |  |  Prefetch: Eligible
|  |  |  Lock Intents
|  |  |  |  Table: Intent Share
|  |  |  |  Row  : Next Key Share
|  |  |  Insert Into Sorted Shared Temp Table  ID = t4
|  |  |  |  #Columns = 3
|  |  |  |  #Sort Key Columns = 1
|  |  |  |  |  Key 1: WORKDEPT (Ascending)
|  |  |  |  Use Partitioned Sort
|  |  |  |  Sortheap Allocation Parameters:
|  |  |  |  |  #Rows     = 63
|  |  |  |  |  Row Width = 32
|  |  |  |  Piped
|  |  Sorted Shared Temp Table Completion  ID = t4
|  |  Access Temp Table  ID = t4
|  |  |  #Columns = 3
|  |  |  Relation Scan
|  |  |  |  Prefetch: Eligible
|  Insert Into Asynchronous Local Table Queue  ID = q1
Access Local Table Queue  ID = q1  #Columns = 5
Return Data to Application
|  #Columns = 5
 
End of section
 
 
Optimizer Plan:
 
               RETURN 
               (   1) 
                 |    
                LTQ   
               (   2) 
                 |    
               MSJOIN 
               (   3) 
              /      \
        TBSCAN        TBSCAN 
        (   4)        (  15) 
          |             |    
         SORT          SORT  
        (   5)        (  16) 
          |             |    
        MSJOIN        TBSCAN 
        (   6)        (  17) 
       /      \         |    
   TBSCAN    TBSCAN  Table:   
   (   7)    (  11)  QUERY   
     |         |     EMPLOYEE 
    SORT      SORT  
   (   8)    (  12) 
     |         |    
   TBSCAN    TBSCAN 
   (   9)    (  13) 
     |         |    
 Table:      Table:  
 QUERY       QUERY  
 DEPARTMENT  PROJECT

This plan is almost identical to the plan in the first example. The main differences are the creation of four subagents when the plan first starts and the table queue at the end of the plan to gather the results of each of subagent's work before returning them to the application.

It is also interesting to note that an extra sort is needed before joining with EMPLOYEE. This is necessary because the subagents processing the merge join between DEPARTMENT and PROJECT may produce the joined rows out of sequence.

Example Three: Multipartition Database Plan with Inter-Partition Parallelism

This example shows the same SQL statement as Example One: No Parallelism Plan, but this query has been compiled on a partitioned database made up of three database partitions.

******************** PACKAGE ***************************************
 
Package Name = QUERY.DYNEXPLN
	Prep Date = 1999/03/12
	Prep Time = 12:00:23:069
 
	Bind Timestamp = 1999-03-12-12.00.23.693295
 
	Isolation Level          = Cursor Stability
	Blocking                 = Block Unambiguous Cursors
	Query Optimization Class = 5
 
	Partition Parallel       = Yes
	Intra-Partition Parallel = No
 
	Function Path            = "SYSIBM", "SYSFUN", "QUERY"
 
-------------------- SECTION ---------------------------------------
Section = 1
 
 
SQL Statement:  
  
  SELECT x.lastname, x.job, y.deptname, y.location, z.projname 
  FROM employee AS x, department AS y, project AS z 
  WHERE x.workdept = y.deptno AND x.workdept = z.deptno AND y.deptno 
          = z.deptno 
 
Buffered Insert          = No
 
Estimated Cost        = 118
Estimated Cardinality = 263
 
Coordinator Subsection:
   Distribute Subsection #2
   |  Broadcast to Node List
   |  |  Nodes = 13, 17, 125
   Distribute Subsection #3
   |  Broadcast to Node List
   |  |  Nodes = 13, 17, 125
   Distribute Subsection #1
   |  Broadcast to Node List
   |  |  Nodes = 13, 17, 125
   Access Table Queue  ID = q1  #Columns = 5
   Return Data to Application
   |  #Columns = 5
 
Subsection #1:
   Access Table Queue  ID = q2  #Columns = 3
   |  Output Sorted
   |  |  #Key Columns = 1
   |  |  |  Key 1: (Ascending)
   Merge Join
   |  Access Table Name = QUERY.DEPARTMENT  ID = 2,4
   |  |  #Columns = 3
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   |  |  Lock Intents
   |  |  |  Table: Intent Share
   |  |  |  Row  : Next Key Share
   |  |  Insert Into Sorted Temp Table  ID = t1
   |  |  |  #Columns = 3
   |  |  |  #Sort Key Columns = 1
   |  |  |  |  Key 1: DEPTNO (Ascending)
   |  |  |  Sortheap Allocation Parameters:
   |  |  |  |  #Rows     = 40
   |  |  |  |  Row Width = 48
   |  |  |  Piped
   |  Sorted Temp Table Completion  ID = t1
   |  Access Temp Table  ID = t1
   |  |  #Columns = 3
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   Merge Join
   |  Access Table Queue  ID = q3  #Columns = 2
   |  |  Output Sorted
   |  |  |  #Key Columns = 1
   |  |  |  |  Key 1: (Ascending)
   Insert Into Asynchronous Table Queue  ID = q1
   |  Broadcast to Coordinator Node
   |  Rows Can Overflow to Temporary Table
 
Subsection #2: 
   Access Table Name = QUERY.EMPLOYEE  ID = 2,5
   |  #Columns = 3
   |  Relation Scan
   |  |  Prefetch: Eligible
   |  Lock Intents
   |  |  Table: Intent Share
   |  |  Row  : Next Key Share
   |  Insert Into Sorted Temp Table  ID = t2
   |  |  #Columns = 3
   |  |  #Sort Key Columns = 1
   |  |  |  Key 1: WORKDEPT (Ascending)
   |  |  Sortheap Allocation Parameters:
   |  |  |  #Rows     = 27
   |  |  |  Row Width = 32
   |  |  Piped
   Sorted Temp Table Completion  ID = t2
   Access Temp Table  ID = t2
   |  #Columns = 3
   |  Relation Scan
   |  |  Prefetch: Eligible
   |  Insert Into Asynchronous Table Queue  ID = q2
   |  |  Hash to Specific Node
   |  |  Rows Can Overflow to Temporary Tables
   Insert Into Asynchronous Table Queue Completion  ID = q2
 
Subsection #3:
   Access Table Name = QUERY.PROJECT  ID = 2,7
   |  #Columns = 2
   |  Relation Scan
   |  |  Prefetch: Eligible
   |  Lock Intents
   |  |  Table: Intent Share
   |  |  Row  : Next Key Share
   |  Insert Into Sorted Temp Table  ID = t3
   |  |  #Columns = 2
   |  |  #Sort Key Columns = 1
   |  |  |  Key 1: DEPTNO (Ascending)
   |  |  Sortheap Allocation Parameters:
   |  |  |  #Rows     = 38
   |  |  |  Row Width = 28
   |  |  Piped
   Sorted Temp Table Completion  ID = t3
   Access Temp Table  ID = t3
   |  #Columns = 2
   |  Relation Scan
   |  |  Prefetch: Eligible
   |  Insert Into Asynchronous Table Queue  ID = q3
   |  |  Hash to Specific Node
   |  |  Rows Can Overflow to Temporary Tables
   Insert Into Asynchronous Table Queue Completion  ID = q3
 
End of section
 
 
Optimizer Plan:
 
               RETURN 
               (   1) 
                 |    
                BTQ   
               (   2) 
                 |    
               MSJOIN 
               (   3) 
              /      \
       MSJOIN           MDTQ  
       (   4)          (  14) 
      /      \           |    
   MDTQ      TBSCAN    TBSCAN 
  (   5)     (  10)    (  15) 
    |          |         |    
  TBSCAN      SORT      SORT  
  (   6)     (  11)    (  16) 
    |          |         |    
   SORT      TBSCAN    TBSCAN 
  (   7)     (  12)    (  17) 
    |          |         |    
  TBSCAN   Table:      Table:  
  (   8)   QUERY       QUERY  
    |      DEPARTMENT  PROJECT 
 Table:   
 QUERY   
 EMPLOYEE

This plan has all the same pieces as the plan in the first example, but the section has been broken into four subsections. The subsections have the following tasks:

Coordinator Subsection. This subsection coordinates the other subsections. In this plan, it causes the other subsections to be distributed and then uses a table queue to gather the results to be returned to the application.
Subsection #1. This subsection scans table queue q2 and uses a merge join to join it with the DEPARTMENT table. A second merge join then adds in the data from table queue q3. The joined rows are then sent to the coordinator subsection using table queue q1.
Subsection #2. This subsection scans the EMPLOYEE table, sorts it, and hashes to a specific node with the results. These results are read by Subsection #1.
Subsection #3. This subsection scans the PROJECT table, sorts it, and hashes to a specific node with the results. These results are read by Subsection #1.

Example Four: Multipartition Database Plan with Inter-Partition and Intra-Partition Parallelism

This example shows the same SQL statement as Example One: No Parallelism Plan, but this query has been compiled on a partitioned database made up of three database partitions, each of which is on a four-way SMP machine.

******************** PACKAGE ***************************************
 
Package Name = QUERY.DYNEXPLN
	Prep Date = 1999/03/12
	Prep Time = 12:04:53:077
 
	Bind Timestamp = 1999-03-12-12.04.53.780702
 
	Isolation Level          = Cursor Stability
	Blocking                 = Block Unambiguous Cursors
	Query Optimization Class = 5
 
	Partition Parallel       = Yes
	Intra-Partition Parallel = Yes (Bind Degree = 4)
 
	Function Path            = "SYSIBM", "SYSFUN", "QUERY"
 
-------------------- SECTION ---------------------------------------
Section = 1
 
 
SQL Statement:  
  
  SELECT x.lastname, x.job, y.deptname, y.location, z.projname 
  FROM employee AS x, department AS y, project AS z 
  WHERE x.workdept = y.deptno AND x.workdept = z.deptno AND y.deptno 
          = z.deptno 
 
Buffered Insert          = No
 
Estimated Cost        = 140
Estimated Cardinality = 263
 
Coordinator Subsection:
   Distribute Subsection #2
   |  Broadcast to Node List
   |  |  Nodes = 13, 17, 125
   Distribute Subsection #3
   |  Broadcast to Node List
   |  |  Nodes = 13, 17, 125
   Distribute Subsection #1
   |  Broadcast to Node List
   |  |  Nodes = 13, 17, 125
   Access Table Queue  ID = q1  #Columns = 5
   Return Data to Application
   |  #Columns = 5
 
Subsection #1:
   Process Using 4 Subagents
   |  Access Table Queue  ID = q3  #Columns = 3
   |  Insert Into Sorted Shared Temp Table  ID = t1
   |  |  #Columns = 3
   |  |  #Sort Key Columns = 1
   |  |  |  Key 1: (Ascending)
   |  |  Use Partitioned Sort
   |  |  Sortheap Allocation Parameters:
   |  |  |  #Rows     = 27
   |  |  |  Row Width = 32
   |  |  Piped
   |  Access Temp Table  ID = t1
   |  |  #Columns = 3
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   |  Merge Join
   |  |  Access Table Name = QUERY.DEPARTMENT  ID = 2,4
   |  |  |  #Columns = 3
   |  |  |  Parallel Scan
   |  |  |  Relation Scan
   |  |  |  |  Prefetch: Eligible
   |  |  |  Lock Intents
   |  |  |  |  Table: Intent Share
   |  |  |  |  Row  : Next Key Share
   |  |  |  Insert Into Sorted Shared Temp Table  ID = t2
   |  |  |  |  #Columns = 3
   |  |  |  |  #Sort Key Columns = 1
   |  |  |  |  |  Key 1: DEPTNO (Ascending)
   |  |  |  |  Use Partitioned Sort
   |  |  |  |  Sortheap Allocation Parameters:
   |  |  |  |  |  #Rows     = 40
   |  |  |  |  |  Row Width = 48
   |  |  |  |  Piped
   |  |  Sorted Shared Temp Table Completion  ID = t2
   |  |  Access Temp Table  ID = t2
   |  |  |  #Columns = 3
   |  |  |  Relation Scan
   |  |  |  |  Prefetch: Eligible
   |  Insert Into Sorted Shared Temp Table  ID = t3
   |  |  #Columns = 6
   |  |  #Sort Key Columns = 1
   |  |  |  Key 1: (Ascending)
   |  |  Use Partitioned Sort
   |  |  Sortheap Allocation Parameters:
   |  |  |  #Rows     = 44
   |  |  |  Row Width = 76
   |  |  Piped
   |  Access Temp Table  ID = t3
   |  |  #Columns = 6
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   |  Merge Join
   |  |  Access Table Queue  ID = q5  #Columns = 2
   |  |  Insert Into Sorted Shared Temp Table  ID = t4
   |  |  |  #Columns = 2
   |  |  |  #Sort Key Columns = 1
   |  |  |  |  Key 1: (Ascending)
   |  |  |  Use Partitioned Sort
   |  |  |  Sortheap Allocation Parameters:
   |  |  |  |  #Rows     = 38
   |  |  |  |  Row Width = 28
   |  |  |  Piped
   |  |  Access Temp Table  ID = t4
   |  |  |  #Columns = 2
   |  |  |  Relation Scan
   |  |  |  |  Prefetch: Eligible
   |  Insert Into Asynchronous Local Table Queue  ID = q2
   Access Local Table Queue  ID = q2  #Columns = 5
   Insert Into Asynchronous Table Queue  ID = q1
   |  Broadcast to Coordinator Node
   |  Rows Can Overflow to Temporary Table
 
Subsection #2:
   Process Using 4 Subagents
   |  Access Table Name = QUERY.EMPLOYEE  ID = 2,5
   |  |  #Columns = 3
   |  |  Parallel Scan
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   |  |  Lock Intents
   |  |  |  Table: Intent Share
   |  |  |  Row  : Next Key Share
   |  |  Insert Into Sorted Shared Temp Table  ID = t5
   |  |  |  #Columns = 3
   |  |  |  #Sort Key Columns = 1
   |  |  |  |  Key 1: WORKDEPT (Ascending)
   |  |  |  Use Round-Robin Sort
   |  |  |  Sortheap Allocation Parameters:
   |  |  |  |  #Rows     = 27
   |  |  |  |  Row Width = 32
   |  |  |  Piped
   |  Sorted Shared Temp Table Completion  ID = t5
   |  Access Temp Table  ID = t5
   |  |  #Columns = 3
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   |  Insert Into Asynchronous Local Table Queue  ID = q4
   Access Local Table Queue  ID = q4  #Columns = 3
   |  Output Sorted
   |  |  #Key Columns = 1
   |  |  |  Key 1: (Ascending)
   Insert Into Asynchronous Table Queue  ID = q3
   |  Hash to Specific Node
   |  Rows Can Overflow to Temporary Tables
 
Subsection #3:
   Process Using 4 Subagents
   |  Access Table Name = QUERY.PROJECT  ID = 2,7
   |  |  #Columns = 2
   |  |  Parallel Scan
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   |  |  Lock Intents
   |  |  |  Table: Intent Share
   |  |  |  Row  : Next Key Share
   |  |  Insert Into Sorted Shared Temp Table  ID = t6
   |  |  |  #Columns = 2
   |  |  |  #Sort Key Columns = 1
   |  |  |  |  Key 1: DEPTNO (Ascending)
   |  |  |  Use Round-Robin Sort
   |  |  |  Sortheap Allocation Parameters:
   |  |  |  |  #Rows     = 38
   |  |  |  |  Row Width = 28
   |  |  |  Piped
   |  Sorted Shared Temp Table Completion  ID = t6
   |  Access Temp Table  ID = t6
   |  |  #Columns = 2
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   |  Insert Into Asynchronous Local Table Queue  ID = q6
   Access Local Table Queue  ID = q6  #Columns = 2
   |  Output Sorted
   |  |  #Key Columns = 1
   |  |  |  Key 1: (Ascending)
   Insert Into Asynchronous Table Queue  ID = q5
   |  Hash to Specific Node
   |  Rows Can Overflow to Temporary Tables
 
End of section
 
 
Optimizer Plan:
 
              RETURN 
              (   1) 
                |    
               BTQ   
              (   2) 
                |    
               LTQ   
              (   3) 
                |    
              MSJOIN 
              (   4) 
             /      \
       TBSCAN         TBSCAN 
       (   5)         (  20) 
         |              |    
        SORT           SORT  
       (   6)         (  21) 
         |              |    
       MSJOIN          DTQ   
       (   7)         (  22) 
      /      \          |    
  TBSCAN    TBSCAN     LMTQ  
  (   8)    (  16)    (  23) 
    |         |         |    
   SORT      SORT     TBSCAN 
  (   9)    (  17)    (  24) 
    |         |         |    
   DTQ      TBSCAN     SORT  
  (  10)    (  18)    (  25) 
    |         |         |    
   LMTQ   Table:      TBSCAN 
  (  11)  QUERY       (  26) 
    |     DEPARTMENT    |    
  TBSCAN              Table:  
  (  12)              QUERY  
    |                 PROJECT 
   SORT  
  (  13) 
    |    
  TBSCAN 
  (  14) 
    |    
 Table:   
 QUERY   
 EMPLOYEE

This plan is similar to that in Example Three: Multipartition Database Plan with Inter-Partition Parallelism, except that multiple subagents execute each subsection. Also, at the end of each subsection, a local table queue gathers the results from all of the subagents before the qualifying rows are inserted into the second table queue to be hashed to a specific node.

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

[ DB2 List of Books | Search the DB2 Books ]