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 the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
