IDUG DB2 Tech Conference in Denver, Colorado, USA 2012
Denver Colorado Convention Center, Denver, Colorado
May 14-18, 2012

View Printable Version

Deep Dive into DB2 10 Query Performance Optimization: Statistics and Optimization Profiles - H14

Session Number: 319
Track: DB2 10 for Linux, Unix, Windows
Session Type: Podium Presentation
Primary Presenter: John Hornibrook [IBM Canada]
Co-Presenter: Calisto Zuzarte [IBM Toronto Lab]

Room 105 => Fri, May 18, 2012 (09:15 AM - 10:15 AM)

Audience experience level: Intermediate, Advanced
Presentation Category: Implementing New DB2 Releases and Features
Presentation Platform: DB2 for Linux, UNIX, Windows
Audiences this presentation will apply to: Data Architects, Database Administrators
Technical areas this presentation will apply to: New Release (DB2 for LUW)
Objective 1: Understand how statistical views can be used to improve query performance and what new features have been added in DB2 10.
Objective 2: See how it is easier to create and maintain statistical views in DB2 10.

Objective 3: Learn about new RUNSTATS features and how its performance has been improved.

Objective 4: Understand how optimization profiles can be more easily used to influence query access paths.

Objective 5: See how DB2 registry variables can be set dynamically and more selectively.

Abstract:  DB2 10 significantly improves query performance and database administration through enhancements to catalog statistics and optimization profiles. Statistical views have been improved in DB2 10 to make them easier to use and maintain. Their capabilities have also been extended to collect statistics for complex expressions used in predicates. Statistics for statistical views can now be collected automatically by the DB2 data server, using the same approach as tables and nicknames. Statistical views are now easier to deploy using the Optim Query Tuner workload statistical view advisor. The RUNSTATS utility has also been enhanced in DB2 10, with the addition of index page sampling, better index page prefetching and overall improved performance. Finally, DB2 optimization profiles have been extended to make them more powerful and easier to use. SQL statement matching is now more flexible so that an optimization profile can match many SQL statements with a similar structure. Optimization profiles can now be used to specify certain registry variables, so that they can be applied more selectively. Attend this presentation to understand how these new features can improve your query performance and make administering your DB2 system easier.

For questions or concerns about your event registration, please contact