Finding the best indexes for a specified workload. When specifying the 

workload, you can use the frequency parameter to prioritize the queries. You 

can also limit disk space for the target indexes.

Testing an index on a workload without having to create the index

Example 9 39 shows a simple 

db2advis

 call against a single SQL query; for more 

options run 

db2advis  h

 from the command line.

Example 9 39   Finding indexes for a particular query

db2advis  d db2_emp  s "select first_name, last_name, dept_name from 

departments d, employees e where d.dept_code = e.dept_code and e.last_name like 

 W% " 

execution started at timestamp 2004 02 10 14.15.00.408000

recommending indexes...

Initial set of proposed indexes is ready.

Found maximum set of [2] recommended indexes

Cost of workload with all indexes included [0.155868] timerons

total disk space needed for initial set [   0.018] MB

total disk space constrained to         [   1.000] MB

  2  indexes in current solution

 [ 50.3188] timerons  (without indexes)

 [  0.1559] timerons  (with current solution)

 [%99.69] improvement

Trying variations of the solution set.

  2  indexes in current solution

 [ 50.3188] timerons  (without indexes)

 [  0.1559] timerons  (with current solution)

 [%99.69] improvement

   LIST OF RECOMMENDED INDEXES

   ===========================

   index[1],    0.009MB

   CREATE UNIQUE INDEX IDX030801141500000 ON "DB2INST1"."DEPARTMENTS" 

("DEPT_CODE" ASC) INCLUDE ("DEPT_NAME") ALLOW REVERSE SCANS ;

   COMMIT WORK ;

     RUNSTATS ON TABLE "DEPARTMENTS" FOR INDEX "IDX030801141500000" ;

   COMMIT WORK ;

   index[2],    0.009MB

   CREATE INDEX IDX030801141500000 ON "DB2INST1"."EMPLOYEES" ("LAST_NAME" ASC, 

"FIRST_NAME" ASC, "DEPT_CODE" ASC) ALLOW REVERSE SCANS ;

   COMMIT WORK ;

     RUNSTATS ON TABLE "EMPLOYEES" FOR INDEX "IDX030801141500000" ;

   COMMIT WORK ;

   ===========================

 Chapter 9. Testing and tuning 

315