Q
Problem solve Get help with specific problems with your technologies, process and projects.

Clarifying parameters of Runstats

Each of the parameters of the RUNSTATS utility has a specific and particular meaning and should be used according to the statistics desired.

When running DB2's Runstats utility, which of the following should be used?

TABLE(ALL) INDEX(ALL) KEYCARD
or
INDEX(ALL) KEYCARD
Does TABLE(ALL) need to be there?
Well, each of the parameters of the RUNSTATS utility has a specific and particular meaning and you should use the parameters according to the statistics that you wish to accumulate.

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:

 

RUNSTATS TABLESPACE DSN8D81A.DSN8S81E
TABLE(DSN8810.EMP)
COLGROUP(EDLEVEL, JOB, SALARY)

This RUNSTATS uses COLGROUP to collect cardinality statistics on a the group of columns - EDLEVEL, JOB, and SALARY.

Find more information on RUNSTATS

This was last published in December 2006

Dig Deeper on IBM DB2 management

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSQLServer

Close