Learning Visually with Examples [Electronic resources] نسخه متنی

16.6. Lack of Proper Maintenance

There are two important database maintenance operations that you need to perform on a regular basis.

• Reorganize your tables and indexes as they become unclustered due to INSERT, UPDATE, and DELETE statements.

• Keep the statistics on your tables and indexes current, especially as you add, change, or remove data from your tables.

The DB2 optimizer uses the statistics about your tables and indexes when it is building the access plan for the statements executed by your applications. If the statistics are out of date or show that the data is not clustered according to the indexes you have defined on the tables, the optimizer cannot choose the most efficient access plan.

Statistics about your database objects are stored in the database's system catalog tables. DB2 reads this information when the optimizer is building the access plan for one of your SQL statements. You can update this information with the current statistics by using one of these methods:

• Running the

  RUNSTATS utility

• Using the LOAD utility to load data into one of your tables

• Running the

  REORGCHK command

Use the

RUNSTATS utility to gather new, updated statistics for tables and indexes after you have done any of the following:

• Reorganized a table or index

• Added a large number of rows to a table using the

  LOAD or

  IMPORT utilities

• Made a large number of changes to the data in the table using

  INSERT, UPDATE , or

  DELETE statements.

• Changed a table space's prefetch size

The reorganize utility clusters the data in the table so that it is in the same order as the index you specify. Be sure to specify the index that is most often used to join this table with other tables, and/or to access data in this table as a result of the SQL you are executing. To reorganize the table named

org so that it is clustered in the same order as the index

orgx on the table, use the command:

reorg table org index orgx 

You can also reorganize a table using the Control Center by opening the tables list, selecting the table you wish to reorganize, and then right-clicking on the table and choosing the

Reorganize option. This displays the dialog shown in Figure 16.12.

You can choose the index on which you wish to cluster the data, and also choose whether you want to perform an online or offline load. You can then open the

Schedule tab and choose to either schedule the reorganization for a later time or run the reorganization immediately (see Figure 16.13). You can then select

OK .

After reorganizing the table, be sure to capture the new statistics on the table. This is done using the command:

runstats on table johndoe.org 

Since you also have at least one index on this table, you should capture the index statistics at the same time. Therefore it would be better to run the command:

runstats on table johndoe.org and indexes all 

If this table contains a lot of data, and/or you have a lot of indexes defined, you should capture detailed statistics on the table and indexes. This is done using the command:

runstats on table johndoe.org 
with distribution and detailed indexes all 

This provides the optimizer with the most complete statistics on the table data and indexes. You can also gather statistics using the Control Center. Open the tables list, select the table you wish to reorganize, right-click on the table, and choose the

Run Statistics option. This displays the dialog shown in Figure 16.14.

On the

Column tab in this dialog you can tell DB2 whether you want to capture basic statistics or if you want to capture distribution statistics on the table. You can then go to the Index tab to choose the index on which you want the data clustered (see Figure 16.15).

You can also specify if you want to capture detailed statistics that help the optimizer choose the most efficient access plans. Open the

Schedule tab and choose to either schedule the run statistics operation for a later time or to run it immediately. You can then select

OK .

Chapter 12, Maintaining Data, for more details about the

REORG, REORGCHK , and

RUNSTATS utilities.