Section 16.6. Lack of Proper Maintenance

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.

Figure 16.12. REORG utility options

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.

Figure 16.13. Scheduling a REORG

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.

Figure 16.14. The RUNSTATS utility: Column tab

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).

Figure 16.15. The RUNSTATS utility: Index tab

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.

NOTE

Refer to Chapter 12, Maintaining Data, for more details about the REORG, REORGCHK, and RUNSTATS utilities.