Usage Notes - Advanced SQL Engine - Teradata Database

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.

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.