DB2 Tuning Toolkit – DB2 Design advisor - Ddb2advis

15 June,2012 by Jack Vamvas

One of the DBA objectives is to decrease the query cost. Decreasing the query cost – improves overall performance. The db2advis recommends  indexes for a query.

 Read the post - DB2 Tuning Toolkit – db2expln    - to analyse  the accesspath for a specific  query.

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 db2advis command

db2advis -d myDB -i index.sql 

If you see the error message:

Explain tables not set up properly for schema MYINST

The insert into the ADVISE_INSTANCE table has failed.

0 solutions were evaluated by the advisor

exiting with error code [-219]

 

To fix that problem, I execute db2 -tvf <instance_home>\sqllib\misc\EXPLAIN.DDL

A typical response.

 

execution started at timestamp 2012-06-15-11.58.47.685111
found [1] SQL statements from the input file
Recommending indexes...
  0  indexes in current solution
 [140.1555] timerons  (without recommendations)
 [140.1555] timerons  (with current solution)
 [0.00%] improvement


--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
--  no indexes are recommended for this workload.


--
--
-- RECOMMENDED EXISTING INDEXES
-- ============================


--
--
-- UNUSED EXISTING INDEXES
-- ============================
-- ===========================
--

-- ====ADVISOR DETAILED XML OUTPUT=============
-- ==(Benefits do not include clustering recommendations)==
--

--
--
--
--0
--
-- 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,
 from sysibm.sysindexes s_time, colnames
 - a WHERE density = 1 AND iid = 3
--
--
--0.000000
--1
--
--

-- ====ADVISOR DETAILED XML OUTPUT=============
--

0 solutions were evaluated by the advisor
DB2 Workload Performance Advisor tool is finished.

 Related Posts

DB2 Tuning Toolkit – db2expln

DB2 Tuning Toolkit – db2exfmt

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 – DB2 Design advisor - Ddb2advis

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