TruncateAutomateHistory Stored Procedure | Teradata Vantage - TruncateAutomateHistory - Advanced SQL Engine

TruncateAutomateHistory Stored Procedure | Teradata Vantage - TruncateAutomateHistory - Advanced SQL Engine - Teradata Database

Application Programming Reference

Product

Advanced SQL Engine

Teradata Database

Release Number

17.10

Published

July 2021

Language

English (United States)

Last Update

2021-07-27

dita:mapPath

hvk1593628831140.ditamap

dita:ditavalPath

hvk1593628831140.ditaval

dita:id

B035-1090

lifecycle

Product Category

Teradata Vantage™

Truncates rows from the TDSTATS.AutomateHistoryTbl table.

Syntax

REPLACE PROCEDURE TDSTATS.TruncateAutomateHistory (
  IN OlderThan      TIMESTAMP(6) WITH TIME ZONE,
  IN DatabaseName   VARCHAR(128) CHARACTER SET UNICODE,
  IN TableName      VARCHAR(128) CHARACTER SET UNICODE,
  IN ObjectListName VARCHAR(128) CHARACTER SET UNICODE,
  IN AutomateId     BIGINT,
  OUT DeletedRows   INTEGER
)
  ...
;

Syntax Elements

OlderThan: Time. OlderThan deletes the automate history data older than the time you specify.; If you specify NULL, time is not used as a qualifier for deletion.
DatabaseName: Name of the database. DatabaseName limits the deletion to rows associated with the database you specify.
TableName: Name of the table within the specified DatabaseName. TableName limits the deletion to rows associated to the table you specify within DatabaseName.
ObjectListName: Name of the object list. ObjectListNamedeletes the rows associated with the objects (that is, one or more database names or fully qualified table names where each name entry may optionally contain wildcard characters) in the list you specify.; For more information about this input parameter, see the usage notes or CreateObjectList.
AutomateId: Automate results ID. AutomateId deletes results associated with the Automate results ID you specify.
DeletedRows: Number of deleted rows.

Usage Notes

You must periodically run TruncateAutomateHistory to prevent the TDSTATS.AutomateHistoryTbl table from consuming an excessive amount of space.

Specifying the Results to Remove

If you specify NULL for one of the following input parameters, all rows will be truncated.

To remove results ...	You must specify a value for ...
from a particular Automate-related call or results that older than the specified time	AutomateId or OlderThan.
on a particular object	both DatabaseName and TableName.
for a particular named object list	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.

Example: Using TruncateAutomateHistory

The following example shows how to remove all Automate-related results that are older than one year.

CALL TDSTATS.TruncateAutomateHistory (CAST(CURRENT_DATE - INTERVAL '1' YEAR AS TIMESTAMP(6)), NULL, NULL, NULL,NULL, DeletedRows);
*** Procedure has been executed.
 *** Total elapsed time was 1 second.
DeletedRows
-----------
        300