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 RUSNTATS 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
>>-RUNSTATS--TABLESPACE-----------------------------------------> >--+-LIST--listdef-name-----------------------------------------------------------+--> '-+----------------+-table-space-name--+-------------------------------------+-' '-database-name.-' | .-FORCEROLLUP--NO--. | '-PART--integer--+------------------+-' '-FORCEROLLUP--YES-' >--+--------------------------------------------------+---------> | (1) .-(ALL)-. | +-TABLE------+-------+--| all-tables-spec |--------+ | .-,--------------------------------------------. | | V (1) | | '---TABLE------table-name--| named-tables-spec |-+-' >--+-------------------------------------------------------------+--> | (2) | '-+-INDEX------(--ALL--)--| correlation-stats-spec |--------+-' | (3) | +-INDEX--(--*------)--| correlation-stats-spec |----------+ | .-,--------------------------------------. | | V | | '-INDEX--(----index-name--| correlation-stats-spec |-+--)-' .-SHRLEVEL--CHANGE ---. .-REPORT--NO--. >--+---------------------+--+-------------+---------------------> '-SHRLEVEL--REFERENCE-' '-REPORT--YES-' .-UPDATE--ALL----------------. >--+----------------------------+----| history-spec |-----------> '---UPDATE--+-ACCESSPATH-+---' +-SPACE------+ '-NONE-------' >--+-----------------------+----------------------------------->< '-SORTDEVT--device-type-'
- The TABLE keyword is not valid for a LOB table space.
- You cannot specify INDEX if either USE PROFILE or DELETE PROFILE option is also specified.
- 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.
all-tables-spec
>>---+-| sample-spec |--+----------------------+-+------------->< | '-| use-profile-spec |-' | '-DELETE PROFILE----------------------------'
named-tables-spec
(1) (2) >>-+---| sample-spec |----------+-| column-spec |--+-------------------+-+---+->< | | '-| colgroup-spec |-' | | | | (3) | | | '---| use-profile-spec |-----------------' | +-| column-spec |--+-------------------+--+--------------------------+----+ | '-| colgroup-spec |-' | (3) | | | '-| set-profile-spec |-----' | | (3) | '-DELETE PROFILE----------------------------------------------------------'
- 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.
- 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.
- 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
>>-+-----------------------------------------------------------------+->< | .-25------. | +-SAMPLE--+---------+---------------------------------------------+ | '-integer-' | | .-AUTO------------. | '-TABLESAMPLE SYSTEM--+-numeric-literal-+--+--------------------+-' '-REPEATABLE-integer-'
use-profile-spec
>>-USE PROFILE--+--------------------+------------------------->< '-INCLUDE--+-(NPI)-+-' '-NPI---'
column-spec:
.-COLUMN--(--ALL--)-----------------. >>-+-----------------------------------+------------------------> | .-,---------------. | | V (1) | | '-COLUMN--(----column-name-----+--)-' >--+------------------+---------------------------------------->< '-SORTNUM--integer-'
- 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 will not replace it with the COLUMN(ALL) option. Users have to state explicitly if they want the COLUMN(ALL) option.
colgroup-spec:
.----------------------------------------------------------. | .-,-----------. | V V | | >>---COLGROUP--(----column-name-+--)--| colgroup-stats-spec |-+-><
colgroup-stats-spec:
>>-+------------------------------------+-----------------------> | .-MOST--. | '-FREQVAL -COUNT--integer--+-------+-' +-BOTH--+ '-LEAST-' >--+--------------------------------------+-------------------->< | .-NUMQUANTILES--100-----. | '-HISTOGRAM--+-----------------------+-' '-NUMQUANTILES--integer-'
correlation-stats-spec:
>>-+-------------+----------------------------------------------> | (1) | '-KEYCARD-----' .----------------------------------------------------------. V .-FREQVAL -NUMCOLS--1--COUNT--10--MOST-----------------. | >----+------------------------------------------------------+-+--> | .-MOST--. | '-FREQVAL -NUMCOLS--integer--COUNT--integer--+-------+-' +-BOTH--+ '-LEAST-' .----------------------------------------------------------------. V | >----+------------------------------------------------------------+-+->< | .-NUMCOLS--1--NUMQUANTILES--100---------------. | '-HISTOGRAM--+---------------------------------------------+-' | .-NUMQUANTILES--100-----. | '-NUMCOLS--integer--+-----------------------+-' '-NUMQUANTILES--integer-'
- The KEYCARD option is deprecated. The KEYCARD functionality is now built into the normal execution of the RUNSTATS INDEX utility and cannot be disabled.
set-profile-spec
>>-+-SET--PROFILE--+-------------------------+-+--------------->< | | (1) | | | '-FROM EXISTING STATS-----' | '-UPDATE--PROFILE---------------------------'
- The FROM EXISTING STATS clause is not valid if a column-spec, colgroup-spec, or INDEX keyword has been specified
history-spec
(1).-HISTORY--NONE-----------. >>-----+-------------------------+----------------------------->< '-HISTORY--+-ALL--------+-' +-ACCESSPATH-+ '-SPACE------'
- 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.
- 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.
- USE PROFILE
- Specifies a stored statistics profile that is 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.
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.
- 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 an existing RUNSTATS profile that 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. 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.Related information: - 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.
- 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 or statistical view results in changes to the table or statistical view 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.
- 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.
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. You must specify COUNT
integer with COLGROUP FREQVAL.
RUNSTATS TABLESPACE ignores FREQVAL MOST, FREQVAL LEAST, or
FREQVAL BOTH when it processes XML table spaces and indexes.
- COUNT integer
- Specifies how many frequently occurring values are collected from the specified column group.
You must specify a value for integer. No default value is
assumed.
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.
- 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. RUNSTATS TABLESPACE ignores HISTOGRAM when processing XML table spaces and
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.
- 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 and COUNT keywords.
- 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.
- 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.
The default value is 10.
- 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.
- 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.
- 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.
Running the RUNSTATS always invalidates the dynamic cache. However, when you specify UPDATE NONE REPORT NO, RUNSTATS invalidates statements in the dynamic statement cache without collecting statistics, updating catalogs tables, or generating reports.
- 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: The SORTNUM keyword is ignored if the UTSORTAL subsystem parameter is set to YES and the IGNSORTN subsystem parameter is set to YES. - 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.