I was a little busy from past sometime so didn't get a chance to post anything. There are a lot new things I read from last 1 month. There are some interesting posts from Susan Visser about the availability of the books in India, about the salary survey, about IDUG and some polls. I also hear to the podcast from John Boyer about the XForm 1.1. but one interesting things I tried during this time is the use of package cache in DB2 LUW.
In my last post I talked about the advantages of Static run over dynamic. A dynamic query goes through the same phase as of static. The only difference is that in case of static, DB2 saves the compiled SQL statements in catalogs while in dynamic compilation occurs every time. So if DB2 provides some mechanism to save the compiled SQL in memory and use this in future if the same statement encountered again, A dynamic statement can give performance benefit even better then static in some cases. Package cache serve this ppurpose. If you think, in your application most of the trasactions are repeating, increasing the size if package cache (pckcachesz DB config parameter) will allow DB2 to save the compiled dynamic statement in memory and reuse it. This may not give you advantage if your statement is not repeating. The first time the dynamic statement will take its own time as it need to be compiled but from 2nd time onwards you can see the performace banefit. The real questions here is, will it be the alternate to static statement? i think its not. I am not sure how many statement we can cache ? . Apart from that, this activity is totally depend on the DB Manager when it decides to cache and when its not. If there are a lot of statement compared to the size of the cache, there are the possibility that the compiled statements are overwritting each other and hence providing no banefit. Apart from that this cache is allocated whenever the database is initialized and freed when the database shut down, hence the statement need to be cached every time the database initialized again.