It's also possible that the delay on first touch has nothing to do with query optimization at all. It is possible that the problem arises instead from OS memory caching. Use WRKSYSSTS during your first touch to look at page faults in the memory pool where the DB server jobs are running. Try the same again during subsequent touches. If the page faults are high during first touch and not so high during subsequent touches, your problem is memory caching. If that's the case, allocate more memory to the memory pool.
-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx [mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim Oberholtzer
Sent: Wednesday, February 01, 2012 9:20 AM
To: Java Programming on and around the IBM i
Subject: Re: AS400/DB2 - caching specific query
Tomer,
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.
Regards,
Tomer S.
--
This is the Java Programming on and around the IBM i (JAVA400-L) mailing list To post a message email: JAVA400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/java400-l.
midrange.com
