16.10 - Deleting Old Log Data Manually from System Tables - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

Before deleting old log data, determine whether you need to retain records of the data set for deletion. For example, while Teradata recommends that you purge log data at least every 90 to 180 days, security policy for some sites may require retention of access log data for a much longer time period.

You cannot delete data that is less than 30 days old.

Use the following procedure when deleting old log data from system tables:

  1. If your site requires long term retention of log data, do the following:
    1. Create a duplicate log table in another database using the Copy Table syntax for the CREATE TABLE statement.
      CT DBC.tablename AS databasename.tablename

      For further information on using the Copy Table syntax, see “Create Table” in SQL Data Definition Language Syntax and Examples.

    2. Back up the table to tape storage in accordance with your site back up policy.
      Tip: Match the log backup/deletion schedule to the amount of log data you want to maintain locally in system tables. For example, to maintain 90 days of log data in a system table do the first back up at 120 days, then delete the last 30 days of data in the log. Back up the log data every 30 days after that.
    3. Drop the duplicate table using a DROP TABLE statement.
  2. Log on to SQL Administrator as DBADMIN or another administrative user with DELETE privileges on database DBC.
  3. In the Query window, enter an SQL statement to purge old log entries.

    For example:

    DELETE FROM DBC.object_name  WHERE (Date  - LogDate) >  number_of_days  ;

    where:

    Syntax Element Description
    object_name The name of the DBC object that allows log deletions, as shown in About Database Logs. For example, DeleteAccessLogV is the delete object for the Access Log.
    Date SQL syntax element for the current date.
    LogDate The date contained in the LogDate column of the log view based on the specified number_of_days from the current date.
    number_of_days The number of days of data that you want to retain in the system table; the span of days from the current date to the oldest LOGDATE to be retained.