15.10 - UpdateStatThresholdSetting - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

Records the user specified THRESHOLD settings to include when preparing and executing SQL COLLECT STATISTICS statements on selected statistics or objects.

REPLACE PROCEDURE TDSTATS.UpdateStatThresholdSetting
 (IN  SCOID            BIGINT,
  IN  DatabaseName     VARCHAR(128) CHARACTER SET UNICODE,
  IN  TableName        VARCHAR(128) CHARACTER SET UNICODE,
  IN  ThresholdType    CHAR(1) CHARACTER SET LATIN,
  IN  AgeThreshold     INTEGER,
  IN  GrowthThreshold  INTEGER,
  IN  ForCurrentlyOnly CHAR(1) CHARACTER SET LATIN,
  OUT NumUpdated       INTEGER)
           .
           .
           .
;
 

Parameter

Description

SCOID

TDSTATS ID of an individual statistic whose THRESHOLD settings are to be changed.

Note: This input parameter cannot be NULL.

DatabaseName

Required. DatabaseName limits the new settings that will be applied to all statistics collected within the database you specify.

Note: This input parameter cannot be NULL.

TableName

Name of the table within the specified DatabaseName. TableName limits the new settings to statistics collected on the table you specify within DatabaseName.

ThresholdType

Type of threshold option change:

  • S means SYSTEM THRESHOLD
  • U means THRESHOLD <AgeThreshold> DAYS AND THRESHOLD <GrowthThreshold> PERCENT
  • N means no threshold.
  • Note: When specifying the U option, you must also specify a value other than NULL for the AgeThreshold or GrowthThreshold input parameter, or both.

    For more information about the COLLECT STATISTICS THRESHOLD option, see SQL Data Definition Language or Database Administration.

    AgeThreshold

    User supplied age threshold in number of days.

    This input parameter is valid only when SampleType is U. For more information about the SampleType input parameter, see “UpdateStatSampleSetting” on page 579.

    Note: A NULL or zero value indicates a no age threshold.

    GrowthThreshold

    User supplied growth threshold as a percentage.

    This input parameter is valid only when SampleType is U. For more information about the SampleType input parameter, see “UpdateStatSampleSetting” on page 579.

    Note: A NULL or zero value indicates no growth threshold.

    ForCurrentlyOnly

    Possible values:

  • Y. If you specify Y, the THRESHOLD setting will be issued along with the FOR CURRENT option making it applicable to the current collection only.
  • N or NULL. If you specify N or NULL, the THRESHOLD setting is applied to all future collections.
  • For more information about the THRESHOLD FOR CURRENT option, see SQL Data Definition Language.

    The new collection settings will take effect during the next RunCollect operation involving the selected statistics. For more information, see “RunCollect” on page 550.

    Updating Statistics

     

    To update ...

    You must specify a value for ...

    a single statistics collection

    SCOID.

    Note: This input parameter cannot be NULL. When specifying a value for SCOID, you do not need to specify a value for DatabaseName or TableName.

    all statistics for a particular database

    DatabaseName.

    Note: This input parameter cannot be NULL.

    all statistics for a particular table

    both DatabaseName and TableName.

     

    Parameter

    Description

    NumUpdated

    Number of statistics whose THRESHOLD settings have been updated.

    For more information about the THRESHOLD option, see SQL COLLECT STATISTICS in SQL Data Definition Language or Database Administration.

    The following example shows how to specify a ThresholdType option (for example, THRESHOLD 10 PERCENT AND THRESHOLD 7 DAYS) when collecting statistics on the individual statistic whose TDSTATS database ID is 103.

    CALL TDSTATS.UpdateStatThresholdSetting(103, 'Personnel', 'Employee', 'U', 7, 10, NULL, NumUpdated);
     
    *** Procedure has been executed. 
     *** Total elapsed time was 1 second.
     
     NumUpdated
    -----------
              1