DB2 Tuning Toolkit – db2exfmt

19 June,2012 by Jack Vamvas

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   

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on DB2 Tuning Toolkit – db2exfmt

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright