TERADATA SPACE CONCEPTS AND SPACE UTILITIES

Share via:

Dear Readers,

In this article, we will see TERADATA SPACE CONCEPTS AND SPACE UTILITIES

There are three types of spaces available in Teradata.

Permanent Space

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

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

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.

Updatespace :

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.

Eg :

update all space for all databases;

UpdateDBC:

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:

  1. PermSpace
  2. SpoolSpace
  3. TempSpace

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:

  • CurrentPermSpace
  • CurrentSpoolSpace
  • CurrentTempSpace
  • MaxPermSpace
  • MaxSpoolSpace
  • MaxTempSpace

 

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.

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Add Comment