1.Does the DB2 optimizer use PLAN_TABLE to pick the access path at run time for a query? (Or) 2.Does it tries to pick the access path by re-optimizing the query? If yes. Can we say to DB2 to use the access path from PLAN_TABLE for certain packages that are having SQL on static table (I mean look up tables the values never change)?

Does it improve the performance instead of re-optimizing just pick the access path from PLAN_TABLE?

-- basivi inaganti (inaganb@nationwide.com), August 13, 2002

Answers

owDB2 will re-optimize SQL statements at BIND time for static SQL - or (predominantly) at run time for dynamic SQL. DB2 will not "read the access path from the PLAN_TABLE." The access paths are bound into the package or plan and DB2 will "read" them from there as it runs your static programs. You can use an optimization hint to force DB2 to choose an access path that is already stored in the PLAN_TABLE. This usually should be done only when you are having a severe performance problem caused by an access path changing. As to your question about whether an optimization hint will improve performance - of course, the answer is it depends. It depends on whether or not the old access path you ar etrying to use is still optimal. Things could have changed cush that that access path is bad and performance could actually worsen.

Good luck! Craig

-- Craig S. Mullins (craig_mullins@bmc.com), September 10, 2002.