17.00 - 17.05 - Clearing Out Values Manually in the DBC.Acctg Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
You can either use the following manual procedure to clear out values in the DBC.Acctg table or use an automated method, such as the Teradata Viewpoint Performance Data Collection portlet. See Setting Up Automated PDCR Log Maintenance.

The DBC.Acctg table logs CPU utilization and I/O statistics for each account a user owns on each AMP.

Updates to the table are made periodically during each AMP step on each processor affected by the step. (If there are long-running steps, AMPUsage numbers show large increases periodically, instead of continuous incremental additions.) Data is collected and added to what is already in the table until you reset the counters to zero.

If you use account string expansion, ASE codes can cause the table to grow even more quickly. You may need to update DBC.Acctg on a regular basis to clear out values and reset the accumulators (For details, see Enabling Account String Expansion). However, use caution when purging rows containing accounts with ASE codes.

Purge obsolete rows from DBC.Acctg carefully when there is an ASE string embedded in the account string by constructing the appropriate WHERE clause. Since ASE is embedded in the AccountName text string instead of a separate field, careful SQL coding is required.

Use the following process to first save the data, then clear out DBC.Acctg and reset the counters.

  1. To control growth, archive the entire predefined set of system tables using DSA with an all-AMPs dump of database DBC.
    If you cannot use the tool that created the archive to reload it, try the BTEQ IMPORT command.
  2. After the accumulated AMP data is successfully summarized, and archived if necessary, run the ClearAccounting macro to reset the values of the CPU and I/O columns:
    EXEC DBC.ClearAccounting;
    If you use ASE but are not sure which rows to purge from the DBC.Acctg:
    1. Create a table similar to DBC.Acctg with a non-unique primary index, date, and time columns.
    2. Populate the table with rows that are created for each respective account by capturing the sum total of the relevant accounting fields at regular intervals.
    3. Run queries against the table. Entries that show no change are candidate rows to purge.