First of all, the TABLE parameter specifies the table on which column statistics are to be gathered. TABLE(ALL) specifies that column statistics are to be gathered on all columns of all tables in the table space. So, for multi-table table spaces, the TABLE parameter can have a significant impact on what is being collected.
Likewise, the INDEX parameter is used to specify for which indexes you wish to gather statistics. RUNSTATS gathers column statistics for the first column of the index, and possibly additional index columns depending on the options that you specify. You will almost always want to specify INDEX(ALL) as opposed to specifying a particular index. The default is ALL.
Finally, keep in mind the new features of RUNSTATS in DB2 V8 that allow you to collect additional statistics. You can use RUNSTATS to collect frequency distributions for non-indexed columns or groups of columns, cardinality values for groups of non-indexed columns, and least-frequently occurring values, most-frequently occurring values, or both, for any group of columns. These are controlled using the COLGROUP and FREQVAL parameters. Here is an example of collecting cardinality statistics for a column group:
COLGROUP(EDLEVEL, JOB, SALARY)
This RUNSTATS uses COLGROUP to collect cardinality statistics on a the group of columns - EDLEVEL, JOB, and SALARY.
Dig Deeper on IBM DB2 management
Related Q&A from Craig S. Mullins
Our expert suggests that the best way to pass the SAP BW exam is to have hands-on job experience. But that's not all he has to say about ... Continue Reading
To export data from a DB2 table to a flat file, you need to run an export specifying the proper file format. The export utility exports data from a ... Continue Reading
Craig Mullins recommends two specific resources for learning how to create and support Binary Large Objects (BLOB) in DB2. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.