DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide

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