Delete Old Log Data Manually| Teradata Vantage - Deleting Old Log Data Manually from System Tables - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantage™

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.
      CREATE TABLE DBC.tablename AS databasename.tablename

      For further information on using the Copy Table syntax, see “CREATE TABLE” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

    2. Back up the table 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 ;
    object_name
    The name of the DBC object that allows log deletions, as shown in 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.