DB2 LUW optimizer: beginner to intermediate guide

The DB2 LUW Optimizer: Beginner to Intermediate Guide is a hands-on workshop for people who both are new or have some experienced using the DB2 SQL Optimizer (aka compiler). The workshop provided a high-level overview and basic understanding of the DB2 Optimizer while at the same time focusing on the DB2 Tools and Features for the DB2 Optimizer. The topics were presented in a workshop style, which used an interactive hands-on approach. The use of interactive exercises reinforced the topics that were presented over the course of the workshop. In this workshop the following topics were covered: • Section 1 Introduction to the Workshop Database and an SQL Primer • Section 2 Phases of the DB2 LUW Optimizer • Section 3 High Level Overview • Section 4 Explain Facility • Section 5 DB2EXFMT Tool • Section 6 Operators • Section 7 Predicates and Joins • Section 8 Basic Tuning Hints • Section 9 Catalog Statistics • Section 10 Cardinality Estimates (filter factor/selectivity) • Section 11 Statistical Views • Section 12 Optimizer Guidelines This also included interactive hands-on exercises using a database prepared specifically for this workshop. The presentation began with an introduction to the database created, which was used in the hands-on exercises. The database was created with tables of data to illustrate the topics presented. This introduction to the database also included a review of basic SQL. The next section was an introduction to the DB2 LUW Optimizer. The different phases of the Optimizer were discussed from the time the query entered the compiler to the time it was executed. The following section continued to provide a high level overview of the sections in the DB2 Explain Report and also the key components that influence the Optimizer when generating an Access Plan for any query. To understand the Access Plan generated by the DB2 Optimizer the Explain Facility was discussed in the next section. This also included a brief description of the DB2 Explain Tools available to view the Access Plan. With a better understanding of how to generate an Access Plan of a query for review the next section described the various operators used in an Access Plan. These operators were used in the generated Access Plan to show how the result set will be processed. In conjunction with the next section, where predicates and joins were discussed in more detail, exercises were used to illustrate how an SQL query would be translated into an Access Plan. The remaining sections built upon the general knowledge of Access Plans for an SQL query on how to influence changes in the Access Plan. In the Basic Tuning Hints section, there was a brief discussion on how the Database Manager and Database Configuration Parameters along with the DB2 Registry Variables can influence the DB2 Optimizer. In the next the two sections the Catalog Statistics and Cardinality Estimates were introduced. The statistics for a table describes the size of the data in the table. However, there are additional features of DB2 LUW that allow more control over the DB2 Optimizer. These two features are Statistical Views and Optimizer Guidelines, which were presented in the last two sections of the workshop.