15.10 - AutomateUsedStats - Teradata Database

Teradata Database Application Programming Reference

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

Copies the definitions of those statistics identified by a prior Analyzer-related operation as having been used during query optimization but not yet automated to the TDSTATS database.

REPLACE PROCEDURE TDSTATS.AutomateUsedStats(
 IN  DatabaseName        VARCHAR(128) CHARACTER SET UNICODE,
 IN  TableName           VARCHAR(128) CHARACTER SET UNICODE,
 IN  ObjectListName      VARCHAR(128) CHARACTER SET UNICODE,
 IN  AnalysisId          INTEGER,
 IN  ExcludeTempTables   CHAR(1) CHARACTER SET LATIN,
 IN  MarkApproved        CHAR(1) CHARACTER SET LATIN,
 OUT AutomateId          BIGINT,
 OUT NumCopied           INTEGER)
           .
           .
           .
;
 

Parameter

Description

DatabaseName

Name of the database. DatabaseName limits the operation to statistics defined on 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.

AnalysisID

Result ID from a prior call to AnalyzeStatsUsage. For more information, see “AnalyzeStatsUsage” on page 518.

Note: This input parameter cannot be NULL.

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.
  • The AutomateUsedStats external stored procedure identifies actively used statistics that must be automated and copies them.

    The summarized results from this operation are written to the AutomateHistoryTbl table. To display these results, you can call AutomateReport.

    Unless you specify a value of N in the ExcludeTempTables input parameter, any statistics defined on global temporary tables are automatically excluded under the assumption that their management must be highly customized within the user application that populates them.

    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.

     

    Parameter

    Description.

    AutomateId

    System supplied ID for the history results from this operation.

    NumCopied

    Number of statistics copied to the TDSTATS database.

    This example shows how to perform the following operations:

  • Analyze the statistics usage in the Personnel database for queries logged over a seven-day period.
  • CALL TDSTATS.AnalyzeStatsUsage ('2012-12-01 00:00:00.00','2012-12-31 00:00:00.00', NULL, 'Personnel', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Y', AnalysisId, NumEvents);
     
    *** Procedure has been executed. 
    *** Total elapsed time was 10 seconds.
     
      AnalysisId   NumEvents
    ------------- -------------
          2         6
  • Identify the results ID (for example, 2) from the call to AnalyzeStatsUsage as having been used during query optimization, if not already done.
  • CALL TDSTATS.AutomateUsedStats ('Personnel', NULL, NULL, 2, NULL, 'Y', AutomateId, NumCopied);
     
    *** Procedure has been executed. 
    *** Total elapsed time was 10 seconds.
     
      AutomateId   NumCopied
    ------------- -------------
          3        2
     

    For more information on ...

    See ..

    displaying the AutomateUsedStats results

    “AutomateReport” on page 501.

    creating or adding object list entries

  • “CreateObjectList” on page 603.
  • “AddObjectListEntry” on page 604.