Teradata Database:

A Teradata database is a defined logical repository for:

  • Tables
  • Views
  • Macros
  • Triggers
  • Stored Procedures

Attributes that may be specified for a database:

  • Perm Space – maximum amount of space available for tables
  • Spool Space – maximum amount of work space available for requests
  • Temp Space – maximum amount of temporary table space

A Teradata database is created with the CREATE DATABASE command.

Example:

CREATE DATABASE Database_2 FROM Sysdba

AS PERMANENT = 20e9, SPOOL = 200e6;

“Database_2” is owned by “Sysdba”.

A database is empty until objects are created within it.

Teradata User:

A Teradata user is a database with an assigned password.

A Teradata user may logon to Teradata and access objects within:

  • itself
  • other databases for which it has access rights

Examples of attributes that may be specified for a user:

  • Perm Space – maximum amount of space available for tables
  • Spool Space – maximum amount of work space available for requests
  • Temp Space – maximum amount of temporary table space

A user is an active repository while a database is a passive repository.

A user is created with the CREATE USER command.

Example:

CREATE USER User_C FROM User_A

AS  PERMANENT = 10e6

,SPOOL = 100e6

,TEMPORARY =  80e6

,PASSWORD = lucky_day ;

“User_C” is owned by “User_A”.

A user is empty until objects are created within it.

Database – User Comparison:



Logon and establish a session with a priority

May have a startup string  Default database, dateform, timezone, and default character set Collation Sequence

  • You can only LOGON as a known User to establish a session with Teradata.
  • Tables and Stored Procedures require Perm Space.
  • Views, Macros, and Triggers are definitions in the DD/D and require no Perm Space.
  • A database (or user) with zero Perm Space may have views, macros, and triggers, but cannot have tables or stored procedures.

Hierarchy of Databases and Users:


  • A new database or user must be created from an existing database or user.
  • All Perm space specifications are subtracted from the immediate owner or parent.
  • Perm space is a zero sum game – the total of all Perm Space allocations must equal the total amount of disk space available to Teradata.
  • Perm space is only used for tables and stored procedures.
  • Perm space currently unused is available to be used as Spool or Temp space.

Example of a System Hierarchy:



  • A User and/or a Database may be given PERM space.
  • In this example, Mark and Tom have no PERM space, but Susan does.

Permanent Space:



  • Table rows, index subtable rows, and stored procedures row use Perm space.
  • Fallback protection uses twice the Perm space of No Fallback.
  • Perm space is deducted from the owner’s database space.
  • Disk space is not reserved ahead of time, but is available on demand.
  • Perm space is defined globally for a database.
  • Perm space can be dynamically modified.
  • The global limit divided by the number of AMPs is the per/AMP limit.
  • The per/AMP limit cannot be exceeded.
  • Good data distribution is crucial to space management.

Spool Space:


CREATE USER Susan FROM CS_Users AS PERMANENT = 40e6 BYTES,

SPOOL = 240e6 BYTES, PASSWORD = secret …



  • Spool space is work space acquired automatically by the system for intermediate query results or answer sets.
  • SELECT statements generally use Spool space.
  • Only INSERT, UPDATE, and DELETE statements affect table contents.
  • The Spool limit cannot exceed the Spool limit of the original owner.
  • The Spool limit is divided by the number of AMPS in the system, giving a per-AMP limit that cannot be exceeded.
  • “Insufficient Spool” errors often result from poorly distributed data or joins on columns with large numbers of non-unique values.
  • Keeping Spool rows small and few in number reduces Spool I/O.

Temporary Space:


CREATE USER Susan FROM CS_Users AS PERMANENT = 40e6 BYTES,

SPOOL = 240e6 BYTES, TEMPORARY = 120e6 BYTES, PASSWORD = secret …



  • Temporary space is space acquired automatically by the system when a “Global Temporary” table is used and materialized.
  • The Temporary limit cannot exceed the Temporary limit of the original owner.
  • The Temporary limit is divided by the number of AMPS in the system, giving a per-AMP limit that cannot be exceeded.
  • “Insufficient Temporary” errors often result from poorly distributed data or joins on columns with large numbers of non-unique values.
Note: Volatile Temporary tables and “derived” tables utilize Spool space.

System Variables and Keywords:

As the requesting user, system variables and the help function can be used to display information about your session, objects within a database/user, etc.

USER  –  username                                ACCOUNT   –  account id

DATABASE  –  current database     SESSION  –  session #


Creating Tables:

Creating a table requires …

–  defining columns

–  assigning of a primary index

–  optional assignment of secondary indexes

CREATE TABLE Employee

(Employee_Number  INTEGER NOT NULL
,Last_Name  CHAR(20) NOT NULL
,First_Name  VARCHAR(20)
,Salary_Amount  DECIMAL(10,2)
,Department_Number  SMALLINT
,Job_Code  CHAR(3))

UNIQUE PRIMARY INDEX (Employee_Number)
INDEX (Last_Name) ;

  • Database objects may be created or dropped as needed.

Secondary indexes may be

–  created at table creation

–  created after table creation

–  dropped after table creation

Data Types:



Access Rights and Privileges:

Data Definition Privileges

Command Object

Database and/or User

CREATE                                           Table and/or View

DROP                                              Macro and/or Trigger

Stored Procedure

Role and/or Profile

Data Manipulation Privileges

SELECT

INSERT                                               Table

UPDATE                                               View

DELETE

EXECUTE                                       Macro and/or Stored Procedure

Data Control Privileges

DUMP                                                    Database

RESTORE                                               Table

CHECKPOINT                                          Journal

Privileges on

GRANT                                                  Databases

REVOKE                                                 Users

Objects