DeAutomateStats Stored Procedure | Application Programming Reference| Vantage - 17.05 - DeAutomateStats - Teradata Database

Teradata Vantage™ - Application Programming Reference

Advanced SQL Engine
Teradata Database
June 2020
Programming Reference

Reverses prior automate operations by removing specified statistics definitions from the TDSTATS database.


 OUT DeAutomateId   BIGINT,
 OUT NumRemoved   INTEGER)

Input Parameters

Parameter Description
DatabaseName Name of the database from which all statistics should be de-automated.

This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see “Usage Notes.”

TableName Name of the table within the specified DatabaseName. TableName limits the operation to the table you specify within DatabaseName.

This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see “Usage Notes.”

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 de-automated.

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

SCOID An individual statistic. SCOID limits the operation to an individual statistic as identified by its value as recorded in the TDSTATS.StatsTbl.SCOID column.
InActiveSince Time. InActiveSince limits the operation to those qualifying statistics that have been marked inactive by the CleanupStats or InActivateStat external stored procedure. For information about these external stored procedures, see CleanupStats and InActivateStat.
OrphansOnly Possible values:
  • Y. If you specify Y, the operation is limited to only those qualifying collections whose definitions no longer exist in the dictionary.
  • N or NULL. If you specify N or NULL, all qualifying collections regardless of their existence in the dictionary are removed. The default value is N.

Usage Notes

If you specify all input parameters as NULL, all previously automated statistics for all objects in the system will be de-automated.

De-automated statistics, unless already orphaned, are not dropped from the dictionary and will remain accessible to query optimization. You must issue the SQL DROP STATISTICS statement to de-automate these statistics from the dictionary.

To display the DeAutomateStats results, you can call AutomateReport.

Wildcard Characters

The following table describes the wildcard characters that the names of some databases, tables, and objects contain.
Character Description
% (PERCENT SIGN) Represents any string of zero or more arbitrary characters. Any string of characters is acceptable as a replacement for the percent.
_ (LOW LINE) Represents exactly one arbitrary character. Any single character is acceptable in the position in which the underscore character appears.

For more information on how to use these wildcard characters, see the SQL LIKE clause in Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

De-automating Statistics

To de-automate ... You must specify a value ...
a single statistics collection for 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 for DatabaseName.
all statistics for a particular table for both DatabaseName and TableName.
a list of objects that cannot be specified by DatabaseName or TableName for 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.

only those qualifying statistics that have no recent usage in query optimization for InactiveSince.
only those qualifying statistics whose definitions no longer exist in the dictionary of Y for OrphansOnly.

Output Parameters

Parameter Description
DeAutomateId Results ID for all events generated by this operation.
NumRemoved Number of statistic definitions removed.

Example: Using DeAutomateStats

The following example shows how to de-automate all statistic definitions from the Personnel database.

The input argument value that begins with a colon represents a host variable whose value was populated in a prior call where it served as an output argument.

CALL TDSTATS.DeAutomateStats ('Personnel',NULL,NULL,NULL,NULL,'N',DeAutomateId,NumRemoved);
CALL TDSTATS.AutomateReport(:DeAutomateId);

Related Topics

For more information on the ... See ...
SQL DROP STATISTICS statement Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
DeAutomateStats results AutomateReport.