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:
|
MarkApproved | Possible values:
|
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.
- 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
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
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. |