Previous Topic: SQL and Access PathsNext Topic: Importance of Catalog Statistics

SQL and Access Paths › The DB2 Optimizer

The DB2 Optimizer

The DB2 optimizer performs the following functions:

The DB2 optimizer uses catalog statistics to attach a cost to the possible access paths and then chooses the cheapest path. Understanding the DB2 optimizer, the DB2 access paths, and how your data is accessed helps you adjust the DB2 performance.

The DB2 optimizer is responsible for interpreting your queries and determining how to access your data in the most efficient manner. However, it can use only the best of the available access paths. The DB2 optimizer does not know what access paths are possible that are not available. The DB2 optimizer can deal only with the information that you provide. This information is primarily stored in the DB2 system catalog, which includes the basic information about the tables, columns, indexes, and statistics. The DB2 optimizer does not know about the indexes that could be built, or anything about the possible inputs to our queries (unless all literal values are provided in a query), input files, batch sequences, or transaction patterns. Therefore, an understanding of the DB2 optimizer, statistics, and access paths is important, but the design of applications and databases for performance is also important.

The DB2 optimizer interprets your queries and determines the most efficient method to access your data. The DB2 optimizer can use access paths that are only available when determining the most efficient way to access your data. If other access paths exist but are not available, these access paths are not considered.

The DB2 optimizer can use only the information you provide when determining access paths and interpreting queries. The primary location for this information is the DB2 system catalog, which includes the basic information about tables, columns, indexes, and statistics. However, the DB2 optimizer does not know about indexes that could be built, possible inputs to queries, input files, batch sequences, or transactions patterns unless you specifically provide this information.