Re: AS400/DB2 - caching specific query

[Jim Oberholtzer <midrangel@xxxxxxxxxx>]

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.