In this article, we will see TERADATA SPACE CONCEPTS AND SPACE UTILITIES
There are three types of spaces available in Teradata.
Permanent space is the maximum amount of space available for the user/database to hold data rows. Permanent tables, journals, fallback tables and secondary index sub-tables use permanent space.
Permanent space is not pre-allocated for the database/user. They are just defined as the maximum amount of space the database/user can use. The amount of permanent space is divided by the number of AMPs. Whenever per AMP limit exceeds, an error message is generated.
Spool space is the unused permanent space which is used by the system to keep the intermediate results of the SQL query. Users without spool space cannot execute any query.
Similar to Permanent space, spool space defines the maximum amount of space the user can use. Spool space is divided by the number of AMPs. Whenever per AMP limit exceeds, the user will get a spool space error.
Temp space is the unused permanent space which is used by Global Temporary tables. Temp space is also divided by the number of AMPs.
Teradata supports two space utilities inorder to correct the space if any space inconsistency happens due to any exception.
The Update Space utility, updatespace, recalculates the permanent, temporary, or spool space used by either of the following:
A single database and its individual tables
All databases in a system and their individual tables
Update Space accomplishes this by performing the following:
Examining storage descriptors and adding up space for each table.
Setting values in CurrentPermSpace, CurrentTempSpace, or CurrentSpoolSpace in the DATABASESPACE table for each table and for the containing database as a whole.
UpdateDBC: maximum allowed values for permanent, temporary, and spool space.
Update Space: current usage for permanent, temporary, and spool space.
Update Space presents a command-line environment that allows the entry of the UPDATE command, which has the following syntax.
TEMPORARY : to update only the temporary space.
ALL : to update all the space
SPOOL : to update only the spool space.
PSPOOL : to update only the spool space that persists across restarts.
SPACE FOR : specifies the name of the database(s) for which space is to be recalculated.
ALL DATABASES : all the databases in a system
dbname : the name of a single database
ALL PROXYUSERS : to update the specified space for all Trusted Sessions proxy users.
update all space for all databases;
The Update DBC utility, updatedbc, performs the following:
For table dbc.dbase:
PermSpace, SpoolSpace, and TempSpace values for user DBC.
The PermSpace value in DBASE for user DBC is the total available storage space minus the PermSpace for all other databases.
The SpoolSpace and TempSpace values in DBASE for user DBC are the total available storage space.
For databases other than DBC, the PermSpace, SpoolSpace, and TempSpace values in the DBASE table are the maximums declared when the database is defined.
The DBASE table includes the following columns:
For table dbc.databasespace:
MaxPermSpace, MaxSpoolSpace, and MaxTempSpace values for each database in the system based on the PermSpace, SpoolSpace, and TempSpace values in the DBASE table for that database.
The DATABASESPACE table includes the following columns:
Use Update DBC only to correct inconsistency in the DBASE or DATABASESPACE tables, which might occur as the result of rare types of system failures.
In order to run Update DBC, the system should be quiescent (no users should be logged on). To see the system state, type pdestate -a at the Linux command line.
Do no use the CNS/Database Window STOP command to abort Update DBC when it is running.