NGTSTATS syntax

This topic describes the NGTSTATS syntax statement and its options. An option block indicates that there is a separate section for that option following the main syntax diagram.

 

Related topics

SYSIN-statement-syntax

Input for BMC AMI Stats

BMC-AMI-Stats-DD-statements 

NGTSTATS_TABLESPACE.png

EXCLUDE_TABLESPACE.png

NGTSTATS

Keyword that begins the statistics collection statement.

Value

Description

NGTSTATS TABLESPACE

You can use a specific qualified table space name (databaseName.tableSpaceName), use wildcards (databasePattern.tableSpacePattern), or specify an object set.

NGTSTATS TABLESPACES

OBJECTSET objectSetName

Specifies the name of an existing object set. For more information, see the chapter about using object sets in the Using object setstopic.

EXCLUDE TABLESPACE

Indicates an object to be excluded from processing.

You can use either a specific qualified table space name (databaseName.tableSpaceName) or use wildcards (databasePattern.tablespacePattern):

  • If you are using a qualified table space name, you must specify both the database name and the table space name. Also, the database name cannot be DSNDB01, DSNDB06, or DSNDB07.

    Important

    You can use DSNDB04 only in the EXCLUDE TABLESPACE command.

  • You can use the percent (%) and underscore (_) symbols as wildcards to construct a query that obtains all table spaces that match the specified pattern:
    • Use % to represent any character string.
    • Use _ to represent a single character. A line will print on SYSPRINT and SUMMARY for each found object.

Statistics specification block

statistics_spec_block_SPE1911.png

TABLE(ALL)

(optional) BMC AMI Stats collects statistics for all tables in the table space. BMC AMI Stats collects statistics for all tables by default, even if you omit TABLE(ALL). Collecting statistics for only specific tables is not an option.

COLUMN

(optional) BMC AMI Stats collects statistics for index columns. If you omit COLUMN, BMC AMI Stats collects statistics only for the first key column for each index.

ALL

BMC AMI Statscollects statistics for all index columns.

Important

If you specify COLUMN(ALL) and READTS(YES), BMC AMI Stats collects table column cardinality statistics.

NONE

BMC AMI Stats

 does not collect any column statistics for the table space except for the first key column of each index.

columnName

BMC AMI Statscollects statistics for the columns listed within the parentheses and the first key column for each index.

Important

  • If you specify this option, BMC AMI Stats automatically specifies READTS(YES).
  • Column names can be qualified or non-qualified.
  • Expect increased resource consumption when you specify more columns names.

Restrictions

Do not specify:

  • Table spaces that contain wildcards
  • Object sets
  • Multi-table table space

COLGROUP specification block

INDEX(ALL) or INDEXES(ALL)

(optional) BMC AMI Stats collects statistics for all indexes by default, even if you omit INDEX(ALL) or INDEXES(ALL). Collecting statistics for only specific indexes is not an option.

Distributed options specification block

Common options syntax

 COLGROUP specification block

colgroup specification syntax block.png

COLGROUP

COLGROUP indicates that the specified columns are to be treated as a group. BMC AMI Stats collects correlation statistics for the specified columns. 

Important

  • If you specify this option, BMC AMI Stats automatically specifies READTS(YES).
  • To specify multiple column groups, use multiple COLGROUP options.

Restrictions

  • The COLGROUP option can contain a maximum of 255 COLGROUP specifications.
  • A maximum of 127 column names are allowed per COLGROUP specification.
  • The length of a COLGROUP value cannot exceed the maximum length of the COLVALUE column (2000) in the SYSIBM.SYSCOLDIST catalog table. 
  • The COLGROUP cannot be specified if wild cards or object sets are being processed.
  • The COLGROUP cannot be specified for a multi-table table space.

columnName

columnName with one or more names of columns that are part of the column group. You can specify a list of column names. If specifying multiple columns, separate each name with a comma.

FREQVAL

COUNT (numberValues)

[MOST|LEAST|BOTH]

FREQVAL is specified for each COLGROUP to collect frequency data on the COLGROUP. The following additional considerations apply:

  • If you do not specify FREQVAL, COLGROUP uses the +FREQ value. If +FREQ is not specified in the BMC AMI Utilities configuration parameters, or the STTPARMS DD or if +FREQ(NONE) is specified, and you do not specify the FREQVAL sub-parameter of COLGROUP, COLGROUP collects only cardinality data (no frequency data).
  • If you do not specify COUNT, COLGROUP uses the +COUNT value. If +COUNT is not specified in the BMC AMI Utilities configuration parameters or STTPARMS DD, and you do not specify COUNT, COLGROUP uses the default value, 10.
  • If you do not specify MOST, LEAST, or BOTH, COLGROUP uses the +FREQ value. If +FREQ is not specified in the BMC AMI Utilities configuration parameters or STTPARMS DD or if +FREQ(NONE) is specified, and you do not specify MOST, LEAST, or BOTH, COLGROUP uses the default value, MOST.

The parameters associated with KEYCARD do not apply to COLGROUP.

Distributed options syntax block

distributed_options_spec_block_PTF_BQU1777.png

KEYCARD FREQVAL NUMCOLS numberColumns COUNT numberValues [MOST|LEAST|BOTH]

BMC AMI Stats collects and posts statistics as follows:

FREQVAL NUMCOLS numberColumns

Collects column group distribution statistics for all intermediate groupings of columns in each index. The default value is 1 (one column).

COUNT numberValues

Collects, for each index, the frequency distribution statistics for the specified number of values in a column. The default number of values is 10. You can specify whether to collect the most frequent (MOST), least frequent (LEAST), or both (BOTH). The default value is MOST.

Important

You can use the BMC AMI Utilities parameters to set the default values for NUMCOLS, COUNT, and MOST | LEAST| BOTH. For more information, see BMC-AMI-Stats-parameters-STTPARMS-DD.

TBCOLFREQ

BMC AMI Statscollects single column frequency statistics for all or a list of table columns by reading the table space. When you specify TBCOLFREQ, the KEYCARD FREQVAL COUNT, MOST, LEAST, and BOTH values apply to data collection.

Important

  • If you specify this option, BMC AMI Stats automatically specifies READTS(YES).
  • If you do not specify the COLUMN option, BMC AMI Stats automatically specifies COLUMN(ALL).

Reset specification block

reset_spec_block.png

RESET ACCESSPATH

Resets the access-path statistics for the tables and indexes

This keyword does not affect real-time statistics or space-related statistics. If you specify RESET ACCESSPATH, you cannot specify any other keywords for collecting specific statistics.

HISTORY ACCESSPATH

Inserts rows into SYSTABLES and SYSINDEXES for each affected object after BMC AMI Stats resets statistics via RESET ACCESSPATH

 

  • 13.1
Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*