ActivateStat Stored Procedure | Application Programming Reference | Vantage - ActivateStat - Advanced SQL Engine - Teradata Database

Application Programming Reference
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
cpn1571792172880.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1090
lifecycle
previous
Product Category
Teradata Vantage™

Reactivates a previously inactivated statistic to reflect observed query usage and makes it eligible again for future collections via the PrepCollect and RunCollect external stored procedures.

Definition

REPLACE PROCEDURE TDSTATS.ActivateStat
 (IN SCOID BIGINT,
 IN  DatabaseName    VARCHAR(128) CHARACTER SET UNICODE,
 IN  ObjectName      VARCHAR(128) CHARACTER SET UNICODE,
  IN  ObjectListName  VARCHAR(128) CHARACTER SET UNICODE,
  IN  MarkPermanent   CHAR(1) CHARACTER SET LATIN,
 OUT NumInActivated  INTEGER)
           .
           .
           .
;

Input Parameters

Parameter Description
SCOID TDSTATS database ID of an individual statistic.
DatabaseName Name of the database. DatabaseName reactivates all statistics defined on the database you specify.
ObjectName Name of the table within the specified DatabaseName. ObjectName limits the reactivation to the statistics defined on the table you specify within DatabaseName.
ObjectListName Name of the object list. ObjectListName allows you to specify a previously defined list of objects (that is, one or more database names or fully qualified table names where each name entry may optionally contain wildcard characters) whose statistics should be reactivated.

For more information about this input parameter, see “Usage Notes” or CreateObjectList.
MarkPermanent Possible values:
  • Y. If you specify Y, the statistic is permanently marked active such that future calls to the CleanupStats external stored procedure are not recommended for inactivation.
  • N or NULL. If you specify N or NULL, the statistic remains a candidate for inactivation by future calls to the CleanupStats external stored procedure. The default value is N.

Usage Notes

You can use the ActivateStat external stored procedure to mark a still active or inactive statistic as permanently active so that the statistic is removed from future consideration by CleanupStats. For more information about this external stored procedure, see CleanupStats.

Reactivating Statistics

If you do not specify a value for the following input parameters, the operation is performed on all user created databases in the system.
To reactivate ... You must specify a value for ...
a single statistic SCOID.
When specifying a value for SCOID, you do not need to specify a value for the DatabaseName or TableName.
all statistics for a particular database DatabaseName.
all statistics for a particular table both DatabaseName and TableName.
a list of objects that cannot be specified by DatabaseName or TableName

ObjectListName that was previously created by the CreateObjectList and AddObjectListEntry external stored procedures.

ObjectListName usage is mutually exclusive with DatabaseName and TableName . When you specify a value other than NULL for ObjectListName , DatabaseName and TableName must be NULL.

Output Parameter

Parameter Description
NumActivated Number of statistics marked active that were previously inactive or not permanently active.

Example: Using ActivateStat

The following example shows how to reactivate a previously inactivated statistic on the Personnel.Employee table whose TDSTATS database ID is 101.

CALL TDSTATS.ActivateStat(101,'Personnel','Employee', NULL, NULL, NumActivated);
 *** Procedure has been executed.
 *** Total elapsed time was 1 second.
NumActivated
------------
           1