15.10 - AutomateStats - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

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

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

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” on page 603.

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.

    Note: The default value is N.

    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 SQL Functions, Operators, Expressions, and Predicates.

    Automating Statistics

    Notice:

    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.

    Note: 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.

     

    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.

    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
     

    For more information on ...

    See ..

    creating or adding object list entries

  • “CreateObjectList” on page 603.
  • “AddObjectListEntry” on page 604.
  • displaying the AutomateStats results

    “AutomateReport” on page 501.

    BTEQ

    Basic Teradata Query Reference.