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.
NOTERefer to Chapter 12, Maintaining Data, for more details about the REORG, REORGCHK, and RUNSTATS utilities. |