450 DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide
----------------
Parallelism: None
CPU Speed: 4.723442e-07
Comm Speed: 100
Buffer Pool size: 75000
Sort Heap size: 20000
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
The EXPLAIN INSTANCE section lists overall DB2 instance and database
parameter information.
Besides identification details such as the DB2 code base level, requestor name,
and time of request, this section also provides environmental settings in which
the SQL compiler optimized the query. Information provided includes total buffer
pool size (sum of all buffer pools including temporary table spaces), sort heap
size, type of query parallelism, dynamic or static SQL, optimization level, and
isolation levels. The CPU and Communications speeds are used by the optimizer
to compare the capabilities of the remote data source servers during cost
optimization to determine predicate pushdown. This environmental settings are
critical to understanding the optimizer’s decision in arriving at a particular access
plan.
SQL STATEMENT section
Example B-3 lists the section that provides additional information about the SQL
statement, and shows how DB2 Query Rewrite has rewritten the SQL
before it is
processed by pushdown analysis. Using global semantics, query rewrite
transforms SQL statements into forms that can be optimized more easily, and as
a result can improve the possible access paths. Queries might be rewritten in
multiple ways, including operation merging, operation movement, and predicate
translation.
Example: B-3 STATEMENT section
---------------- STATEMENT 1 SECTION 1 ----------------
QUERYNO: 1
Appendix B. DB2 EXPLAIN facility with DB2 Information Integrator 451
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY,
SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE *
(1-L_DISCOUNT)) AS SUM_DISC_PRICE, SUM(L_EXTENDEDPRICE *
(1-L_DISCOUNT) * (1+L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY,
AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC,
COUNT(*) AS COUNT_ORDER
FROM ora.LINEITEM
WHERE L_SHIPDATE <= DATE ('1998-12-01') - 90 DAYS
GROUP BY L_RETURNFLAG, L_LINESTATUS
ORDER BY L_RETURNFLAG, L_LINESTATUS
Optimized Statement:
-------------------
SELECT Q3.$C0 AS "L_RETURNFLAG", Q3.$C1 AS "L_LINESTATUS", Q3.$C2 AS
"SUM_QTY", Q3.$C3 AS "SUM_BASE_PRICE", Q3.$C4 AS "SUM_DISC_PRICE",
Q3.$C5 AS "SUM_CHARGE", (Q3.$C2 / Q3.$C6) AS "AVG_QTY", (Q3.$C3 /
Q3.$C6) AS "AVG_PRICE", (Q3.$C7 / Q3.$C6) AS "AVG_DISC", Q3.$C6 AS
"COUNT_ORDER"
FROM
(SELECT Q2.$C0, Q2.$C1, SUM(Q2.$C2), SUM(Q2.$C3), SUM((Q2.$C3 * (1 -
Q2.$C4))), SUM(((Q2.$C3 * (1 - Q2.$C4)) * (1 + Q2.$C5))), COUNT(*
), SUM(Q2.$C4)
FROM
(SELECT Q1.L_RETURNFLAG, Q1.L_LINESTATUS, Q1.L_QUANTITY,
Q1.L_EXTENDEDPRICE, Q1.L_DISCOUNT, Q1.L_TAX
FROM ORA.LINEITEM AS Q1
WHERE (Q1.L_SHIPDATE <= '09/02/1998')) AS Q2
GROUP BY Q2.$C1, Q2.$C0) AS Q3
ORDER BY Q3.$C0, Q3.$C1
Two versions of the text of the SQL statement are recorded for each statement
explained.
򐂰 The original statement is the code that the SQL compiler receives from the
application. The original SQL statement in db2exfmt shows the SQL
submitted by the user. If the user SQL included any views, their names
appear in the original SQL statement.
򐂰 The Optimized statement is reverse translated from the internal compiler
representation of the query.

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.