Friday, May 3, 2013

In-depth article on DB2 Statistical Views published on developerWorks

There is a new article on developerWorks: Get the most out of the DB2 optimizer: Leveraging statistical views to improve query execution performance. One of the many new features in DB2 10.1 that we mention during DB2 Bootcamps is for automated runstats on statistical views. Often, the reaction is "what are statistical views?".

There is an entire section about statistical views in the DB2 Information Center. Basically, there are a view defined on complex queries.Then, on the result set of those view statistics are collected which help the optimizer to improve query planning. Essentially, those statistics help determine cardinalities on complex relationships between multiple tables. Though DB2 usually has statistical information on each single table, it does not on the predicate-based combination (a.k.a. join) of multiple tables. That is when statistical views come to the rescue.

You can read more on them at the developerWorks article and see how the query plans and performance improve.