526 DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide
Coordinator Partition
Number of columns: 9
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.O_CUSTKEY(A)+Q3.O_COMMENT
+Q3.O_SHIPPRIORITY+Q3.O_CLERK
+Q3.O_ORDERPRIORITY+Q3.O_ORDERDATE
+Q3.O_TOTALPRICE+Q3.O_ORDERSTATUS+Q3.O_ORDERKEY
Partition Column Names:
----------------------
+NONE
Output Streams:
--------------
7) To Operator #6
Estimated number of rows: 3.00114e+07
Partition Map ID: -100
Partitioning: (COOR )
Coordinator Partition
Number of columns: 9
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.O_CUSTKEY(A)+Q3.O_COMMENT
+Q3.O_SHIPPRIORITY+Q3.O_CLERK
+Q3.O_ORDERPRIORITY+Q3.O_ORDERDATE
+Q3.O_TOTALPRICE+Q3.O_ORDERSTATUS+Q3.O_ORDERKEY
Partition Column Names:
----------------------
+NONE
8) SORT : (Sort)
Cumulative Total Cost: 2.83444e+07
Cumulative CPU Cost: 3.50582e+11
Cumulative I/O Cost: 2.94425e+06
Cumulative Re-Total Cost: 0
Cumulative Re-CPU Cost: 0
Cumulative Re-I/O Cost: 2.64806e+06
Cumulative First Row Cost: 2.83444e+07
Cumulative Comm Cost:2.41827e+06
Cumulative First Comm Cost:2.41827e+06
Appendix B. DB2 EXPLAIN facility with DB2 Information Integrator 527
Estimated Bufferpool Buffers: 2.94425e+06
Arguments:
---------
DUPLWARN: (Duplicates Warning flag)
FALSE
NUMROWS : (Estimated number of rows)
30011368
ROWWIDTH: (Estimated width of rows)
112
SORTKEY : (Sort Key column)
1: Q3.O_CUSTKEY(A)
SPILLED : (Pages spilled to bufferpool or disk)
2.64806e+06
TEMPSIZE: (Temporary Table Page Size)
4096
UNIQUE : (Uniqueness required flag)
FALSE
Input Streams:
-------------
5) From Operator #9
Estimated number of rows: 3.00114e+07
Partition Map ID: -100
Partitioning: (COOR )
Coordinator Partition
Number of columns: 9
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.O_COMMENT+Q3.O_SHIPPRIORITY+Q3.O_CLERK
+Q3.O_ORDERPRIORITY+Q3.O_ORDERDATE
+Q3.O_TOTALPRICE+Q3.O_ORDERSTATUS
+Q3.O_ORDERKEY+Q3.O_CUSTKEY
Partition Column Names:
----------------------
+NONE
Output Streams:
--------------
6) To Operator #7
Estimated number of rows: 3.00114e+07
Partition Map ID: -100
Partitioning: (COOR )
528 DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide
Coordinator Partition
Number of columns: 9
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.O_CUSTKEY(A)+Q3.O_COMMENT
+Q3.O_SHIPPRIORITY+Q3.O_CLERK
+Q3.O_ORDERPRIORITY+Q3.O_ORDERDATE
+Q3.O_TOTALPRICE+Q3.O_ORDERSTATUS+Q3.O_ORDERKEY
Partition Column Names:
----------------------
+NONE
9) TQ : (Table Queue)
Cumulative Total Cost: 638128
Cumulative CPU Cost: 3.73425e+10
Cumulative I/O Cost: 296184
Cumulative Re-Total Cost: 628410
Cumulative Re-CPU Cost: 1.90555e+10
Cumulative Re-I/O Cost: 296184
Cumulative First Row Cost: 10.4828
Cumulative Comm Cost:2.41827e+06
Cumulative First Comm Cost:0
Estimated Bufferpool Buffers: 296184
Arguments:
---------
LISTENER: (Listener Table Queue type)
FALSE
TQMERGE : (Merging Table Queue flag)
FALSE
TQREAD : (Table Queue Read type)
READ AHEAD
TQSEND : (Table Queue Write type)
DIRECTED
UNIQUE : (Uniqueness required flag)
FALSE
Input Streams:
-------------
4) From Operator #10
Estimated number of rows: 1.00038e+07
Partition Map ID: 4
Partitioning: (MULT )
Multiple Partitions
Appendix B. DB2 EXPLAIN facility with DB2 Information Integrator 529
Number of columns: 9
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.O_COMMENT+Q3.O_SHIPPRIORITY+Q3.O_CLERK
+Q3.O_ORDERPRIORITY+Q3.O_ORDERDATE
+Q3.O_TOTALPRICE+Q3.O_ORDERSTATUS
+Q3.O_ORDERKEY+Q3.O_CUSTKEY
Partition Column Names:
----------------------
+1: Q3.O_ORDERKEY
Output Streams:
--------------
5) To Operator #8
Estimated number of rows: 3.00114e+07
Partition Map ID: -100
Partitioning: (COOR )
Coordinator Partition
Number of columns: 9
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.O_COMMENT+Q3.O_SHIPPRIORITY+Q3.O_CLERK
+Q3.O_ORDERPRIORITY+Q3.O_ORDERDATE
+Q3.O_TOTALPRICE+Q3.O_ORDERSTATUS
+Q3.O_ORDERKEY+Q3.O_CUSTKEY
Partition Column Names:
----------------------
+NONE
10) TBSCAN: (Table Scan)
Cumulative Total Cost: 628410
Cumulative CPU Cost: 1.90555e+10
Cumulative I/O Cost: 296184
Cumulative Re-Total Cost: 628410
Cumulative Re-CPU Cost: 1.90555e+10
Cumulative Re-I/O Cost: 296184
Cumulative First Row Cost: 10.4278
Cumulative Comm Cost:0
Cumulative First Comm Cost:0
Estimated Bufferpool Buffers: 296184
530 DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
SEQUENTIAL
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
Input Streams:
-------------
3) From Object TPCD.ORDERS
Estimated number of rows: 1.00038e+07
Partition Map ID: 4
Partitioning: (MULT )
Multiple Partitions
Number of columns: 10
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.$RID$+Q2.O_CUSTKEY+Q2.O_ORDERKEY
+Q2.O_ORDERSTATUS+Q2.O_TOTALPRICE
+Q2.O_ORDERDATE+Q2.O_ORDERPRIORITY+Q2.O_CLERK
+Q2.O_SHIPPRIORITY+Q2.O_COMMENT
Partition Column Names:
----------------------
+1: Q2.O_ORDERKEY
Output Streams:
--------------
4) To Operator #9
Estimated number of rows: 1.00038e+07
Partition Map ID: 4
Partitioning: (MULT )
Multiple Partitions
Number of columns: 9
Subquery predicate ID: Not Applicable

Get DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.