Records the user-specified THRESHOLD settings to include when preparing and executing SQL COLLECT STATISTICS statements on selected statistics or objects.
Syntax
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 ) ... ;
Syntax Elements
- SCOID
- TDSTATS ID of an individual statistic whose THRESHOLD settings are to be changed.
- DatabaseName
- DatabaseName limits the new settings that will be applied to all statistics collected within the database you specify.
- 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.
- AgeThreshold
- User-supplied age threshold in number of days.
- GrowthThreshold
- User-supplied growth threshold as a percentage.
- 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.
- NumUpdated
- Number of statistics whose THRESHOLD settings have been updated.
Usage Notes
The new collection settings will take effect during the next RunCollect operation involving the selected statistics. For more information, see RunCollect.
Updating Statistics
To update ... | You must specify a value for ... |
---|---|
a single statistics collection | SCOID. 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. This input parameter cannot be NULL.
|
all statistics for a particular table | both DatabaseName and TableName. |
Example: Using UpdateStatThresholdSetting
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