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

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.

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.