15.10 - DeAutomateStats - Teradata Database

Teradata Database Application Programming Reference

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

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

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

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

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” on page 523 and “InActivateStat” on page 540.

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.
  • Notice:

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

    De-automating Statistics

     

    To de-automate ...

    You must specify a value ...

    a single statistics collection

    for SCOID.

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

    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.

    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.

     

    Parameter

    Description

    DeAutomateId

    Results ID for all events generated by this operation.

    NumRemoved

    Number of statistic definitions removed.

    The following example shows how to de-automate all statistic definitions from the Personnel database.

    Note: 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);
     

    For more information on the ...

    See ...

    SQL DROP STATISTICS statement

    SQL Data Definition Language.

    DeAutomateStats results

    “AutomateReport” on page 501.