Appendix B. DB2 EXPLAIN facility with DB2 Information Integrator 469
to run the nickname parts of a join query in parallel. This can possibly make
the query run faster if the amount of nickname data that participates in the
join is large.
The following applies to the server options identified in Table B-5 on page 464:
򐂰 COMM_RATE
The default is 2, and the value is in mega-bytes/second.
򐂰 CPU_RATIO
A value less than one (that is, 0.00001) means that the data source has more
CPU capacity than the federated server.
򐂰 IO_RATIO
A value less than one (that is, 0.00001) means that the data source has a
faster disk I/O rate than the federated server.
򐂰 VARCHAR_NO_TRAILING_BLANKS
This option affects performance with Oracle data sources. With
VARCHAR_NO_TRAILING_BLANKS = 'N' (default), for VARCHAR and
VARCHAR2 columns in joins and filters, DB2 II sends RPAD(colname)= to
Oracle, and Oracle cannot use an index to process this join or filter, possibly
causing poor performance. The option can be set either for an entire server
definition, or as a column on option on an individual column of a nickname.
Join of nicknames referencing Oracle and SQL server
We used the 32-bit instance for this example. Our objective was to review the
access plan in a db2exfmt output for a federated query that joined nicknames at
two different data sources and no local data access.
Our SQL query ranks customers based on the their placement of large quantity
orders. Large quantity orders are defined as those orders whose total quantity is
above 300 in our case. The customer table resides on the SQL server, while the
orders and line item table reside on Oracle.
Example B-8 shows the complete db2exfmt output for our SQL query. The SQL
statement we issued has been highlighted under “Original statement” in this
output.
Example: B-8 db2exfmt output - Join of nicknames
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
470 DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 08.02.0
SOURCE_NAME: SQLC2E05
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2004-06-24-08.52.14.146220
EXPLAIN_REQUESTER: DB2I32
Database Context:
----------------
Parallelism: None
CPU Speed: 4.841528e-07
Comm Speed: 100
Buffer Pool size: 1000
Sort Heap size: 256
Database Heap size: 1200
Lock List size: 100
Maximum Lock List: 10
Average Applications: 1
Locks Available: 1020
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 203 ----------------
QUERYNO: 1
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
SELECT C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE,
SUM(L_QUANTITY)
FROM MSS.CUSTOMER, ORA.ORDERS, ORA.LINEITEM
WHERE O_ORDERKEY IN
(SELECT L_ORDERKEY
FROM ORA.LINEITEM
GROUP BY L_ORDERKEY
Appendix B. DB2 EXPLAIN facility with DB2 Information Integrator 471
HAVING SUM(L_QUANTITY) > 300 ) AND.C_CUSTKEY = O_CUSTKEY AND.O_ORDERKEY =
L_ORDERKEY
GROUP BY C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE
ORDER BY O_TOTALPRICE DESC, O_ORDERDATE
FETCH FIRST 100 ROWS ONLY
Optimized Statement:
-------------------
SELECT Q9.$C0 AS "C_NAME", Q9.$C1 AS "C_CUSTKEY", Q9.$C2 AS "O_ORDERKEY",
Q9.$C3 AS "O_ORDERDATE", Q9.$C4 AS "O_TOTALPRICE", Q9.$C5
FROM
(SELECT Q8.$C0, Q8.$C1, Q8.$C2, Q8.$C3, Q8.$C4, SUM(Q8.$C5)
FROM
(SELECT Q7.C_NAME, Q7.C_CUSTKEY, Q6.O_ORDERKEY, Q6.O_ORDERDATE,
Q6.O_TOTALPRICE, Q5.L_QUANTITY
FROM ORA.LINEITEM AS Q5, ORA.ORDERS AS Q6, MSS.CUSTOMER AS Q7
WHERE (Q6.O_ORDERKEY = Q5.L_ORDERKEY) AND (Q7.C_CUSTKEY = Q6.O_CUSTKEY)
AND Q6.O_ORDERKEY = ANY
(SELECT Q3.$C1
FROM
(SELECT SUM(Q2.$C1), Q2.$C0
FROM
(SELECT Q1.L_ORDERKEY, Q1.L_QUANTITY
FROM ORA.LINEITEM AS Q1) AS Q2
GROUP BY Q2.$C0) AS Q3
WHERE (300 < Q3.$C0)) ) AS Q8
GROUP BY Q8.$C4, Q8.$C3, Q8.$C2, Q8.$C1, Q8.$C0) AS Q9
ORDER BY Q9.$C4 DESC, Q9.$C3
Access Plan:
-----------
Total Cost: 1.24987e+12
Query Degree:1
Rows
RETURN
( 1)
Cost
I/O
|
100
GRPBY
( 2)
1.24987e+12
2.94129e+11
|
2.9993e+07
TBSCAN
472 DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide
( 3)
1.24987e+12
2.94129e+11
|
2.9993e+07
SORT
( 4)
1.24986e+12
2.94128e+11
|
2.9993e+07
MSJOIN
( 5)
1.24984e+12
2.94127e+11
/----+----\
1.5e+06 19.9953
TBSCAN FILTER
( 6) ( 10)
583190 1.24984e+12
65026 2.94127e+11
| |
1.5e+06 2.9993e+07
SORT TBSCAN
( 7) ( 11)
521190 1.24984e+12
49719 2.94127e+11
| |
1.5e+06 2.9993e+07
SHIP SORT
( 8) ( 12)
135200 1.24984e+12
34412 2.94127e+11
| |
1.5e+06 2.9993e+07
NICKNM: MSS SHIP
CUSTOMER ( 13)
1.24983e+12
2.94126e+11
/------+-----\
1.5e+07 5.99861e+07
NICKNM: ORA NICKNM: ORA
ORDERS LINEITEM
1) RETURN: (Return Result)
Cumulative Total Cost: 1.24987e+12

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.