15.10 - ActivateStat - Teradata Database

Teradata Database Application Programming Reference

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

Reactivates a previously inactivated statistic to reflect observed query usage and makes it eligible again for future collections via the PrepCollect and RunCollect external stored procedures.

REPLACE PROCEDURE TDSTATS.ActivateStat
 (IN SCOID BIGINT,
 IN  DatabaseName    VARCHAR(128) CHARACTER SET UNICODE,
 IN  ObjectName      VARCHAR(128) CHARACTER SET UNICODE,
  IN  ObjectListName  VARCHAR(128) CHARACTER SET UNICODE,
  IN  MarkPermanent   CHAR(1) CHARACTER SET LATIN,
 OUT NumInActivated  INTEGER)
           .
           .
           .
;
 

Parameter

Description

SCOID

TDSTATS database ID of an individual statistic.

DatabaseName

Name of the database. DatabaseName reactivates all statistics defined on the database you specify.

ObjectName

Name of the table within the specified DatabaseName. ObjectName limits the reactivation to the statistics defined on the table you specify within DatabaseName.

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

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

MarkPermanent

Possible values:

  • Y. If you specify Y, the statistic is permanently marked active such that future calls to the CleanupStats external stored procedure are not recommended for inactivation.
  • N or NULL. If you specify N or NULL, the statistic remains a candidate for inactivation by future calls to the CleanupStats external stored procedure. The default value is N.
  • You can use the ActivateStat external stored procedure to mark a still active or inactive statistic as permanently active so that the statistic is removed from future consideration by CleanupStats. For more information about this external stored procedure, see “CleanupStats” on page 523.

    Reactivating 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 reactivate ...

    You must specify a value for ...

    a single statistic

    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

    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

    NumActivated

    Number of statistics marked active that were previously inactive or not permanently active.

    The following example shows how to reactivate a previously inactivated statistic on the Personnel.Employee table whose TDSTATS database ID is 101.

    CALL TDSTATS.ActivateStat(101,'Personnel','Employee', NULL, NULL, NumActivated);
     
     *** Procedure has been executed.
     *** Total elapsed time was 1 second.
     
    NumActivated
    ------------
               1