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.
