Teradata : TERADATA BASIC TERMINOLOGIES AND SPACE ALLOCATION

Share via:

TERADATA BASIC TERMINOLOGIES AND SPACE ALLOCATION

 

 

Below are few of the key terminologies (other than the ones explained in previous articles) that need to be remembered before going forward in Teradata:

Node: Teradata System is made up of many servers known as Nodes. Each node consists of its own CPU, memory, OS, Teradata software and disk space.

VPROCs: VPROCs are virtual processors that are part of Teradata’s architecture for carrying out specific tasks. These are not physical but only logical, hence the name. Examples of vprocs are AMPs, PEs etc.

TPA (Trusted Parallel Applications): Any node which has Teradata installed on it is called a TPA node.

HSN (Hot Standby Node): A node which takes over once any TPA node goes down.

Fallback: A second copy of a table for fault tolerance.

Transient Journal: A transient journal is used to store the before images of a tables on which a DML statement is running. A before image is needed in case the request fails and rollback needs to be done. Once the request is completed successfully, transient journal is purged.

Other terminologies to know are PE, AMP, Bynet which we have already covered in previous articles.

SPACE ALLOCATION:

In Teradata, space allocation is the amount of disk space that is permitted for a specific user or database. The allocation is an upper limit of usage. There are three types of space in Teradata

  1. Perm Space (Permanent Space)
  2. Spool Space
  3. Temporary Space

Permanent (Perm) Space: Perm space is the amount of space allocated to a particular user or database to store database objects. This space is used to store the actual table data, indexes,

 

 

fallback data etc. When Teradata is installed, all the perm space is taken by the system master user called DBC. The perm space is divided equally among all the AMPs as Teradata tries to keep the data in each amp to be as uniform as possible for faster retrievals.
For example, from the above figure if DBC has 1 TB allocation and has 5 AMPs in the system then each AMP has 0.2 TB space allocated to them.

 

When a new user / database is created, its space is given from DBC and can go into different levels of hierarchy. The child database can have zero space allocation as views don’t take up space and can still exist in a database with zero perm space allocated. If the child database is dropped, its space is automatically given back to the parent.

Spool Space:

Spool Space is the spaced allocated to store the intermediate and final result sets of a user’s query. Each user that needs to run query on Teradata needs to have spool space allocated. If a session running on Teradata has its spool space exceed the limit that is defined for the user, the request gets aborted and an error message is thrown to the user. Generally spool space issues occur because of operating over large data sets and dealing with skewed data using bad primary index which we will look into the future articles. If any amp goes out of spool space, the error is thrown irrespective of other amps still being able to execute the request. Spool Space is allocated from the unused perm space on a Teradata system. Once the perm space is completely filled, there will be no space available to be used as spool space. Spool Space can be allocated from any available space on the system and not just the parent database. Once the request has been completed and the session is logged off, all the spool space gets cleared.

Temporary Space:

Temp space is space allocated to be used up by Global Temporary Tables. Once the session is logged off, all the data in GTTs are purged. Similar to spool space, temp space comes from the unused perm space on a Teradata system.

 

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

3 thoughts on “Teradata : TERADATA BASIC TERMINOLOGIES AND SPACE ALLOCATION

Add Comment