16.10 - Clearing Out Values in the DBC.Acctg Table - 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)

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 ARC with an all-AMPs dump of database DBC. (For a list of the tables that are archived, see Teradata Archive/Recovery Utility Reference.)
    If you archived data using a tool other than ARC, such as FastExport or BTEQ, then ARC cannot reload it. 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.