How to update the execution plan in DB2

As with Oracle databases, the DB2 database also uses an optimizer to analyze your SQL and generate the execution plan that it considers optimal (Access plans). The DB2 optimizer is actually a collection of standard Rules, and in general we just tell DB2 what to retrieve, not how to retrieve it.

So what does the DB2 optimizer do to determine the optimal access path for SQL?

The DB2 Optimizer is a cost-based optimizer, or CBO (Based optmizer). That is, the DB2 optimizer applies a query cost formula that evaluates and balances four factors for each possible access path: CPU cost, I/O cost, statistics in the DB2 system directory, and actual SQL statements.

So let's take a quick look at the workflow of the DB2 optimizer:

1. The DB2 optimizer, after receiving the SQL statement, first verifies the SQL syntax and ensures that the SQL is correct

2. According to the current system environment information, generate the best execution plan to optimize the SQL statement

3. Translate SQL into a computer instruction language and perform this optimized SQL

4. Return the results, or store them for future execution

In our view, statistical information in the DB2 system catalog is a very important basis for the DB2 optimizer to work correctly. These statistics provide the optimizer with information about the state of the table that the SQL statement being optimized will access. This information mainly includes:

table--includes information such as the number of records in the table, PAGE, Pctfree, and compress, and the related system views are: Sysstat.tables, syscat.tables

Columns-includes information such as the number, length, distribution characteristics, and compress of Columns, and the related system views are: Sysstat.columns, Syscat. Columns

index--includes whether there are indexes, the organization of the index (number of leaf pages and levels), the number of discrete values for the index keys, and whether to cluster the indexes, and the related system views are: Sysstat.indexes, Syscat. Indexes

The other is the partition/node group information and the table space information

How do you update this information in a timely manner? Ensure that the DB2 optimizer is working properly and provides the following methods in DB2.

Runstats and Reogchk

Runstats the function of this command is to collect the state information of database objects, which is very important for the database to use reasonable access plan. In general, below are a few situations where we need to collect statistics using Runstats:

1. After creating a index for the table, we'd better do a runstat. This is a situation that we often overlook. Many times we add a index to the table, analysis of the implementation plan, found no change, feel very strange. In fact, this time, you need to do a runstats, you can. In 8.2, DB2 has made a good improvement to avoid this problem, in the creation of index, you can immediately update your information.

2. After a reorg of the table, remember to do a runstats. Because the table to do reorg, will modify a lot of information on the table, such as high water level, so do a runstats, you can update statistical information.

3. When the data in your table has changed a lot, in general, about 10%-20% of the amount of data in the table has changed, should be a runstats. These changes include deletion, modification, and insertion. For some very large tables, for example, in a data warehouse project, some fact tables are huge. At this time, the complete runstats of a large table may take a considerable amount of time, DB2 8.1 support us to sample these large tables, for example, only 20% of the data as Runstats, in general, can also guarantee the correct implementation plan. Of course, first of all, make sure that the data in this table is better distributed evenly.