db2exfmt - Explain Table Format Command - gives information needed to tune the query .One of the DBA objectives is to decrease the query cost. Decreased query cost equals quicker user response. Decreasing the query cost – improves overall performance.
Read the post - DB2 Tuning Toolkit – db2expln - to analyse the accesspath for a specific query.
Read the post - DB2 Tuning Toolkit – DB2 Design advisor - Ddb2advis - for index recommnedations
A syntax and process example:
Step 1 – Create the source file
Put a query in a input file called index.sql. This is just a sample query
select SUBSTR(TBNAME,1,40), SUBSTR(TBCREATOR,1,10),
substr(name,1,30), SUBSTR(CREATOR,1,8),substr(colnames,1,60), firstkeycard, fullkeycard, sequential_pages, density, iid, uniquerule, stats_time, colnames
from sysibm.sysindexes a
ORDER BY tbcreator, TBNAME, NAME;
Step 2 Execute the db2exfmt command
db2 set current explain mode explain
db2 –tvf indextune.sql
db2exfmt -1 –d myDB –o db2exfmt.out
The output will be be similar to :
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 09.05.3
SOURCE_NAME: SQLC2G15
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2012-06-19-14.40.37.846644
EXPLAIN_REQUESTER: ***********
Database Context:
----------------
Parallelism: None
CPU Speed: 2.361721e-07
Comm Speed: 0
Buffer Pool size: 138228
Sort Heap size: 7157
Database Heap size: 2761
Lock List size: 72726
Maximum Lock List: 97
Average Applications: 1
Locks Available: 4514830
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 1
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
select SUBSTR(TBNAME,1,40), SUBSTR(TBCREATOR,1,10), substr(name,1,30),
SUBSTR(CREATOR,1,8),substr(colnames,1,60), firstkeycard, fullkeycard,
sequential_pages, density, iid, uniquerule, stats_time, colnames
from sysibm.sysindexes a
WHERE density = 1 AND iid = 3
Optimized Statement:
-------------------
SELECT SUBSTR(Q1.TBNAME, 1, 40), SUBSTR(Q1.TBCREATOR, 1, 10), SUBSTR(Q1.NAME,
1, 30), SUBSTR(Q1.CREATOR, 1, 8), SUBSTR(Q1.COLNAMES, 1, 60),
Q1.FIRSTKEYCARD AS "FIRSTKEYCARD", Q1.FULLKEYCARD AS "FULLKEYCARD",
Q1.SEQUENTIAL_PAGES AS "SEQUENTIAL_PAGES", 1 AS "DENSITY", Q1.IID AS
"IID", Q1.UNIQUERULE AS "UNIQUERULE", Q1.STATS_TIME AS "STATS_TIME",
Q1.COLNAMES AS "COLNAMES"
FROM SYSIBM.SYSINDEXES AS Q1
WHERE (Q1.IID = 3) AND (Q1.DENSITY = 1)
Access Plan:
-----------
Total Cost: 140.165
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
0.107904
TBSCAN
( 2)
140.165
72
|
1261
TABLE: SYSIBM
SYSINDEXES
Q1
Extended Diagnostic Information:
--------------------------------
No extended Diagnostic Information for this statement.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 140.165
Cumulative CPU Cost: 3.57774e+06
Cumulative I/O Cost: 72
Cumulative Re-Total Cost: 0.755584
Cumulative Re-CPU Cost: 3.19929e+06
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 140.06
Estimated Bufferpool Buffers: 72
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v9.5.0.3 : s081118
HEAPUSE : (Maximum Statement Heap Usage)
112 Pages
STMTHEAP: (Statement heap size)
4096
Input Streams:
-------------
2) From Operator #2
Estimated number of rows: 0.107904
Number of columns: 13
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.COLNAMES+Q2.STATS_TIME+Q2.UNIQUERULE
+Q2.IID+Q2.DENSITY+Q2.SEQUENTIAL_PAGES
+Q2.FULLKEYCARD+Q2.FIRSTKEYCARD+Q2.$C4+Q2.$C3
+Q2.$C2+Q2.$C1+Q2.$C0
2) TBSCAN: (Table Scan)
Cumulative Total Cost: 140.165
Cumulative CPU Cost: 3.57724e+06
Cumulative I/O Cost: 72
Cumulative Re-Total Cost: 0.755467
Cumulative Re-CPU Cost: 3.1988e+06
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 140.06
Estimated Bufferpool Buffers: 72
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
Predicates:
----------
2) Sargable Predicate
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.0412371
Predicate Text:
--------------
(Q1.IID = 3)
3) Sargable Predicate
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.00207507
Predicate Text:
--------------
(Q1.DENSITY = 1)
Input Streams:
-------------
1) From Object SYSIBM.SYSINDEXES
Estimated number of rows: 1261
Number of columns: 13
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.$RID$+Q1.STATS_TIME+Q1.UNIQUERULE
+Q1.SEQUENTIAL_PAGES+Q1.FULLKEYCARD
+Q1.FIRSTKEYCARD+Q1.COLNAMES+Q1.CREATOR
+Q1.NAME+Q1.TBCREATOR+Q1.TBNAME+Q1.IID
+Q1.DENSITY
Output Streams:
--------------
2) To Operator #1
Estimated number of rows: 0.107904
Number of columns: 13
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.COLNAMES+Q2.STATS_TIME+Q2.UNIQUERULE
+Q2.IID+Q2.DENSITY+Q2.SEQUENTIAL_PAGES
+Q2.FULLKEYCARD+Q2.FIRSTKEYCARD+Q2.$C4+Q2.$C3
+Q2.$C2+Q2.$C1+Q2.$C0
Related Posts
DB2 Tuning Toolkit – db2expln
DB2 Tuning Toolkit – DB2 Design advisor - Ddb2advis
