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 hash 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.

   ******************** PACKAGE ***************************************
 
   Package Name = QUERY.DYNEXPLN
   Prep Date = 1998/06/09
   Prep Time = 11:14:16:037
 
   Bind Timestamp = 1998-06-09-11.14.16.371570
 
   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        = 119
   Estimated Cardinality = 109
 
   Access Table Name = QUERY.EMPLOYEE  ID = 5
   |  #Columns = 3
   |  Relation Scan
   |  |  Prefetch: Eligible
   |  Lock Intents
   |  |  Table: Intent Share
   |  |  Row  : Next Key Share
   |  Process Build Table for Hash Join
   Hash Join
   |  Estimated Build Size: 7167
   |  Estimated Probe Size: 9011
   |  Access Table Name = QUERY.PROJECT  ID = 7
   |  |  #Columns = 2
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   |  |  Lock Intents
   |  |  |  Table: Intent Share
   |  |  |  Row  : Next Key Share
   |  |  Process Build Table for Hash Join
   |  Hash Join
   |  |  Estimated Build Size: 5789
   |  |  Estimated Probe Size: 6414
   |  |  Access Table Name = QUERY.DEPARTMENT  ID = 4
   |  |  |  #Columns = 3
   |  |  |  Relation Scan
   |  |  |  |  Prefetch: Eligible
   |  |  |  Lock Intents
   |  |  |  |  Table: Intent Share
   |  |  |  |  Row  : Next Key Share
   |  |  |  Process Probe Table For Hash Join
   Return Data to Application
   |  #Columns = 5
 
   End of section
 
 
   Optimizer Plan:
 
                  RETURN
                  (   1)
                    |
                  HSJOIN
                  (   2)
                 /      \
           HSJOIN         TBSCAN
           (   3)         (   6)
          /      \          |
      TBSCAN    TBSCAN   Table:
      (   4)    (   5)   QUERY
        |         |      EMPLOYEE
    Table:      Table:
    QUERY       QUERY
    DEPARTMENT  PROJECT

The first part of the plan accesses the EMPLOYEE table to build a hash join Build table. Then the PROJECT table is accessed, and hash join builds another Build table. Finally, the DEPARTMENT table is scanned, and lookups are performed on the Build tables, first on the one created from PROJECT, then on the one created from EMPLOYEE. When the application completes, the qualifying rows are returned.

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 = 1998/06/09
   Prep Time = 11:21:20:032
 
   Bind Timestamp = 1998-06-09-11.21.20.322036
 
   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        = 129
   Estimated Cardinality = 109
 
   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  : Next Key Share
   |  |  Process Build Table for Hash Join
   |  Hash Join
   |  |  Estimated Build Size: 7167
   |  |  Estimated Probe Size: 9011
   |  |  Access Table Name = QUERY.PROJECT  ID = 7
   |  |  |  #Columns = 2
   |  |  |  Parallel Scan
   |  |  |  Relation Scan
   |  |  |  |  Prefetch: Eligible
   |  |  |  Lock Intents
   |  |  |  |  Table: Intent Share
   |  |  |  |  Row  : Next Key Share
   |  |  |  Process Build Table for Hash Join
   |  |  Hash Join
   |  |  |  Estimated Build Size: 5789
   |  |  |  Estimated Probe Size: 6414
   |  |  |  Access Table Name = QUERY.DEPARTMENT  ID = 4
   |  |  |  |  #Columns = 3
   |  |  |  |  Parallel Scan
   |  |  |  |  Relation Scan
   |  |  |  |  |  Prefetch: Eligible
   |  |  |  |  Lock Intents
   |  |  |  |  |  Table: Intent Share
   |  |  |  |  |  Row  : Next Key Share
   |  |  |  |  Process Probe Table For Hash Join
   |  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)
                    |
                    TQ
                  (   2)
                    |
                  HSJOIN
                  (   3)
                 /      \
           HSJOIN         TBSCAN
           (   4)         (   7)
          /      \          |
      TBSCAN    TBSCAN   Table:
      (   5)    (   6)   QUERY
        |         |      EMPLOYEE
    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.

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 four database partitions.

  ******************** PACKAGE ***************************************
 
  Package Name = QUERY.DYNEXPLN
  Prep Date = 1998/06/09
  Prep Time = 11:27:30:058
 
  Bind Timestamp = 1998-06-09-11.27.30.583713
 
  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        = 111
  Estimated Cardinality = 190
 
  Coordinator Subsection:
     Distribute Subsection #2
     |  Broadcast to Node List
     |  |  Nodes = 10, 20, 30, 40
     Distribute Subsection #3
     |  Broadcast to Node List
     |  |  Nodes = 10, 20, 30, 40
     Distribute Subsection #1
     |  Broadcast to Node List
     |  |  Nodes = 10, 20, 30, 40
     Access Table Queue  ID = q1  #Columns = 5
     Return Data to Application
     |  #Columns = 5
 
  Subsection #1:
     Access Table Queue  ID = q2  #Columns = 2
     Hash Join
     |  Estimated Build Size: 5789
     |  Estimated Probe Size: 7632
     |  Access Table Queue  ID = q3  #Columns = 3
     |  Hash Join
     |  |  Estimated Build Size: 5333
     |  |  Estimated Probe Size: 6414
     |  |  Access Table Name = QUERY.DEPARTMENT  ID = 4
     |  |  |  #Columns = 3
     |  |  |  Relation Scan
     |  |  |  |  Prefetch: Eligible
     |  |  |  Lock Intents
     |  |  |  |  Table: Intent Share
     |  |  |  |  Row  : Next Key Share
     |  |  |  Process Probe Table For Hash Join
     Insert Into Asynchronous Table Queue  ID = q1
     |  Broadcast to Coordinator Node
     |  Rows Can Overflow to Temporary Table
 
  Subsection #2:
     Access Table Name = QUERY.PROJECT  ID = 7
     |  #Columns = 2
     |  Relation Scan
     |  |  Prefetch: Eligible
     |  Lock Intents
     |  |  Table: Intent Share
     |  |  Row  : Next Key Share
     |  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.EMPLOYEE  ID = 5
     |  #Columns = 3
     |  Relation Scan
     |  |  Prefetch: Eligible
     |  Lock Intents
     |  |  Table: Intent Share
     |  |  Row  : Next Key Share
     |  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)
                   |
                   TQ
                 (   2)
                   |
                 HSJOIN
                 (   3)
                /      \
          HSJOIN           TQ
          (   4)         (   8)
         /      \          |
     TBSCAN       TQ     TBSCAN
     (   5)     (   6)   (   9)
       |          |        |
   Table:       TBSCAN   Table:
   QUERY        (   7)   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 builds a hash join Build table. Then table queue q3 is accessed, and hash join builds another Build table. Finally, the DEPARTMENT table is scanned, and lookups are performed on the Build tables, first on the one created from q3, then on the one created from q2. After joining all the rows, the results are sent to the coordinator via a table queue.
Subsection #2. This subsection scans the PROJECT table and hashes to a specific node with the results. These results are read by Subsection #1.
Subsection #3. This subsection scans the EMPLOYEE table 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 four database partitions.

  ******************** PACKAGE ***************************************
 
  Package Name = QUERY.DYNEXPLN
  	Prep Date = 1998/06/09
  	Prep Time = 11:30:33:077
 
  	Bind Timestamp = 1998-06-09-11.30.33.770876
 
  	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 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        = 111
  Estimated Cardinality = 190
 
  Coordinator Subsection:
     Distribute Subsection #2
     |  Broadcast to Node List
     |  |  Nodes = 10, 20, 30, 40
     Distribute Subsection #3
     |  Broadcast to Node List
     |  |  Nodes = 10, 20, 30, 40
     Distribute Subsection #1
     |  Broadcast to Node List
     |  |  Nodes = 10, 20, 30, 40
     Access Table Queue  ID = q1  #Columns = 5
     Return Data to Application
     |  #Columns = 5
 
  Subsection #1:
     Process Using 3 Subagents
     |  Access Table Queue  ID = q3  #Columns = 2
     |  Hash Join
     |  |  Estimated Build Size: 5789
     |  |  Estimated Probe Size: 7632
     |  |  Access Table Queue  ID = q5  #Columns = 3
     |  |  Hash Join
     |  |  |  Estimated Build Size: 5333
     |  |  |  Estimated Probe Size: 6414
     |  |  |  Access Table Name = QUERY.DEPARTMENT  ID = 4
     |  |  |  |  #Columns = 3
     |  |  |  |  Parallel Scan
     |  |  |  |  Relation Scan
     |  |  |  |  |  Prefetch: Eligible
     |  |  |  |  Lock Intents
     |  |  |  |  |  Table: Intent Share
     |  |  |  |  |  Row  : Next Key Share
     |  |  |  |  Process Probe Table For Hash Join
     |  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 3 Subagents
     |  Access Table Name = QUERY.PROJECT  ID = 7
     |  |  #Columns = 2
     |  |  Parallel Scan
     |  |  Relation Scan
     |  |  |  Prefetch: Eligible
     |  |  Lock Intents
     |  |  |  Table: Intent Share
     |  |  |  Row  : Next Key Share
     |  Insert Into Asynchronous Local Table Queue  ID = q4
     Access Local Table Queue  ID = q4  #Columns = 2
     Insert Into Asynchronous Table Queue  ID = q3
     |  Hash to Specific Node
     |  Rows Can Overflow to Temporary Tables
 
  Subsection #3:
     Process Using 3 Subagents
     |  Access Table Name = QUERY.EMPLOYEE  ID = 5
     |  |  #Columns = 3
     |  |  Parallel Scan
     |  |  Relation Scan
     |  |  |  Prefetch: Eligible
     |  |  Lock Intents
     |  |  |  Table: Intent Share
     |  |  |  Row  : Next Key Share
     |  Insert Into Asynchronous Local Table Queue  ID = q6
     Access Local Table Queue  ID = q6  #Columns = 3
     Insert Into Asynchronous Table Queue  ID = q5
     |  Hash to Specific Node
     |  Rows Can Overflow to Temporary Tables
 
  End of section
 
 
  Optimizer Plan:
 
                 RETURN
                 (   1)
                   |
                   TQ
                 (   2)
                   |
                   TQ
                 (   3)
                   |
                 HSJOIN
                 (   4)
                /      \
          HSJOIN          TQ
          (   5)        (  10)
         /      \         |
     TBSCAN      TQ       TQ
     (   6)    (   7)   (  11)
       |         |        |
   Table:        TQ     TBSCAN
   QUERY       (   8)   (  12)
   DEPARTMENT    |        |
               TBSCAN   Table:
               (   9)   QUERY
                 |      PROJECT
              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 ]