Using BMC AMI Stats
This topic describes how to use statements that control how BMC AMI Stats collects statistics.
Collecting cardinality statistics for a table space
Click here to expand...
This example (the simplest) tells BMC AMI Stats to collect statistics for a single table space, ACCTGTS, in database PAYRGDB:
NGTSTATS TABLESPACE PAYRGDB.ACCTGTS
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.
NGTSTATS TABLESPACE PAYRGDB.ACCTGTS COLUMN(ALL)
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:
NGTSTATS TABLESPACE PAYRGDB.ACCTGTS
READTS(YES)
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.
Collecting distribution statistics for a table space
Click here to expand...
The following examples collect distribution statistics for table space ACCTGTS:
NGTSTATS TABLESPACE PAYRGDB.ACCTGTS COLUMN(ALL)
KEYCARD FREQVAL NUMCOLS 1 COUNT 15 MOST
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.
NGTSTATS TABLESPACE PAYRGDB.ACCTGTS COLUMN(ALL)
KEYCARD FREQVAL NUMCOLS 2 COUNT 3 BOTH
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.
NGTSTATS TABLESPACE PAYRGDB.ACCTGTS COLUMN(ALL)
KEYCARD FREQVAL NUMCOLS 1 COUNT 15 MOST
TBCOLFREQ(YES)
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
Controlling what BMC AMI Stats writes to the catalog
Click here to expand...
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:
NGTSTATS TABLESPACE PAYRG*.ACCTG* UPDATE(ACCESSPATH)
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.
Controlling what BMC AMI Stats writes to the Db2 catalog history tables
Click here to expand...
This example uses the HISTORY keyword with SPACE to tell BMC AMI Stats to write space statistics to the Db2 catalog history table:
NGTSTATS TABLESPACE PAYRG*.ACCTG* HISTORY(SPACE)
The next example tells BMC AMI Stats not to write statistics to the history tables:
NGTSTATS TABLESPACE PAYRG*.ACCTG* HISTORY(NONE)
Important
If you omit the HISTORY keyword, BMC AMI Stats uses the value of the +HIST parameter. If the +HIST parameter is not set or is set to DFLT, BMC AMI Stats uses the Db2 zparm STATHIST value.
Controlling whether BMC AMI Stats writes reports
Click here to expand...
This example sets the REPORT keyword to YES, which tells BMC AMI Stats to write reports to the RUNSTATS DD:
NGTSTATS TABLESPACE PAYRG*.ACCTG* REPORT(YES)
Collecting statistics from an object set
Click here to expand...
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:
NGTSTATS TABLESPACE OBJECTSET TESTOBJECTSET
Running BMC AMI Stats with BMC AMI Utility Manager
Click here to expand...
This is an example of using BMC AMI Stats with BMC AMI Utility Manager
KEYCARD FREQVAL NUMCOLS 4 COUNT 10 BOTH REPORT(YES) UPDATE(NONE)
HISTORY(NONE) SAVESTATS(NO) DELETEAGE(30)
-
13.1