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

Teradata Vantage™ - Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1090-171K
Language
English (United States)

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

Syntax

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)
  ...
;

Syntax Elements

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 the usage notes.
TableName
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.
This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see the usage notes.
ObjectListName
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 the 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.
DeAutomateId
Results ID for all events generated by this operation.
NumRemoved
Number of statistic definitions removed.

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.

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 Information

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