We have runstats performed every night on DB2 V8.1 (AIX). I was running some snapshots earlier and I noticed that there were 32,000 dynamic sql statements executed and 53,000 static sql run. The way I understand it is that the dynamic sql always gets the most recent runstat data, but the static sql will only get the most recent data if a bind is performed after the runstats. Do we need to add a step of doing a bind after the runstats to improve the run time of the 53,000 static sql statements? If so, how much will this help?

Thanks
Anthony

If your stats don't significantly change or you are satisfied with the access path of the packages, then don't rebind.

Dynamic plans may not make use of the new stats if they are still in package cache. Reuse of packages in cache depends on several factors.

Yep!

Marcus has it right!

In our shop we have noticed, when talking about a new application, that during first 1/2 year runstats and rebind shoud run quite often. After that once in a year is about right. Exept when a significant changes in data material will happen (big inserts, deletes or updates).

Cheers, Bill

Marcus wrote:
Dynamic plans may not make use of the new stats if they are still in package cache.

Is there anything you can do to flush the package out of the cache so the dynamic plans pick up the new stats? Re-bind won't do this?

Thanks
Anthony

FLUSH PACKAGE CACHE DYNAMIC. See the SQL Reference for details.

Wouldn't the package cache get flushed at instance stop/start time? We stop and start our instance before our tests. If it does get flushed, we wouldn't need to do the "flush package cache dynamic", or am I wrong?

Thanks Marcus. The FLUSH PACKAGE CACHE DYNAMIC really made a positive difference with our testing!

Anthony