Hi Jim,
Thanks for your fast replay.
I will search the archives for discussions regarding the best QAQQINI settings and
DB/2 performance issues.
How do I put the query jobs into a memory shared pool that does not change?
Can I do it for a specific query?
I forgot to mention earlier that I use java Statement.
I will try to use PreparedStatement.
Do you think that the JTopen JDBC driver will support PreparedStatement
in a way that will take the advantage of the DB2 caching?
-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx [mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim Oberholtzer
Sent: Wednesday, February 01, 2012 5:20 PM
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.
midrange.com
