Similar to Oracle's EXPLAIN PLAN statement and MySQL's EXPLAIN statement, DB2 provides an EXPLAIN PLAN statement which generates information about the access plan chosen for the specified statement. The information is then placed into explain tables for reference. However, there are times when you simply want to see the explain plan displayed in a hierarchical format with cost information; that's where db2expln comes in handy.

The simplest method of generating and displaying the plan and cost information is to place your query in a file (expln.sql) and execute the db2expln command.

SELECT deptname, count(*)
FROM department d JOIN employee e ON d.deptno = e.workdept
GROUP BY deptname
ORDER BY 2 DESC;

db2expln -d sample -z \; -f expln.sql -t

The output is as follows:

[db2inst1]$ db2expln -d sample -z \; -f expln.sql -t

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

        Isolation Level          = Cursor Stability
        Blocking                 = Block Unambiguous Cursors
        Query Optimization Class = 5

        Partition Parallel       = No
        Intra-Partition Parallel = No

        SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
                                   "DB2INST1"

Statement:

  SELECT deptname, count(*)
  FROM department d JOIN employee e ON d.deptno =e.workdept
  GROUP BY deptname
 ORDER BY 2 DESC

Section Code Page = 1208

Estimated Cost = 7.677794
Estimated Cardinality = 14.000000

Access Table Name = DB2INST1.EMPLOYEE  ID = 2,6
|  Index Scan:  Name = DB2INST1.XEMP2  ID = 2
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: WORKDEPT (Ascending)
|  #Columns = 1
|  Skip Inserted Rows
|  Avoid Locking Committed Data
|  Currently Committed for Cursor Stability
|  #Key Columns = 0
|  |  Start Key: Beginning of Index
|  |  Stop Key: End of Index
|  Index-Only Access
|  Index Prefetch: None
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Index Predicate(s)
|  |  Process Build Table for Hash Join
Hash Join
|  Estimated Build Size: 4000
|  Estimated Probe Size: 4000
|  Access Table Name = DB2INST1.DEPARTMENT  ID = 2,5
|  |  #Columns = 2
|  |  Skip Inserted Rows
|  |  Avoid Locking Committed Data
|  |  Currently Committed for Cursor Stability
|  |  May participate in Scan Sharing structures
|  |  Scan may start anywhere and wrap, for completion
|  |  Fast scan, for purposes of scan sharing management
|  |  Scan can be throttled in scan sharing management
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Sargable Predicate(s)
|  |  |  Process Probe Table for Hash Join
Insert Into Sorted Temp Table  ID = t1
|  #Columns = 2
|  #Sort Key Columns = 1
|  |  Key 1: DEPTNAME (Ascending)
|  Sortheap Allocation Parameters:
|  |  #Rows     = 14.000000
|  |  Row Width = 32
|  Piped
|  Buffered Partial Aggregation
Access Temp Table  ID = t1
|  #Columns = 2
|  Relation Scan
|  |  Prefetch: Eligible
Final Aggregation
|  Group By
|  Column Function(s)
Insert Into Sorted Temp Table  ID = t2
|  #Columns = 2
|  #Sort Key Columns = 1
|  |  Key 1: (Descending)
|  Sortheap Allocation Parameters:
|  |  #Rows     = 14.000000
|  |  Row Width = 28
|  Piped
Access Temp Table  ID = t2
|  #Columns = 2
|  Relation Scan
|  |  Prefetch: Eligible
|  Sargable Predicate(s)
|  |  Return Data to Application
|  |  |  #Columns = 2
Return Data Completion

End of section