About Purging the System Logs - Advanced SQL Engine - Teradata Database

Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
upb1600054424724.ditamap
dita:ditavalPath
upb1600054424724.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantage™
The system does not automatically purge logs or the tables underlying views such as the AccessLogV, LogOnOffV, and Software_Event_LogV views. Teradata recommends that you use either the Teradata Viewpoint Performance Data Collection portlet or copy data off the DBC tables into a separate database, archive the data (as desired), and then delete information from the DBC tables. For information about the Viewpoint Performance Data Collection portlet, see Setting Up Automated PDCR Log Maintenance. Some DBC objects to consider purging include:
  • Resource usage (ResUsage) tables that have logging enabled.
  • DBQL logs, which include all DBQL tables that have logging enabled.

    (The tables DBC.DBQLRuleTbl and DBC.DBQLRuleCountTbl are not part of the log maintenance list. These tables are automatically maintained by the Teradata SQL BEGIN/END QUERY LOGGING statements; an error is returned if you attempt to delete their contents.)

  • DBC.SW_Event_Log
  • QCD.DataDemographics. (If you use Query Capture Facility [QCF] with the SQL COLLECT DEMOGRAPHICS statement, you need to explicitly delete rows from this table, DataDemographics, in your user-defined QCD databases.)
    Entries in DataDemographics are deleted automatically when you use the INSERT EXPLAIN WITH STATISTICS AND DEMOGRAPHICS statement. For more information, see “Query Capture Facility” in Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 and “COLLECT DEMOGRAPHICS” in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
    The Teradata Viewpoint Performance Data Collection portlet does not archive this table.
Also, the security administrator should purge the logs associated with access logging as well as any other security-related views as needed. In addition to maintaining the size of system tables and logs, you can reduce log file sizes as follows:
  • Use only DBC.ResUsageSpma instead of activating logging on multiple ResUsage tables. ResUsageSpma may be able to provide all the information you need.
  • Use roles to manage privileges which help reduce the size of DBC.AccessRights. For other tips on reducing the size of the DBC.AccessRights table, see Housekeeping on an Ad-Hoc Basis.
  • Track only the information you need for DBQL. The DBQLogTbl may be able to provide all the information you need.
  • Track only the information you need for accounting.
  • Use active row filter mode where possible for ResUsage tables.

For example, if you want to keep the DBC.Acctg table from growing too large, use only the ASE variables that report the level of information you need. Using &T or &I for ASE accounting will potentially make the DBC.Acctg table very large; but using &D or &L usually has less impact on disk space and may still provide enough level of detail. For more information, see Enabling Account String Expansion.