DB2 Tuning Toolkit – DB2 Design advisor - Ddb2advis

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