Ways to improve query performance

Access paths have a significant impact on DB2® performance. DB2 chooses access paths, but you can use tools to understand how access paths affect performance in certain situations.

An access path is the path that DB2 uses to locate data that is specified in SQL statements. An access path can be indexed or sequential.

Two important factors in the performance of an SQL statement are the amount of time that DB2 uses to determine the access path at run time and the efficiency of the access path. DB2 determines the access path for a statement either when you bind the plan or package that contains the SQL statement or when the SQL statement executes.

The time at which DB2 determines the access path depends on whether the statement is executed statically or dynamically and whether the statement contains input host variables.

The access path that DB2 chooses determines how long the SQL statement takes to run. For example, to execute an SQL query that joins two tables, DB2 has several options. DB2 might make any of the following choices to process those joins:

• Scan the PARTS table for every row that matches a row in the PRODUCTS table.
• Scan the PRODUCTS table for every row that matches a row in the PARTS table.
• Sort both tables in PROD# order; then merge the ordered tables to process the join.

Choosing the best access path for an SQL statement depends on a number of factors. Those factors include the content of any tables that the SQL statement queries and the indexes on those tables.

DB2 also uses extensive statistical information about the database and resource use to make the best access choices.

In addition, the physical organization of data in storage affects how efficiently DB2 can process a query.