16.20 - Usage Notes - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ Data Dictionary

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
zzu1512081120577.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

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 Teradata Database (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.