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

Read syntax diagramSkip visual syntax diagram RUNSTATS TABLESPACE listdef-spectable-space-specstatistics-specreset-spec

listdef-spec

Read syntax diagramSkip visual syntax diagram LIST listdef-name INVALIDATECACHE NOINVALIDATECACHE YES

table-space-spec

Read syntax diagramSkip visual syntax diagram database-name. table-space-name PARTintegerFORCEROLLUPNOFORCEROLLUPYESINVALIDATECACHE NOINVALIDATECACHE YES

statistics-spec

Read syntax diagramSkip visual syntax diagramTABLE1(ALL)all-tables-spec,TABLE1table-namenamed-tables-specINDEX2(ALL)correlation-stats-specINDEX(*3)correlation-stats-specINDEX(,index-namePARTintegercorrelation-stats-spec)STATCLGMEMSRTintegerSHRLEVELCHANGE REGISTERYESREGISTERNO4SHRLEVELREFERENCEREPORTNOREPORTYESUPDATEALLUPDATEACCESSPATHSPACENONEhistory-specSORTDEVTdevice-type
Notes:
  • 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.

all-tables-spec

Read syntax diagramSkip visual syntax diagramsample-specuse-profile-specDELETE PROFILE

named-tables-spec

Read syntax diagramSkip visual syntax diagram sample-spec12column-speccolgroup-specuse-profile-spec3column-speccolgroup-specset-profile-spec3DELETE PROFILE3
Notes:
  • 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.

sample-spec

Read syntax diagramSkip visual syntax diagramSAMPLE25integerTABLESAMPLE SYSTEMAUTOnumeric-literalNONE1REPEATABLE-integer
Notes:
  • 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.

use-profile-spec

Read syntax diagramSkip visual syntax diagram USE PROFILE12 INCLUDENPI(NPI)
Notes:
  • 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.

column-spec:

Read syntax diagramSkip visual syntax diagramCOLUMN(ALL)COLUMN(,column-name1)SORTNUMinteger
Notes:
  • 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.

colgroup-spec:

Read syntax diagramSkip visual syntax diagramCOLGROUP(,column-name)colgroup-stats-spec

colgroup-stats-spec:

Read syntax diagramSkip visual syntax diagramFREQVAL COUNTinteger1MOSTBOTHLEASTHISTOGRAMNUMQUANTILES100NUMQUANTILESinteger
Notes:
  • 1 When the COUNT keyword is not specified, the utility automatically determines the count value and collects the most frequently occurring values.

correlation-stats-spec:

Read syntax diagramSkip visual syntax diagramKEYCARD1FREQVAL NUMCOLS1COUNT10MOSTFREQVAL NUMCOLSintegerCOUNTinteger2MOSTBOTHLEASTHISTOGRAMNUMCOLS1NUMQUANTILES100NUMCOLSinteger3NUMQUANTILES100NUMQUANTILESinteger
Read syntax diagramSkip visual syntax diagramKEYCARD1FREQVAL NUMCOLS1COUNT10MOSTFREQVAL NUMCOLSintegerCOUNTinteger2MOSTBOTHLEASTHISTOGRAMNUMCOLS1NUMQUANTILES100NUMCOLSinteger3NUMQUANTILES100NUMQUANTILESinteger
Notes:
  • 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.

set-profile-spec

Read syntax diagramSkip visual syntax diagramSETPROFILEFROM EXISTING STATS1UPDATEPROFILE
Notes:
  • 1 The FROM EXISTING STATS clause is not valid if a column-spec, colgroup-spec, or INDEX keyword has been specified

history-spec

Read syntax diagramSkip visual syntax diagramHISTORYNONE1HISTORYALLACCESSPATHSPACE
Notes:
  • 1 You can change the default HISTORY value by modifying the STATISTICS HISTORY subsystem parameter. By default, this value is NONE.
reset-spec
Read syntax diagramSkip visual syntax diagramRESET ACCESSPATHHISTORY ACCESSPATH

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.

database-name
Identifies the name of the database to which the table space belongs.

The default value is DSNDB04.

table-space-name
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.

TABLE
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.

(ALL)
Specifies that column statistics are to be gathered on all columns of all tables in the table space. The parentheses around ALL are optional.

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.

(table-name)
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.

Start of changeIf 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.End of change

USE PROFILE
Specifies 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 option. Start of changeIf the STATFDBK_PROFILE subsystem parameter is set to YES, statistics profiles are updated automatically for recommendations that are generated during query optimization.End of change

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.

Start of changeWhen you specify USE PROFILE, the profile options are included in SYSPRINT in message DSNU1376I.End of change

Start of changeFL 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. End of change

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 table.

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.

Start of changeIf 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. End of change

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.

numeric-literal
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.

AUTO
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 report.

When AUTO is specified, and real-time statistics are not available, RUNSTATS sets the sampling rate to 100.

Start of changeNONEEnd of change
Start of changeSpecifies 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.End of change
REPEATABLE integer
Start of changeAdding 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.End of change
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.
COLUMN
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.
(ALL)
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.

Start of changeWhen 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.End of change

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.
FREQVAL
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. Start of changeRUNSTATS TABLESPACE ignores FREQVAL MOST, FREQVAL LEAST, or FREQVAL BOTH when it processes XML table spaces and XML NODEID indexes.End of change
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 occurring values.

Start of changeIt 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. End of change

MOST
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 MOST means that the 10 most frequently occurring values are collected.
BOTH
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.
LEAST
Indicates that the utility collects the least frequently occurring values for the specified set of columns when COLGROUP is specified.
HISTOGRAM
Indicates, 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. Start of changeRUNSTATS TABLESPACE ignores HISTOGRAM when processing XML table spaces and XML NODEID indexes.End of change
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.

INDEX
Specifies 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.

(ALL)
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
The 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.

FREQVAL
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 keyword.
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 in the index.

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.

Start of changeIf 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.End of change

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.

Start of changeIf the COUNT keyword is not specified, the RUNSTATS utility automatically determines the value and collects the most frequently occurring values.End of change

HISTOGRAM
Indicates, 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.

Start of changeSTATCLGMEMSRT integerEnd of change
Start of changeSpecifies 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.

End of change
SHRLEVEL
Indicates whether other programs that access the table space while RUNSTATS is running must use read-only access or can change the table space.
CHANGE
Allows other programs to change the table space or index. With SHRLEVEL CHANGE, RUNSTATS might collect statistics on uncommitted data.
REFERENCE
Allows only read-only access by other programs.
Start of changeREGISTEREnd of change
Start of changeSpecifies 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.
NO
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.

Start of change If the INDEX keyword is specified, Db2 ignores the REGISTER NO keyword for the index portion of the utility processing.End of change

YES
Indicates that pages that are read by the RUNSTATS utility are registered with the coupling facility.
End of change
REPORT
Specifies whether RUNSTATS is to generate a set of messages that report the collected statistics.
NO
Indicates that RUNSTATS is not to generate the set of messages.
YES
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.
UPDATE
Indicates which collected statistics are to be inserted into the catalog tables.
ALL
Indicates that all collected statistics are to be updated in the catalog.
ACCESSPATH
Indicates that Db2 is to update the catalog with only those statistics that are used for access path selection.
SPACE
Indicates that Db2 is to update the catalog with only space-related statistics.
NONE
Indicates that no catalog tables are to be updated with the collected statistics. Start of changeWhen you specify UPDATE NONE REPORT NO, RUNSTATS invalidates statements in the dynamic statement cache without collecting statistics, updating catalogs tables, or generating reports.End of change
Start of change INVALIDATECACHE End of change
Start of changeIndicates whether the dynamic statement cache are invalidated.
YES
Statements in the dynamic cache are invalidated for the objects that are specified in the job statement.
NO
Statements in the dynamic cache are not invalidated for the objects specified in the job statement.
The default value is NO, with the following exceptions:
  • 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.
End of change
HISTORY
Indicates 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.

ALL
Indicates that all collected statistics are to be updated in the catalog history tables.
ACCESSPATH
Indicates that Db2 is to update the catalog history tables with only those statistics that are used for access path selection.
SPACE
Indicates that Db2 is to update the catalog history tables with only space-related statistics.
NONE
Indicates that no catalog history tables are to be updated with the collected statistics.
SORTDEVT
Specifies the device type that the sort program uses to dynamically allocate the sort work data sets that are required.
device-type
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.

SORTNUM
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: Start of changeThe SORTNUM keyword is ignored if the IGNSORTN subsystem parameter is set to YES.End of change
FORCEROLLUP
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.
YES
Indicates that forced aggregation or rollup processing is to be done, even though some partitions might not contain data.
NO
Indicates that aggregation or rollup is to be done only if data is available for all partitions.
If the value for STATISTICS ROLLUP on panel DSNTIPO is NO and data is not available for all partitions, Db2 issues message DSNU623I.
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.