Three 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.
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 with a pushed-down sort operation inside each table access.
****************** PACKAGE ***************************************
Package Name = QUERY.EXAMPLE
Prep Date = 1997/04/07
Prep Time = 09:21:27:087
Bind Timestamp = 1997-04-07-09.21.27.879168
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 X, DEPARTMENT Y, PROJECT Z
WHERE X.WORKDEPT = Y.DEPTNO AND X.WORKDEPT = Z.DEPTNO AND Y.DEPTNO
= Z.DEPTNO
Estimated Cost = 373066
Estimated Cardinality = 1600000
Access Table Name = QUERY.EMPLOYEE ID = 5
| #Columns = 3
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Share
| Insert Into Sorted Temp Table ID = t1
| | #Columns = 3
| | #Sort Key Columns = 1
| | Sortheap Allocation Parameters:
| | | #Rows = 1000
| | | Row Width = 36
| | Piped
Sorted Temp Table Completion ID = t1
Access Temp Table ID = t1
| #Columns = 3
| Relation Scan
| | Prefetch: Eligible
Merge Join
| Access Table Name = QUERY.DEPARTMENT ID = 4
| | #Columns = 3
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Share
| | Insert Into Sorted Temp Table ID = t2
| | | #Columns = 3
| | | #Sort Key Columns = 1
| | | Sortheap Allocation Parameters:
| | | | #Rows = 1000
| | | | Row Width = 60
| | | Piped
| Sorted Temp Table Completion ID = t2
| Access Temp Table ID = t2
| | #Columns = 3
| | Relation Scan
| | | Prefetch: Eligible
Merge Join
| Access Table Name = QUERY.PROJECT ID = 7
| | #Columns = 2
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Share
| | Insert Into Sorted Temp Table ID = t3
| | | #Columns = 2
| | | #Sort Key Columns = 1
| | | Sortheap Allocation Parameters:
| | | | #Rows = 1000
| | | | Row Width = 36
| | | Piped
| Sorted Temp Table Completion ID = t3
| Access Temp Table ID = t3
| | #Columns = 2
| | Relation Scan
| | | Prefetch: Eligible
Return Data to Application
| #Columns = 5
End of section
The first part of the plan accesses the EMPLOYEE table to get the last name, job, and department number. This information is placed in a sorted temporary table since merge joins require their input to be sorted. The resulting temporary table is the outer table of the first merge join operator.
Note that even though the sorted temporary table indicates that the result will be piped (that is, kept in memory), the access plan treats this case as if the result was written to a real table and provides a scan of the table to send the result to the user. This allows the access plan to be independent of the volume of data present at execution time. The temporary table accessed at execution time will either reside in memory or on disk.
The inner of this join operator is another sorted temporary table which is also piped. This temporary table is created from the DEPARTMENT table and contains the department name and location. In a similar fashion, the second merge join accesses and sorts the information needed from the PROJECT table.
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.EXAMPLE
Prep Date = 1997/04/07
Prep Time = 09:23:04:018
Bind Timestamp = 1997-04-07-09.23.04.182277
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 X, DEPARTMENT Y, PROJECT Z
WHERE X.WORKDEPT = Y.DEPTNO AND X.WORKDEPT = Z.DEPTNO AND Y.DEPTNO
= Z.DEPTNO
Intra-Partition Parallelism Degree = 4
Estimated Cost = 373066
Estimated Cardinality = 1600000
Process Using 4 Subagents
| Access Table Name = QUERY.EMPLOYEE ID = 5
| | #Columns = 3
| | Parallel Scan
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Share
| | Insert Into Sorted Shared Temp Table ID = t1
| | | #Columns = 3
| | | #Sort Key Columns = 1
| | | Use Partitioned Sort
| | | Sortheap Allocation Parameters:
| | | | #Rows = 1000
| | | | Row Width = 36
| | | 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.DEPARTMENT ID = 4
| | | #Columns = 3
| | | Parallel Scan
| | | Relation Scan
| | | | Prefetch: Eligible
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Share
| | | Insert Into Sorted Shared Temp Table ID = t2
| | | | #Columns = 3
| | | | #Sort Key Columns = 1
| | | | Use Partitioned Sort
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 1000
| | | | | Row Width = 60
| | | | Piped
| | Sorted Shared Temp Table Completion ID = t2
| | Access Temp Table ID = t2
| | | #Columns = 3
| | | Relation Scan
| | | | Prefetch: Eligible
| Merge Join
| | Access Table Name = QUERY.PROJECT ID = 7
| | | #Columns = 2
| | | Parallel Scan
| | | Relation Scan
| | | | Prefetch: Eligible
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Share
| | | Insert Into Sorted Shared Temp Table ID = t3
| | | | #Columns = 2
| | | | #Sort Key Columns = 1
| | | | Use Replicated Sort
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 1000
| | | | | Row Width = 36
| | | | Piped
| | Sorted Shared Temp Table Completion ID = t3
| | Access Temp Table ID = t3
| | | #Columns = 2
| | | 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
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.
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. Additionally, db2expln was run with the -i option so the Explain facility operator IDs appear in parenthesis down the left side of the plan.
******************** PACKAGE ***************************************
Package Name = QUERY.EXAMPLE
Prep Date = 1997/04/07
Prep Time = 09:51:24:076
Bind Timestamp = 1997-04-07-09.51.24.765882
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 X, DEPARTMENT Y, PROJECT Z
WHERE X.WORKDEPT = Y.DEPTNO AND X.WORKDEPT = Z.DEPTNO AND Y.DEPTNO
= Z.DEPTNO
Buffered Insert = No
Estimated Cost = 1766063
Estimated Cardinality = 43200000
Coordinator Subsection:
(-----) Distribute Subsection #2
| Broadcast to Node List
| | Nodes = 53, 122, 156
(-----) Distribute Subsection #3
| Broadcast to Node List
| | Nodes = 53, 122, 156
(-----) Distribute Subsection #1
| Broadcast to Node List
| | Nodes = 53, 122, 156
( 2) Access Table Queue ID = q1 #Columns = 5
( 1) Return Data to Application
| #Columns = 5
Subsection #1:
( 9) Access Table Name = QUERY.EMPLOYEE ID = 5
| #Columns = 3
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Share
( 9) | Insert Into Sorted Temp Table ID = t1
| | #Columns = 3
| | #Sort Key Columns = 1
| | Sortheap Allocation Parameters:
| | | #Rows = 1000
| | | Row Width = 36
| | Piped
( 8) Sorted Temp Table Completion ID = t1
( 7) Access Temp Table ID = t1
| #Columns = 3
| Relation Scan
| | Prefetch: Eligible
( 7) Merge Join
( 12) | Access Table Queue ID = q2 #Columns = 2
| | Output Sorted #Key Columns = 1
( 5) Merge Join
( 17) | Access Table Queue ID = q3 #Columns = 3
| | Output Sorted #Key Columns = 1
( 2) Insert Into Asynchronous Table Queue ID = q1
| Broadcast to Coordinator Node
| Rows Can Overflow to Temporary Table
Subsection #2:
( 14) Access Table Name = QUERY.PROJECT ID = 7
| #Columns = 2
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Share
( 14) | Insert Into Sorted Temp Table ID = t2
| | #Columns = 2
| | #Sort Key Columns = 1
| | Sortheap Allocation Parameters:
| | | #Rows = 1000
| | | Row Width = 36
| | Piped
( 13) Sorted Temp Table Completion ID = t2
( 12) Access Temp Table ID = t2
| #Columns = 2
| Relation Scan
| | Prefetch: Eligible
( 12) Insert Into Asynchronous Table Queue ID = q2
| Broadcast to All Nodes of Subsection 1
| Rows Can Overflow to Temporary Table
Subsection #3:
( 19) Access Table Name = QUERY.DEPARTMENT ID = 4
| #Columns = 3
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Share
( 19) | Insert Into Sorted Temp Table ID = t3
| | #Columns = 3
| | #Sort Key Columns = 1
| | Sortheap Allocation Parameters:
| | | #Rows = 1000
| | | Row Width = 60
| | Piped
( 18) Sorted Temp Table Completion ID = t3
( 17) Access Temp Table ID = t3
| #Columns = 3
| Relation Scan
| | Prefetch: Eligible
( 17) Insert Into Asynchronous Table Queue ID = q3
| Broadcast to All Nodes of Subsection 1
| Rows Can Overflow to Temporary Table
End of section
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:
It is interesting to examine how the joins are done in the query. In this case, both joins are handled in the same way: the outer table is on local disk and the inner is broadcast to all nodes, which makes these broadcast inner joins. (For more information on join strategies, see "Join Concepts".)