Chapter 7 – Space

Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Chapter 7 – Space

“It’s kind of fun to do the impossible.”

- Walt Disney

When your System Arrives, there is only User named DBC

When Teradata first arrives, there is only one user called DBC. This DBC name comes from Teradata’s first system called the DBC 1012 back in 1988. The DBC stood for Database Computer and 10 to the 12^th power is a Terabyte, thus DBC 1012. Every system ever built by Teradata has a supreme user at the top of the hierarchy called DBC.

USER DBC

User DBC has always been at the top of the Teradata hierarchy. The employee that logs on with the DBC login is the most powerful person in the company. They can essentially do anything to the Teradata database because user DBC contains all Teradata Database software components and all system tables.

Before you define application users and databases, you should first use the CREATE USER statement to create a special administrative user to complete these tasks. Then keep the DBC password in a safe and hidden place.

Create an administrative user, and then logon as that user to protect sensitive data in DBC. In addition, change and secure the DBC password.

First Assignment is to create another User just under DBC

You can name the user anything you want. The standard is actually SYSDBA, but whom better than TeraTom to watch a Teradata system? Don’t give your new user all the space. DBC needs at least 15% of the total system space for the Transient Journal and other functions that run automatically under the DBC user.

USER DBC

•System user DBC contains all Teradata Database software components and all system tables.

•Before you define application users and databases, you should first use the CREATE USER statement to create a special administrative user to complete these tasks.

•The amount of space for the administrative user is allocated from DBC’s current PERM space. DBC becomes the owner of your administrative user and of all users and databases you subsequently create.

•Be sure to leave enough space in DBC to accommodate the growth of system tables and logs.

•You can name the user anything you would like. We have called the user SYSDBA. Create the administrative user, and then logon as that user to protect sensitive data in DBC. In addition, change and secure the DBC password.

•To ensure perm space is from the administrative user, logon as that user to add other users and databases.

Perm and Spool Space

Space has only to do with space on the data warehouse disks. Each AMP controls its own disk farm (each AMP is attached to four physical disks) and about 60% of each disk will be used for tables, and that is called PERM space. The other 40% (Spool) is work space for user queries and answer sets. No AMP can get into another AMP’s disk.

Perm Space is for Permanent Tables

PERM Space is where an AMP keeps its tables. That is what you need to understand. You will also find out that PERM space also houses Secondary Indexes, Join Indexes and Permanent Journals. Just remember that PERM is for the tables and indexes! Notice the different sizes of tables. This is because tables are different sizes.

Spool Space is work space that builds a User’s Answer Sets

Spool space is used by each AMP in order to build the answer set for the user.

Spool Space is in an AMP’s Memory and on its Disk

AMPs have memory called File System Generating Cache (FSG) used for processing.

Users are Assigned Spool Space Limits

Spool is assigned to users, and the only way you are aborted is if you go over your spool limit. Marketing isn’t restricted in total to 10 GB of spool, but it is the individual max. Just like a speed limit of 60 MPH on the highway. Each car can go 60 MPH and each User in Marketing can query at a max speed limit of 10 GB of spool.

What is the Purpose of Spool Limits?

There are two reasons for Spool Limits:

1. If a user makes a mistake and runs a query that could take weeks to run, it will abort as soon as the user goes over their allotted spool limit.

2. It keeps individual users from hogging the system.

Spool is assigned to users, and the only way a user is aborted is if they go over their spool limit. Marketing, Sales, and DBC have unlimited spool, but the max for each individual user is 10 GB in Marketing, 5 GB in Sales, and our power user is at 100 GB.

Why did my query Abort and say “Out of Spool”?

How is it possible that I ran out of spool?

You ran out of spool because your query used over your limit of 10 MBs of spool.

It is also possible that you have logged onto multiple machines or ran multiple queries, and the combination went over 10 MBs of spool.

It is also very likely that the data you were working with was NOT evenly distributed (skewed ), and this is a major cause of Spool errors.

Spool is assigned to users and the only way a user is aborted is if they go over their spool limit. No user has ever failed because they are in Marketing and Marketing has only 10 GB of spool. It doesn’t work that way. Thousands of users in Marketing could run queries simultaneously because Marketing has unlimited amounts of spool, but each user in Marketing is limited to 10 GBs individually.

How can Skewed Data cause me to run “Out of Spool”?

Each User’s Spool limit is actually done per AMP, so if you are assigned 10 GBs of spool, and the system has 10 AMPs, you are really assigned 1 GB of Spool per AMP!

If data is skewed, and you exceed your 1 GB limit on any AMP, you are “out of spool” .

Spool is assigned to every user, but since Teradata is a parallel processing system, each AMP is only concerned with them. Each AMP processes its portion of the data in parallel. Because of this philosophy your Spool Space (10 GB) is divided among the total AMPs in the system. If you have 10 GBs of Spool and there are 10 AMPs you get 1 GB per AMP. If you go over 1 GB on any AMP you are aborted and “Out of Spool”.

Why did my Join cause me to run “Out of Spool”?

What does my system look like when it first arrives?

All Teradata systems start with one USER called DBC .

The first Teradata machine ever built came out in 1988 and it was called the DBC 1012. The DBC portion stood for Database Computer. The 1012 was named because 10 to the 12^th power is equal to a Terabyte. So, the DBC 1012 was a Database Computer designed to process Terabytes of data. So, every system starts with one USER called DBC and DBC owns all the PERM Space in the system.

DBC owns all the PERM Space in the system on day one

When the system is new and arrives at your company DBC is the only USER . DBC counts up all the disk space attached to each AMP and considers that PERM space owned by DBC .

DBC owns all the disk space on day one of your system’s arrival. DBC will then begin to allocate space to other databases or users. Think of PERM space like money. If DBC has 10 Terabytes of space it is like having 10 dollars. If you give away 5 dollars, you only have 5 dollars left. Spool space is more like a speed limit.

DBC’s First Assignment is Spool Space

DBC will create a database called Spool_Reserve (any name will do), but it will reserve between 20% to 40% for Spool. What really happens is that DBC creates Spool_Reserve to claim PERM Space, but never places a table in the database.

When a database is given PERM Space, and no object is created in that database, it is used for Spool. Spool is unused PERM!

DBC’s 2nd Assignment is to CREATE Users and Databases

DBC’s 2^nd assignment will be to create some USERs or DATABASEs and the hierarchy begins. If a USER or DATABASE is assigned PERM space, it can CREATE tables.

The Teradata Hierarchy Begins

Notice in example 1 that DBC owns 10 TB of PERM space. Notice that after DBC created Spool_Reserve (4 TB), USER Retail (2 TB) and USER Financial (2 TB) that DBC now only owns only 2 TB of PERM space.

The Teradata Hierarchy Continues

USER Retail and USER Financial now create the databases and users desired.

Differences between PERM and SPOOL

There are 1,000 users in Retail. Since Retail has 10 GB of spool, that means that every user gets 10 GB of spool. That is the maximum limit for Retail. What it does NOT mean is that Retail is limited to only 10 GB of spool in total. Every user could logon and run a 9 GB query taking up Terabytes of Spool, and nobody would run out of spool. Spool is system wide and calculated on an individual level only.

Databases, Users, and Views

For security purposes, the Retail tables will be kept in their own database called Retail_Tbls (in this example). The general Retail User Population will NOT have access directly to these tables. A Database called Retail_Views houses the views that access the tables. So, the DBA will create Access Rights that allow the views to read the tables and the Users to SELECT from the views.

What are Similarities between a DATABASE and a USER?