Re: SQL Performance in V5R2

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Bill

You should take a look at site <http://www.iseries.ibm.com/db2/sqe.html>, 
which also has a link to an APAR that gives you the latest changes. 
According to the APAR, "join queries and queries with IN or OR predicates 
are now able
to run with the SQL Query Engine".

BTW, it's not just the optimizer that is affected - the engine is 
completely different, running in SLIC. The optimizer actually runs above 
the MI layer and determines whether a statement qualifies for the new 
engine. So parsing is still done above MI, in XPF. Optimization may be 
slower, as it is now a 2-layer process. But that is often not an issue in a 
long-running statement - better optimization often results in shorter 
overall runtime.

Original plans called for support of complex (that means messy joins) 
read-only statements. But it is being rolled out slowly - the APAR tells 
you what is currently supported.

Another piece of this is new statistics on individual columns - things like 
cardinality (number of distinct values), and distribution stats (histogram 
and most frequent values). These can assist the optimizer by giving it more 
exact data, instead of the optimizer making guesses, based on the nature of 
the statement, as the "classic" one does. I.e., estimates of the number of 
records in the returned recordset can be closer to reality. OpsNav has 
tools for viewing thse stats, and there is a set of tools at the 
above-listed site for looking at these things from a green-screen

BTW, the design of the new engine is quite cool - allows plugging in new 
function quite easily - very object-oriented.

HTH

Vern

At 01:17 PM 8/18/2003 -0500, you wrote:
> One of the things listed in "What's new in V5R2" is a rewrite of the query
> optimizer to improve SQL / query performance. We are upgrading soon, but
> being asked now about things we can do to aid in CPU usage and user
> experience. For those of you that have already gone to V5R2, has there been
> a noticeable performance improvement? Our apps are VERY SQL intensive to
> accommodate completely dynamic sorting and filtering, and while our system
> performance is good (box and index tuning done and reviewed regularly) we
> are always prodded by our hardware guys to see if we can't get a few more
> RPMs out of the hamster wheel.
>
> Any shared experience would certainly be appreciated.