Hey,
Can't find the answer to this anywhere.
I'm trying to optimize some queries but DB2 (8.2) keeps caching the results and this messes up my tests.
Is there any way of disabling or skipping the caching of a query?
Thanks

THere is no way to disable it, but you can tell the system to remove what is there. Look at the "FLUSH PACKAGE CACHE DYNAMIC" statement.

Andy

Hi,
"Flush package cache dynamic" only empties 'package cache'. If you would like to 'flush' all caches (for example bufferpools etc) stop the db2 database with command:
db2stop force
db2start

I hope you are testing on test environment. If not then don't look into my advice.

BTW, it is not usual to stop caching in production environment, because database can run much slower. So this is not the case of IBM to support such behavior of DB2.

Hope this helps,
Grofaty

If you are talking about caching of data in bufferpools, forget about trying to disable that for tests. With a normal application, some data is expected to already be in the bufferpool.

For TPC-H tests (see www.tpc.org) they require that each query be run 10 times and then calculate an average run time for each query. Even with nothing else running on the system, the run times can vary quite a bit during the 10 tests of each query. But the averages are fairly predictive of the performance.

Careful: this will stop the DB2 instance and not just a single database

Stolze, I agree. I was just assuming there is a test computer with test database on it. That is how the testing system should be designed...
Grofaty

Running a query right after starting an instance is the most unrealistic test you could possibly devise. DB2 bufferpools are an integral part of the database, and running a query on an empty bufferpool is of no use in the real world of performance tuning or benchmarking.

Marcus_A, the main question was how to empty 'caches'...

But I have seen many SQLs that running from bufferpool executed in second and running from disk (not in bufferpool) run for 5 minutes. This logic is specially seen if federated database is used and using two databases on different platform (federated server is moving a lot of data from source to target database). Stopping and starting a local and remote database is final test I always do to know if transactional SQL is working efficient.

Regards,
Grofaty

It really depends on what you want to do. I did this (recycling DB2 instance) quite often to measure physical disk I/O, for example. Granted, such numbers cannot be applied to product systems, but can be very helpful to derive formulas or whatever you need...

Thanks
Yes, all the work is being done on a test system.
The thing is the large queries are usually not performed by the same client, so for them it's like having no cache (by the time the next client issues a large query, db2 already invalidates it in the cache). That's why I must verify how the queries behave not when ran 10 times, as an average, but only once.
I thought I could avoid restarting DB2 but seems I can't escape it after all :P
Peace

You never said if you wanted to flush the package cache (the compiled SQL query and access plan) or flush the data cache (the bufferpools).

Flushing the bufferpools for each test is not a good idea unless you have a poorly configured system. Even a very large database should have a bufferpool hit ratio above zero if there are separate bufferpools dedicated to small tables and small/medium indexes which should not ever get flushed out of cache by a table scan of a large table (which should be placed in the different bufferpool). Bufferpool resources are shared, and not specific to a client.

If you are talking about package cache, someone already told you how to flush that without a restart.

Based on what you have said so far, the chances of your test coming up with any results that correlate to the real world are close to zero. Any extrapolations or conclusions you make are probably invalid.

I was talking about the package cache. Meanwhile I found out it can be flushed only on DB2 versions 8 and higher.
Thanks