Teradata Space Management

Teradata is designed in such a fashion, to reduce the DBA’s administrative functions when it comes to space management. Space is configured in the following ways in Teradata system –

1)      PERMANENT SPACE

2)      SPOOL SPACE

3)      TEMPORARY SPACE

1) PERMANENT SPACE – Permanent space is where the objects (i.e. – databases, users, tables) are created and stored. PERM space is distributed evenly across all the AMPs. Equal distribution is necessary, because then there is a high percentage that the objects will be shared across all the AMPs, and at the time of data retrieval all AMPs will work parallel to fetch the data.

Unlike other relational databases the Teradata database does not physically defined the PERM space at the time of object creation, instead of that it defines the upper limit for the PERM space and then PERM space is used dynamically by the objects.

E.g. if a database is defined as the 500 GB PERM space and actual size of database is 300 GB only, then the remaining 200 GB will be used as SPOOL space, there is no need of holding the 200 GB when it is not required by the database. But when database required more space then this 200 GB will be released from the SPOOL space and given back to database. This mechanism ensures enough memory to execute all processes in the Teradata system.

2) SPOOL SPACE – Spool space is the amount of space on the system that has not been allocated. The primary reason for the SPOOL space is to store intermediate results or queries that are being processed in Teradata. For example, when executing conditional query all the qualifying rows which satisfies the given condition will be store in the SPOOL space for further processing by the query. Any PERM space currently unassigned is available as a SPOOL space.

Defining a SPOOL space limit is not required when Users and Databases are created. But it is highly recommended to define the upper limit of SPOOL space for any object (i.e. users, database, tables) which you create. Because in case there is no upper limit define for SPOOL space for the object then the processing query for that object might consume all the space in the system and cause “runaway transaction”.

one of the difference between the PERM space and the SPOOL space is that –

In PERM space if we create a CHILD database from the PARENT database then the amount of PERM space for that CHILD database is subtracted from the PARENT PERM space.

For example a database SYSDBA is allotted 500 GB of PERM space. Now if we create another CHILD database, say HR, from SYSDBA , and allot 200 GB of PERM space to HR database, then this 200 GB will be subtracted from the PARENT database SYSDBA. Similarly if we define another CHILD database SALARY from HR and allot 100 GB PERM space to it, then this 100 GB will be deducted from HR database.

While the SPOOL space limit for a CHILD database is not subtracted from its immediate PARENT, but the CHILD database SPOOL space is as large as its immediate PARENT.

In spool space allocation the CHILD database HR and SALARY has the same amount of SPOOL space as there PARENT database SYSDBA has.

To define PERM space and SPOOL space on a database we required below mentioned query –

CREATAE DATABASE teradatatech AS PERM = 10000000, SPOOL =20000000

3) TEMP SPACE – The amount of space used for Global Temporary Tables is known as TEMP space. These results remain available to the user until the session is terminated. Tables created in TEMP space will survive a restart. Permanent space not being used for tables is available for TEMP space.

If you are planning to appear for Teradata Certification Exams then have a look on the sample question set for first exam of TD Certification i.e. TD BASICS (TEO-121) >>