15.10 - DiskSpaceV[X] - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

Accounting

DBC

 

View Column

Data Type

Format

Source Table.Column

Vproc

SMALLINT

NOT NULL

-(5)9

DatabaseSpace.Vproc

DatabaseName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseName

ConnectRulesTbl.ProxyUser

AccountName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.AccountName

MaxPerm

FLOAT

NOT NULL

-,---,---,---,---,---,---,--9

DataBaseSpace.MaxPermSpace

MaxSpool

FLOAT

NOT NULL

-,---,---,---,---,---,---,--9

DataBaseSpace.MaxSpoolSpace

MaxTemp

FLOAT

NOT NULL

-,---,---,---,---,---,---,--9

DataBaseSpace.MaxTempSpace

CurrentPerm

FLOAT

NOT NULL

-,---,---,---,---,---,---,--9

DataBaseSpace.CurrentPermSpace

CurrentSpool

FLOAT

NOT NULL

-,---,---,---,---,---,---,--9

DataBaseSpace.CurrentSpoolSpace

CurrentPersistent
Spool

FLOAT

NOT NULL

-,---,---,---,---,---,---,--9

DataBaseSpace.CurrentPersistentSpool

CurrentTemp

FLOAT

NOT NULL

-,---,---,---,---,---,---,--9

DataBaseSpace.CurrentTempSpace

PeakPerm

FLOAT

NOT NULL

-,---,---,---,---,---,---,--9

DataBaseSpace.PeakPermSpace

PeakSpool

FLOAT

NOT NULL

-,---,---,---,---,---,---,--9

DataBaseSpace.PeakSpoolSpace

PeakPersistentSpool

FLOAT

NOT NULL

-,---,---,---,---,---,---,--9

DataBaseSpace.PeakPersistentSpool

PeakTemp

FLOAT

NOT NULL

-,---,---,---,---,---,---,--9

DataBaseSpace.PeakTempSpace

MaxProfileSpool

FLOAT

-,---,---,---,---,---,---,--9

Profiles.SpoolSpace

MaxProfileTemp

FLOAT

-,---,---,---,---,---,---,--9

Profiles.TempSpace

TrustUserName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseName

AppProxyUser

VARCHAR(1)

X(1)

None

The X view references these additional tables:

  • DBC.AccessRights
  • DBC.Owners
  • DBC.RoleGrants
  • DBC.Roles
  • 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);

    Periodically, you can 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.

    Note: 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 carried out 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

    AccountName

    The AccountName column is the unexpanded default account name for the user or database. A database can only have one account. A user may have a default account plus other accounts. If there is a profile for the user, and it specifies a default account, that account superseded this account name.

    CurrentPerm

    The CurrentPerm column value includes all AMPs unless a specific AMP is requested.

    CurrentPersistentSpool

    This column returns the persistent spool space (in bytes) currently used by the database on a specified AMP or on all AMPs if the SUM aggregate is specified.

    PeakPerm, PeakSpool, PeakPersistentSpool, and PeakTemp

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

    PeakPersistentSpool

    This column returns the integer that represents the maximum persistent spool space (in bytes) that was used at any one time by the database on a specified AMP or on all AMPs if the SUM aggregate is specified.

    TrustUserName and AppProxyUser

    TrustUserName returns the trusted user connected as the application proxy user. AppProxyUser set to ‘Y’ indicates the space is for an application proxy user.

    The following SELECT statement displays the permanent disk space across all AMPs:

    ==> SELECT AMP,DatabaseName,CurrentPerm,MaxPerm FROM DBC.DiskSpace;

    Result:

    AMP  DatabaseName     ­CurrentPerm        ­MaxPerm
    ---  ------------     -----------       ---------
     .       .                     .           .
     .       .                     .           .
    0-0      stst14                0        125,000
    0-0      ud12                  0        125,000
    1-0      atest             1,536        125,000
    1-0      a1                    0        247,500
    1-0      btest             3,584          5,000
    1-0      b2test           49,664        250,000
     .       .                     .           .
     .       .                     .           .
    1-1      atest             1,536        125,000
    1-1      a1                    0        247,500
    1-1      btest             3,584          5,000
    1-1      b2test           50,688        250,000
     .       .                     .           .
     .       .                     .           .
    1-2      atest             1,536        125,000