Using BMC AMI Stats

This example (the simplest) tells BMC AMI Stats to collect statistics for a single table space, ACCTGTS, in database PAYRGDB:

By default:

• The TABLE(ALL) and INDEX(ALL) keywords are implicitly specified.
• For each index, BMC AMI Stats collects cardinality statistics only for the first key column.

To collect statistics for all indexed columns, you would need to include COLUMN(ALL), as the next example shows.

In both examples, the table space page file is not read.

The following example tells BMC AMI Stats to collect statistics for a single table space, ACCTGTS, in database PAYRGDB and to collect table space column cardinality statistics:

In this example, READTS(YES) has been specified to collect cardinality statistics on both indexed and table space columns.

If READTS(YES) is specified:

• The table space page file is processed.
• Table space column cardinality data (non-indexed columns) are collected.
• SYSIBM.SYSCOLUMNS and SYSIBM. SYSCOLSTATS are updated for each non-indexed column.
• NGTSTATS reads the table space to calculate non-indexed column statistics. The advantage of this process is that NGTSTATS can accurately collect all the basic optimizer statistics for all table space, tables, and columns. The disadvantage is that it requires additional resources to collect these statistics.
• If READTS(YES) is not specified, statistics are collected by reading the indexes, not the table space page files. The advantage of this process is that fewer resources are consumed. The disadvantage is that index analysis alone cannot collect some statistics, including the following statistics:
  • Non-indexed table columns
  • SYSCOLSTATS data when no partitioned index exists on a partitioned table space (for example, when you have only DPSIs).
• Use the COLUMN keyword to select all columns or a subset of columns, in a table space. For more information, see COLUMN keyword.
• READTS is turned on automatically if you specify a COLUMN list or frequency distribution is requested via the TBCOLFREQ keyword.

For more information about table space statistics, see Collecting distribution statistics for a table space. 

The following examples collect distribution statistics for table space ACCTGTS:

In this example, BMC AMI Stats collects column cardinality statistics for all indexed columns for each index. Additionally, it collects column group distribution statistics for each index on one column only, the first key column. It collects the15 most frequent values.

For each index, the first statement collects column statistics for all indexed columns. The second statement collects the specified frequency statistics for each index. The specification is to collect distribution statistics from two concatenated columns for three values, and to include both the most and least values.

In both examples, the table space page file is not read.

Use the TBCOLFREQ keyword to collect frequency distribution data on each column in the table space.

In this example, the 15 most frequent values will be collected for each first key column with data from the indexes page files, and the 15 most frequent values will be collected for all other columns in the table space with data from the table space page file.

TBCOLFREQ(YES) implies READTS(YES):

• The table space page file is processed
• TBCOLFREQ controls collection of frequency statistics for non-indexed columns
• TBCOLFREQ consumes a high number of resources.
• For each column, distribution statistics are written to SYSIBM.SYSCOLDIST and SYSIBM.SYSCOLDISTSTATS

This example uses the UPDATE keyword with ACCESSPATH to tell BMC AMI Stats to update only catalog tables that Db2 uses to determine SQL access paths:

The wildcards (the * symbol in PAYRG* and ACCTG*) tell BMC AMI Stats to collect statistics if the database name begins with PAYRG and the table space name begins with ACCTG.

This example uses the HISTORY keyword with SPACE to tell BMC AMI Stats to write space statistics to the Db2 catalog history table:

The next example tells BMC AMI Stats not to write statistics to the history tables:

This example sets the REPORT keyword to YES, which tells BMC AMI Stats to write reports to the RUNSTATS DD:

This example collects table space statistics from object set TESTOBJECTSET. This object set was previously defined in one of the BMC products that supports object sets:

This is an example of using BMC AMI Stats with BMC AMI Utility Manager