The catalog tables hold statistical information on data stored in the database, and the database manager uses these statistics to determine how it will access data for each individual SQL request. If the statistics are unavailable, then default values are used. Table 8 shows the key statistics used for access path selection, and identifies which catalog table they are in.
Table 8. Key Catalog Statistics Used for Path Selection
|
Table Name Column Name |
Description |
Default Value Used by Optimizer When Catalog Value is -1 | ||
|---|---|---|---|---|
|
SYSCATALOG ROWCOUNT NPAGES |
Total number of rows for this table. Number of pages in the dbspace that contain rows of this table. |
100 3 | ||
|
SYSDBSPACES NTABS NACTIVE NPAGES |
Number of tables in the dbspace. Number of active pages in the dbspace. Number of usable pages in the dbspace. |
3*NTABS | ||
|
SYSCOLUMNS COLCOUNT HIGH2KEY LOW2KEY AVGCOLLEN |
Number of distinct values in this column. Second highest value in this column. Second lowest value in this column. Average length of the column. |
| ||
|
SYSINDEXES FULLKEYCOUNT FIRSTKEYCOUNT NLEAF NLEVELS CLUSTERRATIO |
Number of distinct values of the full key. Number of distinct values of the first column of the key. Equals COLCOUNT for the index column. Number of leaf pages in the index. Number of levels in the index. Measure of how clustered an index is. |
| ||
|
SYSCOLSTATS VAL10 VAL50 VAL90 FREQ1VAL FREQ1PCT
|
The value at the tenth percentile. The value at the fiftieth percentile. The value at the ninetieth percentile. The most frequent value in the column. Number of rows that contain that column value, given as a percentage of the total number of rows. The second most frequent value in the column. Number of rows that contain that column value, given as a percentage of the total number of rows. |
| ||
| ||||
It is impractical for these statistics to be maintained on every INSERT, UPDATE, and DELETE operation; therefore, you must periodically update them in the catalog tables with the UPDATE STATISTICS or the UPDATE ALL STATISTICS statement.
Update your statistics whenever a table's contents change significantly.
Suppose you enter the following UPDATE STATISTICS statement:
UPDATE STATISTICS FOR TABLE MYTABLE
The database manager updates the statistics for MYTABLE in the catalog tables. However, the statistics are updated only for indexed columns. (For indexes having multicolumn keys, only the first column is updated.) Similarly, whenever you create a new index, the database manager automatically updates the statistics for index columns.
To update the statistics for all columns in MYTABLE (even those that are not indexed), enter the following statement:
UPDATE ALL STATISTICS FOR TABLE MYTABLE
The complete set of statistics produced by the ALL option may result in a better access strategy being selected by the optimizer component. However, the ALL option can greatly increase the processing time required to run the UPDATE STATISTICS statement. UPDATE ALL STATISTICS is recommended where queries have non-indexed columns with local predicates or queries have multi-column indexes with local or join predicates that are not on the first column of an index. The DB2 Server for VSE & VM SQL Reference manual describes exactly what operations affect each statistic. The SYS0001 DBSPACE, which contains the catalog tables, is a candidate for UPDATE ALL STATISTICS processing.
It is recommended that you schedule UPDATE STATISTICS activities during off-peak hours.
When working with preplanned application programs, ensure that the programs are re-preprocessed whenever the tables accessed by the application have significantly changed (for example, a 10%-20% or more change). Before re-preprocessing, ensure that statistics have been updated so that the optimizer is provided with the new characteristics of the data.
The DBS utility DATALOAD and RELOAD commands automatically collect the statistics for a table as part of the load operation; thus, it is not necessary to issue a separate UPDATE STATISTICS statement (although see the DB2 Server for VSE & VM Database Services Utility or the DB2 Server for VSE & VM Database Services Utility manuals for restrictions). If you want, you can suppress this automatic updating of statistics through the DBS Utility SET UPDATE STATISTICS command.
| Note: | Statistics are not updated when you use DATALOAD to load data into a view. |