DB2 Tuning Toolkit – db2exfmt

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