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

Teradata Vantage™ - Application Programming Reference

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1090-170K

Copies statistics definitions for a specified database or table from the dictionary to the TDSTATS database.

Definition

REPLACE PROCEDURE TDSTATS.AutomateStats(
 IN  DatabaseName      VARCHAR(128) CHARACTER SET UNICODE,
 IN  TableName         VARCHAR(128) CHARACTER SET UNICODE,
 IN  ObjectListName    VARCHAR(128) CHARACTER SET UNICODE,
 IN  NewerThan         TIMESTAMP(6) WITH TIME ZONE,
 IN  ExcludeTempTables CHAR(1) CHARACTER SET LATIN,
 IN  MarkApproved      CHAR(1) CHARACTER SET LATIN,
 IN  DeleteOrphans     CHAR(1) CHARACTER SET LATIN,
 OUT AutomateId        BIGINT,
 OUT NumCopied         INTEGER,
 OUT NumRemoved        INTEGER)
           .
           .
           .
;

Input Parameters

Parameter Description
DatabaseName Name of the database. DatabaseName limits the operation to statistics defined in the database you specify.

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

TableName Name of the table. TableName limits the operation to statistics defined on the table you specify.

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

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

NewerThan Time. NewerThan limits the operation to statistics that were initially collected after the specified time as recorded in the DBC.StatsTbl.CreateTimeStamp column.
ExcludeTempTables Possible values:
  • Y or NULL. If you specify Y or NULL, the statistics defined on global temporary tables of the specified databases are excluded from automation. The default value is Y.
  • N. If you specify N, the statistics defined on global temporary tables of the specified databases are automated.
MarkApproved Possible values:
  • Y or NULL. If you specify Y or NULL, the copied statistic is marked approved and is included in subsequent calls to PrepCollect and RunCollect. The default value is Y.
  • N. If you specify N, the copied statistic is marked unapproved. To approve the statistic, you can call ApproveStat.
DeleteOrphans Previously automated statistics that no longer exist in the dictionary. Possibly values are Y or N.

If you specify Y, these statistics are removed.

The default value is N.

Usage Notes

By default, the AutomateStats external stored procedure operates on statistics defined within all user databases defined in the system.

To automate never before collected statistics as defined by a list of SQL COLLECT STATISTICS statements residing in a text file, do the following:
  • Submit the statistics to the database using BTEQ or another similar client tool.
  • Call AutomateStats.

AutomateStats results are written to the TDSTATS.AutomateHistoryTbl table. To display the AutomateStats 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.

Automating 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 automate ... You must specify a value for ...
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 Parameters

Parameter Description
AutomateId System supplied ID for the history results from this operation.
NumCopied Copies or repairs statistics definitions for a specified database or table from the dictionary to the TDSTATS database. Repairs are limited to those objects that have been marked for preservation after dropping and recreating; see related API PreserveAfterRecreate.
NumRemoved Number of statistics removed from the TDSTATS database.

Example: Using AutomateStats

The following example shows how to copy all statistics definitions for the Personnel database from the DBC dictionary to the TDSTATS database.

CALL TDSTATS.AutomateStats ('Personnel',NULL,NULL,NULL,'Y','Y','N',AutomateId,NumCopied,NumRemoved);
*** Procedure has been executed.
*** Total elapsed time was 1 second.
 AutomateId   NumCopied   NumRemoved
-----------   ----------- -----------
          1        5           0

Related Topics

For more information on ... See ..
creating or adding object list entries
displaying the AutomateStats results AutomateReport.
BTEQ Basic Teradata® Query Reference, B035-2414.