TruncateCollectHistory Stored Procedure | Teradata Vantage - 17.05 - TruncateCollectHistory - Teradata Database

Teradata Vantage™ - Application Programming Reference

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1090-170K

Truncates rows from the TDSTATS.CommandsHistoryTbl table representing execution results from collection commands.

Definition

REPLACE PROCEDURE TDSTATS.TruncateCollectHistory
  (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 RunID          BIGINT,
   IN CmdListID      BIGINT,
   IN CmdListName    VARCHAR(128) CHARACTER SET UNICODE,
   OUT DeletedRows   INTEGER)
           .
           .
           .
;

Input Parameters

Parameter Description
OlderThan Time. OlderThan deletes collection history data older than the time you specify.
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 with the table you specify within DatabaseName.
ObjectListName Name of the object list. ObjectListName deletes rows associated with 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 “Usage Notes” or CreateObjectList.

RunId RunCollect results ID. RunId deletes history rows associated with the RunCollect results ID you specify.
CmdListID Commands list ID. CmdListId deletes history rows associated with the commands list ID you specify.
CmdListName Name of the commands list. CmdListName deletes history rows associated with the commands list you specify.

Usage Notes

Before using TruncateCollectHistory, you should consider the potential impact of losing historical execution data.

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

Removing Results

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

To remove results ... You must specify a value for ...
from a particular collection run operation or results that older than the specified time RunID 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.

Output Parameter

Parameter Description
DeletedRows Number of deleted history rows.

Example: Using TruncateCollectHistory

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

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