BMCSTATS options for collecting statistics

When you collect statistics, you choose parameters on the first BMCSTATS panel. As the following sample shows, this panel lists the parameters by category.

BMCSTATS parameters

• TABLE specifies whether to gather statistics on table columns:
  • Y—Collects statistics on all table columns (see BMCSTATS-options-for-tables).
  • N—Does not collect statistics on table columns.

  • S—Collects statistics on specific tables and columns.

    You can also select column groups for collection.

    Success

    Tip

    If you specify TABLE S, review the values on the Select Tables panel the next time that you specify this action. You might need to change the values from an earlier execution.

• INDEX indicates whether to run BMCSTATS on the indexes in the table space. If you specify Index all Y when you are collecting statistics from an image copy, DASD MANAGER PLUS uses the DB2 data sets to obtain index statistics. The default is Y.
• SPACEONLY specifies whether to gather only space statistics from the ICF catalog. If you need information only about size and extents, specify Y for the SpaceOnly option. The default is N.

• NUMCOLS is the number of key columns from left to right that is concatenated to evaluate the value (1 through 64) for each frequent value that the product collects. The default value of 1 collects frequent values for the first key column only.

  BMCSTATS returns all combinations up to the NUMCOLS value. For example, if you specify NUMCOLS 5, BMCSTATS returns the following values: column 1; columns 1 and 2; columns 1, 2, and 3; columns 1, 2, 3, and 4; and columns 1, 2, 3, 4, and 5. For this information to be useful to the optimizer, you also must specify UPDATEDB2 Y.

• IXNUMQUANTILES specifies the number of quantiles to collect for index key column processing. Statistics for the specified set of columns are divided into groups based on the number of quantiles specified. Index columns must be in all ascending or all descending order for DASD MANAGER PLUS to collect this type of statistic. Otherwise, DASD MANAGER PLUS ignores this option.

  Warning

  Note

  BMCSTATS invokes DSNUTILB to collect histogram data for key columns only if you specified UPDATEDB2 A or UPDATEDB2 P on the BMCSTATS parameters panel (see the above figure). Specifying IXNUMQUANTILES invokes DSNUTILB.

• NUMQCOLS specifies the number of index columns on which to collect quantiles.
• FREQVAL indicates whether BMCSTATS should collect frequency statistics for columns when using the TABLE option. If you do not specify a value for this option, the value specified by the FREQVAL keyword in the installation options is used. The product is shipped with an installation default of Y.
• COUNT is the maximum number of frequent values to collect (for table columns, first key columns, concatenated table columns, and concatenated key columns). Type a value from 1 through 300. The default is 10.

• FREQTYPE specifies whether to collect the most (M) frequently occurring values in a column, the least (L) frequently occurring values in a column, or both (B) types of values. The default is M. The value that you specify for this field also applies to COLGROUP for column group statistics and to Keycard for concatenated key column statistics.

  Warning

  Note

  Specifying a value of L or B in this field can be expensive in terms of CPU processing time and should be considered before choosing these values.

• SAVESTATS specifies whether to save the statistics in the DASD MANAGER PLUS database. The default is Y.

  If you specify SAVESTATS N (to not save BMCSTATS statistics data) and you also specify DELETEAGE to delete statistics rows that are older than a number of days that you specify, DASD MANAGER PLUS does not delete the rows. The SAVESTATS command controls the statistics table handling routines. If you specify SAVESTATS N, no statistics table processing occurs.

• DELETEAGE specifies whether to automatically delete statistics for an object after a certain amount of time. Specify the number of days from 0 through 32767 to keep statistics.

  The number that you specify is the minimum age of object statistics. For example, to delete all statistics entries on this object that are at least 30 days old, enter 30 for this parameter. The default is 32767, which specifies not to delete any statistics.

  Warning

  Note

  This command has no affect if you specify SAVESTATS N.

• REPORT specifies whether to print a report into the job output of the statistics. The default is Y.
• UPDATEDB2 specifies whether to update the DB2 catalog with the statistics that you gather. If the existing DB2 catalog values provide efficient optimizer choices, type N. The product ignores this field for volumes and storage groups. Valid values are A (All), P (Accesspath), S (Space), or N (None). The default is N.
• HISTORY specifies whether to update the DB2 Catalog History tables.
• DELETEHISTAGE specifies how long to keep the DB2 Catalog History table entries before deleting them.
• RESET ACCESSPATH resets access path statistics in the DB2 Catalog to -1 for all tables in the specified table space and related indexes. Real-time statistics and space statistics in the DB2 Catalog are not reset.
• RESETHISTORY inserts rows into the following tables, for the objects specified in the RESET ACCESSPATH command:
  • SYSIBM.SYSTABLES_HIST for tables
  • SYSIBM.SYSINDEXES_HIST for indexes
• OMITCARD0 specifies whether to bypass updating the DB2 catalog for objects in which BMCSTATS finds a zero cardinality. The default is N.
• BADOBJECTRC specifies the return code that BMCSTATS will set if it is unable to process a requested object due to object characteristics (unsupported objects), invalid status, object serialization, or object authorization failures. The default is to issue return code 4. Regardless of the setting of this option, BMCSTATS processing continues.

• RECALL specifies whether to recall archived data sets to collect statistics on them.

  The RECALL option opens the data sets that initiate a recall. N skips the object and returns a code 4, but continues with other objects. The default is N.

• FORCEROLLUP specifies whether to roll up the partition level statistics to the object level in cases in which not all partition statistics are available. The default is N.
• MSGLEVEL specifies the level of messaging. The default of 0 provides standard messaging. Specifying messaging level 1 provides some additional informative messages about progress and timestamps.
• 911ACTION specifies the action BMCSTATS will take if a -911 SQL error occurs during a DELETE, INSERT, or UPDATE operation.
  • S specifies if an SQL -911 error occurs, stop all processing, issue error messages, and issue a return code 8 at termination.
  • I specifies if an SQL -911 error occurs, stop processing the current object, issue warning messages, continue on with the next available object, and issue a return code 4 at termination. I is the default.
• TASKS is the number of concurrent tasks for gathering statistics. Specify the number of multitasking levels that are used for processing partitioned objects by typing a value from 1 through 16. The default is 5.

• TSSAMPLEPCT specifies whether to use random sampling for statistics.

  Sampling greatly reduces resource consumption in producing statistics. If you need to use sampling, consider specifying a percentage of pages to sample.

  The following table defines the sample table space parameter options.

  BMCSTATS sample table space parameter options

  Option	Meaning and use
  N	Do not sample. Type N to process all pages, including very small tables on a multi-table table space that contains both large and small tables. N is the default.
  Y	Sample 25 percent of the pages. If you type Y, BMCSTATS does not sample objects with fewer than 1000 pages. 1
  1 through 50	Sample the specified percentage of pages for statistics. 1

   ¹  The numPages/numTables must be greater than the minimum pages listed, or BMCSTATS will process all pages.

  For more information about sampling, see Sampling-statistics.

• IXSAMPLEPCT specifies whether to use random sampling for statistics. Sampling greatly reduces resource consumption in producing statistics. If you need to use sampling, you should consider specifying a percentage of pages to sample.

  While sampling indexes reduces resource consumption, restrictions affect when you can specify this option. In the following instances, DASD MANAGER PLUS suppresses index sampling and processes the entire index:

  • Specifying an index sampling option in addition to UPDATEDB2 Y

    To eliminate this restriction, you can specify the UPDCATIXS=Y installation option during installation to allow the catalog to be updated with index sampled statistics.

    Error

    Warning

    Index sampling might produce statistics that cause SQL optimizer access selection problems. BMC recommends that you review the index sampled statistics for your indexes before deciding to use them for updating the catalog.

  • Specifying an index sampling option and an option to collect histogram statistics for indexes

    When collecting histogram statistics, BMCSTATS invokes DSNUTILB (which also does not support index sampling).

  • Index has less than 1000 pages

    The following table defines sample index parameter options.

    BMCSTATS sample index parameter options

    Option	Meaning and use
    N	Do not sample. Type N to process all pages, including very small indexes. N is the default.
    Y	Sample 25 percent of the pages. If you type Y, BMCSTATS does not sample objects with fewer than 1000 pages.
    1–50	Sample the specified percentage of pages for statistics. BMCSTATS does not sample objects with fewer than 1000 pages.

    For more information about sampling, see Sampling-statistics.

• ATBWORKAREA specifies if the cardinality and frequency work areas will be placed above or below the 2G bar.

  • Y specifies work areas will be placed above the 2G bar.
  • N specifies work areas will be placed below the 2G bar.

  Above the 2G bar can reduce normal 32 bit addressed storage requirements. Below the bar reduces CPU consumption due to additional dynamic address translation overhead. N is the default.

• OPTIMIZECOMMIT specifies how SQL COMMIT(s) after SQL DELETE, INSERT, and UPDATE will occur.

  • Y specifies COMMIT strategy for DELETE, INSERT, and UPDATE will be optimized.
  • N specifies COMMIT(s) will occur after every DELETE, INSERT, and UPDATE.

  Optimized COMMIT(s) reduce overall SQL overhead. Unoptimized COMMIT(s) reduce deadlocks and deadlock timeouts (SQL error -911). Y is the default.

• QUIESCEINTERVAL (100001000000) specifies the number of object partitions processed before releasing and reinitializing O/S storage pools.

  • 0 specifies no QUIESCEINTERVAL.
  • 0–1000000 specifies the number of partitions processed before storage pools will be reorganized.

  QUIESCEINTERVAL can reduce the risk of storage depletion errors due to storage fragmentation. Storage fragmentation can occur after a large number of object partitions are processed. Use QUIESCEINTERVAL with large object sets and wildcards if storage depletion errors occur (Sx78 ABENDs).

• SORTNUM specifies the number of temporary data sets that DFSORT can use for sorting and is used only with COLGROUP. Specify this field with the SORTDEVT field. You can specify an integer value of 0 through 99.
• SORTDEVT specifies the device type for dynamic allocation of the sort work files that DFSORT can use for sorting and is only used with COLGROUP. Specify this field with the SORTNUM field.