STATCHANGE session environment option

Use the STATCHANGE environment option to specify a positive integer as a global percentage-of-change threshold for UPDATE STATISTICS operations in automatic mode. The setting restricts automatic UPDATE STATISTICS operations to stale or missing distributions in which at least that percentage of rows have changed since statistics were updated.

You can specify an integer percentage value in the range from 1 to 100 for the STATCHANGE session environment option, which has this syntax:

STATCHANGE environment option

SET ENVIRONMENT STATCHANGE { DEFAULT | 'integer' }
Element Description Restrictions Syntax
integer An unsigned integer value in the range 1 - 100 as a percentage of rows changed in a table or fragment since the most recent recalculation Must be delimited between single (') or double (") quotation marks. The DEFAULT keyword applies the current STATCHANGE configuration parameter value. Literal Number asQuoted String

Usage

When automatic UPDATE STATISTICS operations are enabled, the database server can reset the value of the STATCHANGE session environment option as the threshold for recalculating distribution statistics in the current session when your SET ENVIRONMENT STATCHANGE statement specifies one of these values:
'integer' or "integer"
Sets a data-change threshold, as a percentage of rows modified since distribution statistics were calculated, for automatic UPDATE STATISTICS operations on tables or fragments. The integer value (where '1' 'integer' '100') has no effect, however, except when the automatic statistics mode has been enabled by one of the mechanisms identified below at one of the following scopes:
  • the UPDATE STATISTICS statement, if it includes the AUTO keyword
  • the session, if the AUTO_STAT_MODE session option is ON
  • the system default, if the AUTO_STAT_MODE configuration setting is 1
  • for the table, if its STATCHANGE table attribute is AUTO.
If the UPDATE STATISTICS statement includes the FORCE keyword, however, these STATCHANGE settings and attributes are ignored, and table statistics are recalculated, even if the most recently calculated distribution statistics for the table in the system catalog have not been changed by subsequent DML operations.
DEFAULT
Applies the current setting of the AUTO_STAT_MODE configuration parameter to the currebt session. This automatic location and fragmentation during the current session.
  • for all sessions, when the AUTO_STAT_MODE configuration parameter has enabled the automatic mode,
  • or for only the current session, when the SET ENVIRONMENT AUTO_STAT_MODE statement of SQL has enabled the automatic mode.

Automatic UPDATE STATISTICS mode

In automatic mode, the database server selectively recalculates distribution statistics that already exist in the sysdistrib or sysfragdist system catalog tables only for stale distributions. The value that you set for STATCHANGE specifies criteria for determining whether the column distribution statistics of a table or a fragment qualify for an update.

When UPDATE STATISTICS is in automatic mode, but the STATCHANGE session environment option is not set, or is set to DEFAULT, the explicit or default setting of the STATCHANGE configuration parameter can specify a positive integer as a threshold to define stale data distributions. If the STATCHANGE configuration parameter is not set, the default value is 10. The database server compares this threshold to the actual percentage of rows that have been deleted, inserted, or modified by DML operations since the distribution statistics were most recently updated. Only if the actual percentage is equal to or greater than the threshold are distributions recalculated automatically.

You can use the SET ENVIRONMENT STATCHANGE statement, however, to specify an integer value that overrides the explicit or default STATCHANGE configuration parameter setting for the current session.

Examples of SET ENVIRONMENT STATCHANGE

The following statement sets a threshold of 50 percent for the database server to use in determining if distribution statistics qualify for an update: 
SET ENVIRONMENT STATCHANGE '50';
With a STATCHANGE threshold of 50 for the session, when automatic UPDATE STATISTICS operations are enabled, the column distributions of a table or fragment are recalculated only if half of all the rows have been processed by DML operations since the current values in the sysdistrib or sysfragdist system catalog tables were calculated.

Whether the granularity of distributions to be calculated in automatic mode is the entire table or is some of its fragments depends on the current STATLEVEL setting for the table, as described in the topics Statistics options of the CREATE TABLE statement and Statistics options of the ALTER TABLE statement.

The following statement sets the threshold for the server to use to determine if distribution statistics qualify for an update to the current setting of the STATCHANGE configuration parameter: 
SET ENVIRONMENT STATCHANGE DEFAULT;

You might use the DEFAULT option to restore a STATCHANGE threshold that the SET ENVIRONMENT STATCHANGE statement had reset because the session required an unusual STATCHANGE value for automatic UPDATE STATISTICS operations on a specific table.

The next example includes successive SQL statements that can affect whether distribution statistics are recalculated for the customer table:
SET ENVIRONMENT AUTO_STAT_MODE ON;
SET ENVIRONMENT STATCHANGE '25';
UPDATE STATISTICS HIGH FOR customer FORCE;
In this example,
  • The first statement enables automatic statistics mode for the session.
  • The second statement sets the change threshold at 25% for the current session.
  • The UPDATE STATISTICS statement ignores the STATCHANGE setting for the session, and ignores the percentage of modified rows in the customer table, because the FORCE keyword requires the database server to recalculate distribution statistics for all columns in that table.
See also the topic AUTO_STAT_MODE session environment option.