DeAutomateStats Stored Procedure | Application Programming Reference| Vantage - DeAutomateStats - Advanced SQL Engine

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

Definition

REPLACE PROCEDURE TDSTATS.DeAutomateStats(
 IN DatabaseName  VARCHAR(128) CHARACTER SET UNICODE,
 IN TableName   VARCHAR(128) CHARACTER SET UNICODE,
 IN ObjectListName VARCHAR(128) CHARACTER SET UNICODE,
 IN SCOID     BIGINT,
 IN InActiveSince TIMESTAMP(6) WITH TIME ZONE,
 IN OrphansOnly  CHAR(1) CHARACTER SET LATIN,
 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);

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

Application Programming Reference