16.20 - InActivateStat - Teradata Vantage NewSQL Engine

Teradata Vantage™ Application Programming Reference

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1090-162K

Marks an automated statistic inactive because of no observed query usage and disables it from future collections by the PrepCollect and RunCollect external stored procedures.

Definition

REPLACE PROCEDURE TDSTATS.InActivateStat
 (IN SCOID BIGINT,
 IN DatabaseName   VARCHAR(128) CHARACTER SET UNICODE,
 IN TableName     VARCHAR(128) CHARACTER SET UNICODE,
  IN ObjectListName VARCHAR(128) CHARACTER SET UNICODE,
 OUT NumInActivated INTEGER)
           .
           .
           .
;

Input Parameters

Parameter Description
SCOID TDSTATS database ID for an individual statistic.
DatabaseName Name of the database. DatabaseName inactivates all statistics defined on the database you specify.
TableName Name of the table within the specified DatabaseName. TableName limits the operation 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 activated.

For more information about this input parameter, see “Usage Notes” or CreateObjectList.

Usage Notes

Depending on how the CleanupStats external stored procedure is called, any identified inactive statistics may remain active until the InActivateStat external stored procedure is called on them. For more information about this external stored procedure, see CleanupStatsReport.

Inactivating 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 inactivate ... 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
NumInActivated Number of statistics marked inactive that were previously active.

Example: Using InActivateStat

The following example shows how to inactivate an automated statistic on the Personnel.Employee table whose TDSTATS database ID is 101.

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