BMCSTATS options
The following figure shows the BMCSTATS command syntax:
BMCSTATS table options
COLGROUPS options
The following table lists the availability of batch BMCSTATS options by object type. For example, the INDEX option has checks in the TT and TS column, which means that you can specify INDEX when the object type is a table space set or a table space. Values in boldface are the default values.
Object types are as follows:
- INDEX (IX)
- INDEXSPACE (IS)
- STOGROUP (SG)
- TABLESPACE (TS)
- TABLESPACESET (TT)
- VOLUME (VL)
Batch BMCSTATS options
Option | Value | Description | Object type | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
TT, TS | IX, IS | SG | VL | |||||||||||
911ACTION | STOP IGNORE | Specifies the action BMCSTATS will take when it encounters a -911 SQL error during the repository update phase: (default) STOP specifies that BMCSTATS will stop processing on the current object and issue a final return code 8. IGNORE specifies that BMCSTATS will bypass the update phase for the current object and continue processing. | X | X | X | X | ||||||||
ATBWORKAREA | Y N | Specifies whether the cardinality and frequency work area will be above the 64-bit bar and if the collection routine will run in AMODE 64: Y indicates that BMCSTATS will allocate and process the work areas above the 64-bit bar. (default) N indicates that BMCSTATS will allocate and process the work areas below the 64-bit bar. | X | X | NA | NA | ||||||||
BADOBJECTRC | 0 through 8 | Specifies the return code that BMCSTATS sets if it is unable to process a requested object due to object characteristics, for example:
Regardless of the value of this option, BMCSTATS processing continues. 4 is the default. | X | X | NA | NA | ||||||||
COLGROUPCOUNT (only with COLGROUP) | 1 through 300 COUNT | Specifies the number of frequent values to collect for the preceding COLGROUP clause. If this option is not specified for a COLGROUP, the default is 10 | X | NA | NA | NA | ||||||||
COLGROUPFREQTYPE | M (MOST) B (BOTH) L (LEAST) | Specifies the type of frequent values to collect for the preceding COLGROUP clause. Column frequencies can be collected using most frequent values (M), least frequent values (L), or both (B) M (MOST) is the default | X | NA | NA | NA | ||||||||
COLGROUPFREQVAL | NO YES | Specifies whether to collect frequent values for the preceding COLGROUP clause NO is the default | NA | NA | NA | NA | ||||||||
COLUMN (only with TABLE) | colName or ALL | Names the columns on which to gather statistics (for example, COL1, COL2), or if you specify ALL, statistics are gathered on all columns. The colName can be 1 to 30 characters in the format col1, col2, col3 with a maximum of 80 column names. You can only specify the COLUMN keyword once for each TABLE keyword specified. The column list is associated with the previous TABLE option. | X | NA | NA | NA | ||||||||
COUNT | 1 through 300 10 | Specifies the number of frequent values to collect for each concatenation of key columns and table columns COUNT is ignored for XML node ID indexes and XML indexes. 10 is the default. | X | X | NA | NA | ||||||||
DELETEAGE | 0 through 32767 | Deletes statistics older than this number of days for the specified objects 32767 is default. | X | X | X | X | ||||||||
DELETEHISTAGE | 0 through 32767 | Deletes statistics in the history tables older than this number of days for the specified objects 32767 is the default. | X | X | NA | NA | ||||||||
FORCEROLLUP | Y N | Specifies whether to roll up the partition level statistics to the object level if some partition statistics are unavailable Partition-level column statistics that have been collected by BMCSTATS cannot be used for rollup by the IBM RUNSTATS program (or vice versa). N is the default. | X | X | NA | NA | ||||||||
FREQTYPE | M (MOST) B (BOTH) L (LEAST) | Collects column frequencies using most frequent values (M), least frequent values (L), or both (B) When specified with the TABLESPACE option, FREQTYPE affects index and index-partition columns if you also specify INDEX Y. FREQTYPE affects index columns, index-partition columns, table columns, and correlated key columns. M (MOST) is the default. | X | X | NA | NA | ||||||||
FREQVAL | Y N | Specifies whether BMCSTATS should collect frequency statistics for columns when using the TABLE option The COUNT option determines the number of frequencies to collect. The FREQTYPE option determines the type of frequencies collected. If you specify FREQVAL without a value, Y is the default. If you do not specify this option, the FREQVAL value in the installation options is the default. The product is shipped with an installation default of Y. FREQVAL is ignored for XML node ID indexes and XML indexes. | X | X | NA | NA | ||||||||
HISTOGRAM (only with COLGROUP) | Y N | Specifies whether to collect histogram statistics, which provide additional information about the cardinality and distribution of a column or group of columns UPDATEDDB2 A or P is required to use this option. If UPDATEDDB2 A or P is not specified, HISTOGRAM is ignored. HISTOGRAM Y is ignored for XML node ID indexes and XML indexes. N is the default. | X | NA | NA | NA | ||||||||
HISTORY | L (ALL) C (ACCESS PATH) S (SPACE) N (NONE) | Updates the statistics history in the DB2 catalog N (NONE) is the default. | X | X | NA | NA | ||||||||
INDEX | Y N | Specifies whether to gather statistics on the indexes in the table space If you also specify PARTITION, BMCSTATS will process only indexes with a matching partition. If you also plan to collect histogram statistics by using IXNUMQUANTILES, ensure that all index columns are in all ascending or all descending order. Y is default. | X | NA | NA | NA | ||||||||
INVALIDATECACHE | Y N | Specifies whether to invalidate the dynamic cache for an object Y is the default. | X | X | NA | NA | ||||||||
IXNUMQUANTILES | 1 through 100 | Specifies the number of quantiles in which to divide the group of keys The IXNUMQUANTILES option can be specified only when index statistics are also being collected using INDEX Y or from an object set. Index columns must be in all ascending or all descending order for DASD MANAGER PLUS to collect this type of statistic. Specifying IXNUMQUANTILES is optional. If you do not specify this option, no histogram statistics for indexes are collected. Specifying IXNUMQUANTILES invokes DSNUTILB. UPDATEDB2 L (ALL) or C (ACCESSPATH) is required to use this option. If UPDATEDB2 L or C is not specified, IXNUMQUANTILES is ignored. IXNUMQUANTILES is ignored for XML node ID indexes and XML indexes. | X | X | NA | NA | ||||||||
IXSAMPLEPCT 2 | 1 through 50 | Samples the specified percentage of index space pages If the index has less than 1000 pages, BMCSTATS processes the entire index. | X | X | NA | NA | ||||||||
MSGLEVEL | 0 1 | Specifies normal messaging (0) or additional informational messages about progress, timestamps, and default options settings (1) 0 is the default. | X | X | NA | NA | ||||||||
NPICACHEACTION | NONE ATBCACHE LIMIT | Specifies that for multi-dataset non-partitioned indexes, cache storage below the bar will be limited to the value set by NPICACHESTHRESH. When exceeded, the key cache will be moved above the bar and caching will continue. Cache storage held below the bar will be released. NONE is default. | NA | X | NA | NA | ||||||||
NPICACHESTHRESH | Integer 100 | Specifies the amount of storage in megabytes that can be used below the bar before the action specified by NPICACHEACTION occurs, but do not cache above the bar. 100 is default. | NA | X | NA | NA | ||||||||
NPICACHEDSNUM | Integer 25 | Specifies the number of datasets required in a multi-dataset non-partitioned index before the values specified NPICACHEACTION in NPICACHESTHRESH are observed. 25 is the default. | NA | X | NA | NA | ||||||||
NUMCOLS | 1–64 | Specifies the maximum number of index key columns to concatenate when collecting correlated index key statistics NUMCOLS is ignored for XML node ID indexes and XML indexes. 1 is the default. | X | X | NA | NA | ||||||||
NUMQCOLS | 1–64 | Specifies the number of index columns on which to collect histogram data If you specify a number larger than the number of index columns, data will be collected for all columns. NUMQCOLS is ignored for XML node ID indexes and XML indexes. 1 is the default. | X | X | NA | NA | ||||||||
NUMQUANTILES (only with COLGROUP) | 1–100 | Specifies the number of quantiles in which to divide the group of rows The NUMQUANTILES option is specified only for table spaces and only after a HISTOGRAM option. Specifying NUMQUANTILES is optional. NUMQUANTILES is ignored for XML node ID indexes and XML indexes. 100 is the default. | X | NA | NA | NA | ||||||||
OMITCARD0 | Y N | Specifies whether to bypass updating the Db2 catalog for objects in which BMCSTATS finds a zero cardinality N is the default. | X | X | NA | NA | ||||||||
OPTIMIZECOMMIT | Y N | Specifies whether to do an SQL commit after every insert to the stats tables Specifying N indicates tells BMCSTATS to do an SQL commit after every SQL DELETE, INSERT, or UPDATE. Specifying Y indicates that BMCSTATS will minimize the number of SQL commits during the repository update phase. Specifying Y increases the risk of SQL -911 errors by increasing the time that BMCSTATS will hold Db2 locks. Specifying N will decrease the SQL -911 risk but increase Db2 processing time. If you do not specify OPTIMIZECOMMIT, the value specified in the installation options module (DOPTS) is used. The OPTIMIZECOMMIT DOPTS option default is Y. N is the default. | X | X | NA | NA | ||||||||
PARTITION | 1 through 4096 | Specifies a partition on which to collect statistics You can repeat PARTITIONn multiple times. If you omit PARTITION, BMCSTATS collects statistics for all partitions unless you use an object set to limit partitions. | X | X | NA | NA | ||||||||
QUIESCEINTERVAL | 0 10000 through 1000000 | This option enables users with a very large number of Db2 objects per job to avoid potential storage depletion abends. The option allows you to specify a processing interval after which BMCSTATS will stop processing new Db2 objects, complete inflight work, and release and consolidate its main storage holdings. Only then will BMCSTATS start to process new objects. 0 is the default. | X | X | NA | NA | ||||||||
RECALL | Y N | Specifies whether to recall archived data sets Specifying Y recalls the object. Specifying N tells BMCSTATS to bypass the object. N is default. | X | X | NA | NA | ||||||||
REPEATABLE | Y N Integer | Specifies whether to use seed value for sampling repeatable statistics.
| X | X | NA | NA | ||||||||
REPORT | Y N | Tells BMCSTATS whether to print a report of the statistics collected Y is the default. | X | X | X | X | ||||||||
RESET | N ACCESSPATH | When ACCESSPATH is specified, this option resets access path statistics for all tables in the specified table space and related indexes. N is default. | X | NA | NA | NA | ||||||||
RESETHISTORY | N ACCESSPATH | When the ACCESSPATH option is selected, this option inserts rows into the following tables for each object reset path that is selected
N is the default. | X | NA | NA | NA | ||||||||
SAMPLE 1 | Y N T I | Specifies the type of sampling to use for statistics (samples 25%). If the object has less than 1000 pages or if the number of pages divided by the number of tables is less than 1000, BMCSTATS processes the entire object. Type T to restrict sampling by table space. Type I to restrict sampling by index. N is the default. | X | X | NA | NA | ||||||||
SAVESTATS | Y N | Specifies whether to save the statistics in the historical database Y is the default. | X | X | X | X | ||||||||
SORTDEVT | deviceType | Specifies the device type for sort data sets for histogram statistics on DPSIs | X | NA | NA | NA | ||||||||
SORTNUM | 0 through 99 | Specifies the number of temporary data sets for histogram statistics on DPSIs | X | NA | NA | NA | ||||||||
SPACEONLY | Y N | Specifies whether to gather space statistics only (SPACE and EXTENTS) N is the default. | X | X | NA | NA | ||||||||
TABLE | owner.TableName ALL | Specifies the table on which to gather column statistics Specify a table name of up to 257 characters in the format owner.tableName with a maximum of 128 characters for owner, 1 character for period, and 128 characters for tableName, or specify ALL. BMCSTATS collects frequencies for column values by default, as controlled by the COUNT option. All columns of all tables associated with the table spaces are processed if you specify ALL. If you specify a value of N for FREQVAL, BMCSTATS does not collect frequency values on table columns. You can repeat TABLE owner.tableName multiple times. | X | NA | NA | NA | ||||||||
TASKS | 1 through 16 5 | Numbers the multitasking levels used 5 is the default. | X | X | NA | NA | ||||||||
TSSAMPLEPCT | 1 through 50 | Samples the specified percentage of table space pages If the object has less than 1000 pages or if the number of pages divided by the number of tables is less than 1000, BMCSTATS processes the entire object. | X | X | NA | NA | ||||||||
UPDATEDB2 | Specifies whether to update the DB2 catalog with the collected statistics N (NONE) is the default. | X | X | NA | NA | |||||||||
XBMID | xbmSSID | Used for the ZIIP ENABLED option, it specifies an XBM SSID that supports a zIIP-enabled process. If this option is not specified for ZIIP E, ASUSMAIN searches for a supporting XBM SSID. | X | X | NA | NA | ||||||||
ZIIP | E (ENABLED) D (DISABLED) B | Specifies whether DASD MANAGER PLUS attempts to use zIIP processors.
Return code 103 indicates that the XBM subsystem is not active. The XBM subsystem was only partially initialized when the operation request occurred. If you receive this return code, rerun the job. The message for return code 103 is as follows: ZIIP [NOT] ENABLED (returnCode) USING XBM SUBSYSTEM ssid For more information about the ZIIP option, see BMCSTATS performance and tuning. | X | X | NA | NA | ||||||||
1 BMCSTATS does not use index sampling if you are updating the Db2 catalog and did not previously specify the UPDCATIXS=Y installation option, or if you have specified to collect histogram statistics for indexes, or if the index has less than 1000 pages. Index sampling might produce statistics that cause SQL optimizer access selection problems. we recommend that you review the index sampled statistics for your indexes before deciding to use them for updating the catalog.
2 UPDATEDB2 A or P is required to use HISTOGRAM or IXNUMQUANTILES options; otherwise, the HISTOGRAM or IXNUMQUANTILES options are ignored. HISTOGRAM is also ignored for XML node ID indexes and XML indexes.