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






footer




 

 

 

 

 Home | About Us | Network | Services | Support | FAQ | Control Panel | Order Online | Sitemap | Contact

san diego web hosting

 

Our partners: PHP: Hypertext Preprocessor Cheap Web Hosting JSP Web Hosting Ontario Web Hosting  Jsp Web Hosting

Cheapest Web Hosting Java Hosting Cheapest Hosting

Visionwebhosting.net Business web hosting division of Vision Web Hosting Inc.. All rights reserved