Using optimizer profiles

DB2 uses cost-based optimization for choosing the access plan for a query. The total cost depends on various factors, such as system configuration, database and database manager configuration, memory available (buffer pool, sort heap, and so on), current optimization level, CPU parallelism, IO characteristics, and so on, and most importantly information regarding the actual data. In the DB2 terminology, this is also known as catalog statistics. Therefore, it is always recommended to keep the statistics updated. These statistics include information about the following:

• Number of rows, data pages, active blocks, data range information, data length, and so on

• Most frequent values

• Number of distinct values for a column, and clustering details

• Column group statistics

These details are stored in the system catalog tables, and are used by the optimizer to calculate the cost of each possible query execution plan. The DB2 optimizer is intelligent enough to come up with the best...