Most likely that is because the access plan is being deleted at IPL. When the query runs again, the access plan is recreated and kept so the optimization does not need to be done again. Another thing to consider, put the query jobs into a memory shared pool that does not change. That way the optimizer can just use the current plan without having to re-optimize the query every time it runs.

If you are not applying PTF's and the temporary addresses are not growing significantly, you don't need to IPL. V6 is very good at cleaning itself up and V7 is better.

If you search the archives there are several discussions regarding the best QAQQINI settings. Also there is a couple of good Redbooks about DB/2 performance as well. Search for "IBM i Database Performance and Query Optimization" The most recent version is for 7.1 but you might find the 6.1 version somewhere. Most of the 7.1 content applies to 6.1 as well.

Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects

On 2/1/2012 8:59 AM, Tomer Sason wrote:
Hello everybody,

I am using DB2 on AS400 (V6R1).
When I execute a query the first time, It's taking long time (relatively speaking).
The next executions time of the same query are significantly shorter.
If I do not use this specific query for a while, the next time of execution gets longer again
It seems that the query was cached and after a while it was removed from the cache.

I assume this is due to caching policy of DB2.
I think the caching policy can be set in QAQQINI file (either in QSYS or QUSRSYS), but this is done for all queries and for a limited time.
I am looking for a way to keep a specific query in cache in order to improve performance.

Tomer S.

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.