17.00 - Usage Notes - Teradata Database

Teradata Vantageā„¢ - Data Dictionary

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Administration
Programming Reference
featnum
B035-1092-170K

When a database or user is created, allocated disk space is divided evenly among all AMPs. The DiskSpace view returns one row of usage information for each AMP in the system (or for all AMPs if the SUM aggregate is used).

When a database is created, a space row is added on each AMP, with the processor field in each row initialized to 0. The first time the space row is updated, such as when a table is created in the database or the system is restarted, the processor field in each row is updated to reflect the actual processor number.

You can use the DiskSpaceV[X] view to build and maintain a table of disk space usage statistics for each username or accountname.

To create the history table, enter the following statement:

CREATE TABLE DiskSpaceHist (DataBaseName VARCHAR(128) CHARACTER SET UNICODE,
   AccountName VARCHAR(128) CHARACTER SET UNICODE,
   MaxPerm FLOAT,
   MaxSpool FLOAT,
   CurrentPerm FLOAT,
   PeakPerm FLOAT,
   PeakSpool FLOAT,
   CollectDate DATE,
   CollectTime FLOAT )
PRIMARY INDEX (DataBaseName, AccountName);

You can periodically collect usage statistics using the following procedure:

  1. Select statistics from the DiskSpaceV[X] view and insert them in the history table.
  2. Reset DiskSpace counters to zero for the next collection period.
You can reset the maximum and peak DiskSpace counters to zero using the ClearPeakDisk macro, which is provided on the release tape.

This procedure can be done using the following BTEQ script:

.LOGON username, password
    
INSERT INTO DiskSpaceHist
   SELECT DataBaseName, AccountName,
   SUM(MaxPerm),
   SUM(MaxSpool),
   SUM(CurrentPerm),
   SUM(PeakPerm),
   SUM(PeakSpool),
   DATE, TIME
   FROM DBC.DiskSpaceV
   GROUP BY DataBaseName, AccountName, DATE, TIME;
    
   EXECUTE DBC.ClearPeakDisk;
    
   .QUIT

Corresponding Tables

The X view references these additional tables:

  • DBC.AccessRights
  • DBC.Owners
  • DBC.RoleGrants
  • DBC.Roles

AppProxyUser and TrustUserName

These columns are not returned in the X or VX views.

PeakPerm, PeakSpool, PeakPersistentSpool, and PeakTemp

You can use the DBC.ClearPeakDisk macro to reset the PeakPerm, PeakSpool, PeakPersistentSpool, and PeakTemp.