RUNSTATS TABLESPACE syntax and options
RUNSTATS TABLESPACE utility control statements define operations completed by RUNSTATS utility jobs.
You can create a control statement with the ISPF/PDF edit function. After you create it, save it in a sequential or partitioned data set. When you create the JCL for running the job, use the SYSIN DD statement to specify the name of the data set that contains the utility control statement.
The following syntax and descriptions apply to RUNSTATS TABLESPACE control statements, including use of the INDEX keyword. For the syntax and options of RUNSTATS INDEX control statements, see RUNSTATS INDEX syntax and options.
RUNSTATS TABLESPACE syntax diagram
- 1 The TABLE keyword is not valid for a LOB table space.
- 2 You cannot specify INDEX if either USE PROFILE or DELETE PROFILE option is also specified.
- 3 INDEX(*) is an internal representation of INDEX(ALL) that Db2 uses only in the context of RUNSTATS profiles, and is not valid when specified in any RUNSTATS control statement. When you specify the INDEX(ALL) option in a RUNSTATS control statement that creates a profile, Db2 uses INDEX(*) in the PROFILE_TEXT column of the SYSIBM.SYSTABLES_PROFILES catalog table. However, you must specify INDEX(*) instead of INDEX(ALL)) if you modify the profile by updating the value of the PROFILE_TEXT column directly.
- 4 REGISTER NO is ignored for index processing.
- 1 The TABLESAMPLE keyword is only valid for single-table table spaces. Dropped tables are included in this count until REORG, COPY, and MODIFY RECOVERY are run.
- 2 When using TABLESAMPLE to sample multi-table table spaces or table spaces that are segmented and not partitioned, page sampling is not done and execution continues. The TABLESAMPLE keyword is not valid for a LOB table space.
- 3 If one type of PROFILE function is specified on one TABLE clause the same type of PROFILE function must be specified on all TABLE clauses.
- 1 If you specify TABLESAMPLE SYSTEM NONE to override the STATPGSAMP subsystem parameter, you can specify the SAMPLE option. Otherwise, SAMPLE is not allowed to be specified with TABLESAMPLE.
- 1 When USE PROFILE is specified with the TABLE (ALL) keywords and no profile exists for a target table, TABLE ALL INDEX ALL is used for the profile specification.
- 2 When USE PROFILE is specified and no profile exists for a target table, COLUMN ALL INDEX ALL is used for the profile specification.
- 1 The same column name must not be specified more than once. If all columns are listed in the COLUMN option, RUNSTAT will treat it as is, and does not replace it with the COLUMN(ALL) option. Users have to state explicitly if they want the COLUMN(ALL) option.
- 1 When the COUNT keyword is not specified, the utility automatically determines the count value and collects the most frequently occurring values.
- 1 The KEYCARD option is deprecated. The KEYCARD functionality is now built into the normal execution of the RUNSTATS INDEX utility and cannot be disabled.
- 2 When the COUNT keyword is not specified, the utility automatically determines the count value and collects the most frequently occurring values.
- 3 If the target is an XML index, histogram and frequency statistics are collected on the first key column of the XML index only, regardless of the NUMCOLS value.
- 1 The FROM EXISTING STATS clause is not valid if a column-spec, colgroup-spec, or INDEX keyword has been specified
- 1 You can change the default HISTORY value by modifying the STATISTICS HISTORY subsystem parameter. By default, this value is NONE.
RUNSTATS TABLESPACE option descriptions
- TABLESPACE database-name.table-space-name
- Specifies the table space (and, optionally, the database to which it belongs) on which table
space and table statistics are to be gathered. This keyword must not identify a table space in
DSNDB01 or work file databases, which consist of DSNDB07 objects and user-defined work file objects.
- LIST listdef-name
- Specifies the name of a previously defined LISTDEF list name. You can
specify one LIST keyword for each RUNSTATS control statement. When you specify this keyword with
RUNSTATS TABLESPACE, the list must contain only table spaces. Do not specify LIST with keywords from
the TABLE…(table-name) specification. Instead, specify LIST with TABLE
(ALL). Likewise, do not specify LIST with keywords from the
INDEX…(index-name) specification. You cannot specify index names with a
list. Use INDEX(ALL) instead.
If you specify LIST, you cannot specify the PART option. Instead, use the PARTLEVEL option on the LISTDEF statement. The TABLESPACE keyword is required to validate the contents of the list. RUNSTATS TABLESPACE is invoked once for each item in the list.
The partitions or partition ranges can be specified in a list.
- Identifies the name of the database to which the table space belongs.
The default value is DSNDB04.
- Identifies the name of the table space on which statistics are to be gathered.
If the table space that is specified by the TABLESPACE keyword is a LOB table space, you can specify only the following additional keywords: SHRLEVEL REFERENCE or CHANGE, REPORT YES or NO, and UPDATE ALL or NONE.
- PART integer
- Identifies a table space partition on which statistics
are to be collected.
integer is the physical partition number. It must be in the range from 1 to the number of partitions that are defined for the table space. The maximum is 4096.
You cannot specify PART with LIST.
- Specifies the
table on which column statistics are to be gathered. All tables must belong to the table space that
is specified in the TABLESPACE option.
You cannot specify the TABLE option for a LOB table space. However, you can specify the TABLE option with the LIST option, even if the specified list includes LOB table spaces. In this case, the TABLE keyword applies to only the non-LOB table spaces and is ignored for the LOB table spaces.
that column statistics are to be gathered on all columns of all tables in the table space. The
parentheses around ALL are
The TABLE option value cannot specify a LOB table. However, if TABLE(ALL) is specified, and one or more of the tables in the table space have a LOB column, no error is issued for the LOB tables. RUNSTATS gathers table and column statistics only for the non-LOB tables.
- Specifies the tables on which column statistics are to be gathered. If you omit the qualifier,
RUNSTATS uses the user identifier for the utility job as the qualifier. Enclose the table name in
quotation marks if the name contains a blank.
If you specify more than one table, you must repeat the TABLE option. Multiple TABLE options must be specified entirely before or after any INDEX keyword is specified. For example, the INDEX keyword cannot be specified between any two TABLE keywords.
- SAMPLE integer
- Indicates the percentage of rows that RUNSTATS is to sample when
collecting statistics on non-leading-indexed columns of an index or non-indexed columns. You can
specify any value from 1 through 100.
The default value is 25.
When a low SAMPLE value is specified, inaccuracies in sampling can result in inaccurate statistics and thus bad access paths. The smaller the sample, the more potential for inaccuracy, which can affect access paths.
You cannot specify SAMPLE for LOB table spaces.
If the STATPGSAMP subsystem parameter is set to YES and the target table space is a universal table space, SAMPLE is ignored. Instead, RUNSTATS uses TABLESAMPLE SYSTEM AUTO.
- USE PROFILE
that a stored statistics profile is to be used to gather statistics for a table. The statistics
profile is created by using the SET PROFILE option and is updated by using the UPDATE PROFILE
STATFDBK_PROFILE subsystem parameter is set to YES, statistics profiles are updated automatically
for recommendations that are generated during query optimization.
The column, column group, and index specifications are not allowed as part of the control statement, but are used when stored in the statistics profile.If no profile exists for the specified table, default statistics are collected:
- When a table name is not specified, TABLE ALL INDEX ALL is used for the profile specification.
- When a table name is specified, COLUMN ALL INDEX ALL is used for the profile specification.
When you specify USE PROFILE, the profile options are included in SYSPRINT in message DSNU1376I.
FL 507 Additionally, Db2 deletes existing statistics that are not included in the profile. All frequency, keycard, and histogram statistics that are not part of the profile are deleted from the catalog. These statistics are deleted for only the specified table or partition. Statistics are not deleted from catalog history tables. If you specify UPDATE NONE or UPDATE SPACE, no statistics are deleted.
- INCLUDE NPI or INCLUDE (NPI)
- Specifies that statistics are to be collected on the non-partitioned indexes that listed in the profile for the table. The INCLUDE NPI clause is not valid if the PART keyword is not specified at the RUNSTATS TABLESPACE level. You must specify the INCLUDE and NPI keywords together. The parentheses around NPI are optional.
- DELETE PROFILE
- Specifies that the existing RUNSTATS profile for the table is to
be deleted from the SYSIBM.SYSTABLES_PROFILES catalog
Column, column group, and index specifications are not allowed as part of the control statement when the DELETE PROFILE option is used. No statistics are collected when you specify this option in the RUNSTATS control statement.
- TABLESAMPLE SYSTEM
- Allows RUNSTATS to collect statistics on a sample of the data
pages from the table.
If the STATPGSAMP subsystem parameter is set to YES and the target table space is a universal table space, TABLESAMPLE SYSTEM AUTO is the default behavior for RUNSTATS. You do not need to specify this option on the RUNSTATS statement. The exception is if you want a specific sample size other than AUTO. In this case, specify a value for TABLESAMPLE SYSTEM; that value overrides the STATPGSAMP specification.
System sampling considers each page individually, including that page with probability P/100 (where P is the value of numeric-literal) and excluding it with probability 1-P/100. Unless the optional REPEATABLE clause is specified, each execution of RUNSTATS usually yields a different such sample of the table. The size of the sample is controlled by the integer parameter in parentheses, representing an approximate percentage P of the table to be returned. Only a percentage of the data pages as specified through the numeric-literal parameter is retrieved and used for the statistics collection. This keyword is valid only for single-table table spaces.
If you run RUNSTATS with the TABLESAMPLE SYSTEM AUTO keywords for an entire partitioned table space, and the number of rows in each partition varies greatly, a partition that contains rows on very few pages can be skipped. In such cases, a DSNU1375I message indicates each partition that is not sampled. It is best to run RUNSTATS again with the SAMPLE keyword at the partition level for each such partition.
The TABLESAMPLE keyword is not valid for a LOB table space.
When RUNSTATS TABLESPACE TABLE is run on a multi-table table space or a table space that is segmented and not partitioned, the TABLESAMPLE keyword is not used. Instead, all pages are scanned to collect statistics.
- Specifies the size of the sample to be obtained, as a percentage P. This value must be a
positive number that is less than or equal to 100 and greater than 0. For example, a value of 0.01
represents one one-hundredth of a percent, such that 1 row in 10,000 would be sampled, on average. A
value greater than 100, zero, or a value less than zero is treated by Db2 as an error. The smallest allowable positive
number for this option is 0.01 percent.
Depending on table space size and sampling rate that is used, it is possible that a partition is not included in the sample. In this case, RUNSTATS does not collect statistics for this partition, and might report warnings or errors for aggregate statistics.
When numeric-literal is specified, and real-time statistics are not available, Db2 issues message DSNU3343I.
- When "AUTO" is specified, RUNSTATS determines a sampling rate based on the size of the table
when RUNSTATS runs. The larger the table the smaller the sampling rate. The threshold for sampling
is when the table has more than 500,000 rows; otherwise all pages are read. The same threshold is
applicable for TABLESPACE sampling with PART option specified. The number of rows is obtained from
the real-time statistics
When AUTO is specified, and real-time statistics are not available, RUNSTATS sets the sampling rate to 100.
- Specifies that RUNSTATS is not to use system sampling. You can specify TABLESAMPLE SYSTEM NONE to override the value of the STATPGSAMP subsystem parameter for a particular RUNSTATS job. If you specify TABLESAMPLE SYSTEM NONE to override STATPGSAMP, you can specify the SAMPLE option. Otherwise, SAMPLE is not allowed to be specified with TABLESAMPLE.
- REPEATABLE integer
- Adding the REPEATABLE clause to the TABLESAMPLE clause ensures that repeated executions of RUNSTATS return the same sample. The integer parameter is a non-negative integer that represents the seed to be used in sampling. Passing a negative seed results in an error (DSNU048I). The sample set might still vary between repeatable RUNSTATS invocations. Variations can occur if activity against the table results in changes to the table data since the last time TABLESAMPLE REPEATABLE was run.
- SET PROFILE
- Specifies that RUNSTATS generates a RUNSTATS profile for the specified table from the options that are specified in the current RUNSTATS invocation. RUNSTATS stores the profile in the SYSIBM.SYSTABLES_PROFILES catalog table. No statistics are collected when you specify this option in the RUNSTATS control statement. For more information about the options that you can specify in a profile, and the syntax for specifying the options, see: Statistics profile syntax.
- FROM EXISTING STATS
- Specifies that RUNSTATS generate a statistics profile with options that are based on analysis of the statistics that currently exist for the specified table. This option can be specified only with the SET PROFILE option. The keywords used in the generated profile do not necessarily match those used to collect the statistics previously.
- UPDATE PROFILE
- Specifies that RUNSTATS updates an existing statistics profile in the SYSIBM.SYSTABLES_PROFILES catalog table with the options specified in the current RUNSTATS control statement. No statistics are collected when you specify this option in the RUNSTATS control statement. If the column or COLGROUP specification already exists in the profile, the new specification replaces the existing one.
- Specifies columns on which column statistics are to be gathered.
You can specify this option only if you specify a particular table on which statistics are to be gathered. (Use the TABLE (table-name) option to specify a particular table.) If you specify particular tables and do not specify the COLUMN option, RUNSTATS uses the default, COLUMN(ALL). If you do not specify a particular table with the TABLE option, you cannot specify the COLUMN option; however, in this case, COLUMN(ALL) is assumed.
- Specifies that statistics are to be gathered on all columns in the table.
The COLUMN (ALL) option is not allowed for LOB table spaces.
- (column-name, …)
- Specifies the columns on which statistics are to be gathered. You can specify a list of column names. If you specify more than one column, separate each name with a comma.
The more columns that you specify, the longer the job takes to complete.
- COLGROUP (column-name, …)
- Indicates that the specified set of columns are to be treated as a
group. This option enables RUNSTATS to collect a cardinality value on the specified column group.
RUNSTATS TABLESPACE ignores COLGROUP when processing XML table spaces and indexes.
When you specify the COLGROUP keyword, RUNSTATS collects correlation statistics for the specified column group. If you want RUNSTATS to also collect distribution statistics, specify the FREQVAL option with COLGROUP.
(column-name, …) specifies the names of the columns that are part of the column group.
When you define a column group on a single column, you can potentially improve RUNSTATS performance by specifying the STATCLGMEMSRT option or changing the value of the STATCLGSRT subsystem parameter. You can use these options to avoid the external data sort that is used for column group processing.
To specify more than one column group, repeat the COLGROUP option.Restrictions:
- The length of a COLGROUP value cannot exceed the maximum length of the COLVALUE column in the SYSIBM.SYSCOLDIST catalog table.
- A RUNSTATS control statement can contain a maximum of 255 COLGROUP specifications.
- Indicates, when specified with the COLGROUP option, that frequency
statistics are also to be gathered for the specified group of columns. (COLGROUP indicates that
cardinality statistics are to be gathered.) One group of statistics is gathered for each column. RUNSTATS
TABLESPACE ignores FREQVAL MOST, FREQVAL LEAST, or FREQVAL BOTH when it processes XML table spaces
and XML NODEID indexes.
- COUNT integer
- Specifies how
many frequently occurring values are collected from the specified column group.
You must specify a value for integer. If the COUNT keyword is not
specified, the RUNSTATS utility automatically determines the value and collects the most frequently
It is best to specify a COUNT value that is not greater than the value of COLCARDF minus one, for the column group. For most situations, 10 is usually a reasonable value. Greater COUNT values might be needed to detect skewed data, especially in high cardinality cases. However, avoid values greater than 100 in most cases. Specifying a value of 1000 or more can increase the prepare time for some SQL statements. Additionally, specifying a very large COUNT value will use a large amount of storage, which can cause storage constraints during utility execution on partitioned objects with hundreds of partitions.
- Indicates that
the utility collects the most frequently occurring values for the specified set of columns when
COLGROUP is specified. For example,
FREQVAL COUNT 10 MOSTmeans that the 10 most frequently occurring values are collected.
- Indicates that the utility collects the most and the least frequently occurring values for the specified set of columns when COLGROUP is specified. If COUNT is n, the utility collects the n least frequently occurring values and the n most frequently occurring values.
- Indicates that the utility collects the least frequently occurring values for the specified set of columns when COLGROUP is specified.
when specified with the COLGROUP (see colgroup-stats-spec) option of
RUNSTATS TABLESPACE, that histogram statistics are to be gathered for the specified group of
columns. RUNSTATS TABLESPACE ignores
HISTOGRAM when processing XML table spaces and XML NODEID indexes.
- NUMQUANTILES integer
Indicates the number of quantiles that the utility collects. The integer value must be greater than or equal to one. The number of quantiles that you specify should never exceed the total number of distinct values in the column or the column group. The maximum number of quantiles that is allowed is 100.
When the NUMQUANTILES keyword is omitted, NUMQUANTILES takes a default value of 100. Based on the number of records in the table, the number of quantiles is readjusted down to an optimal number.
indexes on which statistics are to be gathered. RUNSTATS gathers column statistics for the first
column of the index, and depending on the options that you specify, possibly additional index
columns. All the indexes must be associated with the same table space, which must be the table space
that is specified in the TABLESPACE option.
INDEX can be used on auxiliary tables to gather statistics on an index.
- Specifies that column statistics are to be gathered for all indexes that are defined on tables that are contained in the table space.
- Specifies that statistics are to be gathered for all indexes that are defined on the specified table.
- (index-name, …)
- Specifies the indexes for which statistics are to be gathered. You can specify a list of index names. If you specify more than one index, separate each name with a comma. Enclose the index name in quotation marks if the name contains a blank.
- PART integer
- Identifies an
index partition on which statistics are to be collected.
integer is the number of the partition.
KEYCARD option is deprecated in the RUNSTATS TABLESPACE control statement and no longer needs to be
specified to collect statistics on the values in the key columns of an index if INDEX is specified.
The RUNSTATS utility automatically collects all of the distinct values in all of the 1 to n intermediate key column combinations for the specified indexes, where n is the number of columns in the index. For example, suppose that you have an index defined on three columns: A, B, and C. RUNSTATS collects cardinality statistics for column A, column set A and B, and column set A, B, and C. With the deprecation of KEYCARD, this functionality cannot be disabled.
The RUNSTATS utility tolerates the specification of the KEYCARD option. The utility does not issue any messages if the control statement includes or excludes the KEYCARD option when INDEX is specified.
- Controls, when specified with the INDEX option, the collection of
frequent-value statistics. If you specify FREQVAL with INDEX, this keyword must be followed by the
- NUMCOLS integer
- Indicates the
number of columns in the index for which RUNSTATS collects frequently occurring values.
integer can be a number between 1 and the number of indexed columns. If
you specify a number greater than the number of indexed columns, RUNSTATS uses the number of columns
For example, suppose that you have an index defined on three columns: A, B, and C. If you specify NUMCOLS 1, Db2 collects frequently occurring values for column A. If you specify NUMCOLS 2, Db2 collects frequently occurring values for the column set A and B. If you specify NUMCOLS 3, Db2 collect frequently occurring values for the column set A, B, and C.
The default value is 1, which means that RUNSTATS collects frequently occurring values on the first key column of the index.
If the target is an XML index, histogram and frequency statistics are collected for the first key column of the XML index only, regardless of the NUMCOLS value.
- COUNT integer
- Indicates the
number of frequently occurring values that are to be collected from the specified key columns. For
example, specifying 15 means that RUNSTATS is to collect 15 frequently occurring values from the
specified key columns.
If the COUNT keyword is not specified, the RUNSTATS utility automatically determines the value and collects the most frequently occurring values.
when specified with the INDEX option (correlation-stats-spec) for RUNSTATS TABLE SPACE, that
histogram statistics are to be gathered for the specified key columns. Histogram statistics can be
collected only on the prefix columns with the same order. Key columns for histogram statistics with
a mixed order are not allowed.
When RUNSTATS collects histogram statistics for partition table spaces, it aggregates them into SYSCOLDIST.
- NUMQUANTILES integer
Indicates how many quantiles that the utility is to collect. The integer value must be greater than or equal to one. The number of quantiles that you specify should never exceed the total number of distinct values in the key columns specified. The maximum number of quantiles that is allowed is 100.
When the NUMQUANTILES keyword is omitted, NUMQUANTILES takes a default value of 100. Based on the number of keys in the index, the number of quantiles is readjusted down to an optimal number.
- STATCLGMEMSRT integer
- Specifies the amount of memory that the utility can use for
sorting records when collecting statistics on a single column that is defined with the COLGROUP
option. Use STATCLGMEMSRT to avoid column group sorts by an external sort program, which can
negatively affect the performance of RUNSTATS.
integer specifies the number of megabytes of memory space that the utility can use for an in-memory sort. Valid values are 0 to 4096. If the amount of space that is needed for the sort exceeds the integer value, the utility invokes a sort program. If you specify 0, the utility automatically invokes a sort program.
The amount of space that is needed for the column group sort depends on the following factors:
- The number of column groups for which RUNSTATS is collecting statistics
- The length of the single-column column group
- The number of distinct values in the column (cardinality)
The value of STATCLGMEMSRT overrides the value of the STATCLGSRT subsystem parameter.Related information:
- Indicates whether other programs that access the table space while
RUNSTATS is running must use read-only access or can change the table space.
- Allows other programs to change the table space or index. With SHRLEVEL CHANGE, RUNSTATS might collect statistics on uncommitted data.
- Allows only read-only access by other programs.
- Specifies whether, when SHRLEVEL CHANGE behavior is
in effect, pages that are read by the RUNSTATS utility in a data sharing environment are registered
with the coupling facility.
- Indicates that pages that are read by the RUNSTATS utility are not registered with the coupling
facility. This option reduces data sharing overhead. However, because REGISTER NO is valid only with
SHRLEVEL CHANGE, and SHRLEVEL CHANGE implies ISOLATION UR, when you set REGISTER NO, RUNSTATS might
collect statistics on uncommitted data.
If the INDEX keyword is specified, Db2 ignores the REGISTER NO keyword for the index portion of the utility processing.
- Indicates that pages that are read by the RUNSTATS utility are registered with the coupling facility.
- Specifies whether RUNSTATS is to generate a set of messages that
report the collected statistics.
- Indicates that RUNSTATS is not to generate the set of messages.
- Indicates that the set of messages is to be sent as output to SYSPRINT. The messages that RUNSTATS generates depend on the combination of keywords in the utility control statement. However, these messages do not depend on the value of the UPDATE option. REPORT YES always generates a report of space and access path statistics.
- Indicates which collected statistics are to be inserted into the
- Indicates that all collected statistics are to be updated in the catalog.
- Indicates that Db2 is to update the catalog with only those statistics that are used for access path selection.
- Indicates that Db2 is to update the catalog with only space-related statistics.
- Indicates that no catalog tables are to be updated with the collected statistics. When you specify UPDATE NONE REPORT NO, RUNSTATS invalidates statements in the dynamic statement cache without collecting statistics, updating catalogs tables, or generating reports.
- Indicates whether the dynamic statement cache are invalidated.
The default value is NO, with the following exceptions:
- Statements in the dynamic cache are invalidated for the objects that are specified in the job statement.
- Statements in the dynamic cache are not invalidated for the objects specified in the job statement.
- For RUNSTATS LIST REPORT NO UPDATE NONE, the default value is YES, and INVALIDATECACHE NO is not supported.
- If the RESET ACCESSPATH keyword is specified, the default value is YES, and INVALIDATECACHE NO is not supported.
which statistics are to be recorded in the catalog history tables. The value that you specify for
HISTORY does not depend on the value that you specify for UPDATE.
The default is the value of the STATISTICS HISTORY subsystem parameter on the DSNTIPO installation panel. By default, this parameter value is NONE.
- Indicates that all collected statistics are to be updated in the catalog history tables.
- Indicates that Db2 is to update the catalog history tables with only those statistics that are used for access path selection.
- Indicates that Db2 is to update the catalog history tables with only space-related statistics.
- Indicates that no catalog history tables are to be updated with the collected statistics.
- Specifies the device type that the sort program uses to
dynamically allocate the sort work data sets that are required.
- Specifies any disk device type that is acceptable for the DYNALLOC parameter of the SORT or OPTIONS option of the external sort program. Tape devices are not supported by the sort program.
If all of the following conditions are true, SORTDEVT defaults to SYSALLDA and the temporary data sets are dynamically allocated:
- You omit SORTDEVT.
- A sort is required.
- You did not provide the DD statements that the sort program requires for the temporary data sets
If you specify SORTDEVT and omit SORTNUM, no value is passed to the sort program; the sort program uses its own default.
- Specifies the number of required sort work data sets that
the sort program is to allocate.
integer is the number of temporary data sets that can range from 2 to 255.
You need at least two sort work data sets for each sort. The SORTNUM value applies to each sort invocation in the utility. For example, suppose RUNSTATS is being run on three indexes, SORTKEYS is specified, no constraints limit parallelism, and SORTNUM is specified as 8. In this case, a total of 24 sort work data sets are allocated for a job.
Each sort work data set uses both above the line and below the line virtual storage. Therefore, if you specify too high a value for SORTNUM, the utility might decrease the degree of parallelism due to virtual storage constraints, and possibly decrease the degree down to one, which means that no parallelism is used.Important: The SORTNUM keyword is ignored if the IGNSORTN subsystem parameter is set to YES.
- Specifies whether aggregation or roll up of statistics is to occur
even if statistics were not gathered on some partitions. This option enables the optimizer to select
the best access path.
- Indicates that forced aggregation or rollup processing is to be done, even though some partitions might not contain data.
- Indicates that aggregation or rollup is to be done only if data is available for all partitions.
- RESET ACCESSPATH
- Resets access path statistics for all tables in the specified
table space and related indexes. Real-time statistics and space statistics in the catalog for the
target objects are not reset. For a complete list of the statistics that are reset or deleted when
you specify this option, see: Resetting access path statistics.Important: You cannot recover previous values after the RUNSTATS utility is invoked with the RESET ACCESSPATH option, unless a statistics history is maintained. Specifying the HISTORY_ACCESSPATH option only records when the access path statistics were reset, and does not provide a method for recovering the previous values. For more information about how to maintain a statistics history, see Collecting statistics history.
Statements that refer to the objects for which statistics are reset are invalidated in the dynamic statement cache.
This option cannot be specified for LOB table spaces.
When this RESET ACCESSPATH is used, other keywords that specify the specific statistics to be collected within the table space cannot be specified.
- HISTORY ACCESSPATH
- Inserts rows into the following tables for each object for which
the access path statistics are reset when the RESET ACCESSPATH option is specified:
- SYSIBM.SYSTABLES_HIST for tables.
- SYSIBM.SYSINDEXES_HIST for indexes.