Download (direct link):
• Databases and users
• Permanent tables
• Global temporary and volatile tables
- Partitioned I
• Data and data structures
• Stored Procedures
• Permanent Journals
Teradata RDBMS Database Administration
1 - 1Chapter 2: Building the Teradata RDBMS
Databases and Users
Databases and Users
This section provides information on Teradata RDBMS Databases and Users. It includes the following topics:
• "Creating a Database or User"
• "Dropping an Empty Database or User"
Creating a Database or User
Any user other than DBC or the site administrative user (if that user has ALL I privileges on ALL objects in the database) must be explicitly granted the
CREATE DATABASE and CREATE USER privileges before they can create another user or database, even in their own space. (For details, see "Granting CREATE and Access Privileges to a New User" on page 5-8.)
As you create users and databases, a hierarchical relationship evolves, as shown in the following example:
• DBC owns everything in the hierarchy, and is the immediate owner, or parent, of A and B.
• A owns C, D, and F. A is the immediate owner, or parent, of C and D.
• C is the immediate owner, or parent, of F.
• B is the immediate owner, or parent, of E.
The user who submits the CREATE DATABASE/USER statement is the creator of the database/user.
The database or user whose permanent space is used to create a new database/ user becomes the immediate owner of that new database/user.
In addition, that owner owns all databases/users below it in the hierarchy, because they are created from its original permanent space. The exception to this is if ownership of a specific database/user is transferred to another database/user (see "Increasing Space by Giving Ownership" on page 3-20).
2 - 2
Teradata RDBMS Database AdministrationChapter 2: Building the Teradata RDBMS
Databases and Users
The creator is not necessarily the immediate owner; a creator is the immediate | owner only if the new database/user resides within the creator's database (and thus is directly below the creator database in the hierarchy). With the appropriate privileges, the creator can create a new database/user somewhere else in the hierarchy. For more information on owner and creator privileges, see "Privileges To and From PUBLIC" on page 6-7.)
Dropping an Empty Database or User
You can use the DROP DATABASE/DROP USER statement only if the user or database to be dropped is empty. This means you must first drop all objects it contains, including data tables, views, global temporary tables, macros, stored procedures, triggers, and indexes before you can drop that database or user. Also, if a journal table exists, first be sure that no data table references it and then remove it with the DROP DEFAULT JOURNAL TABLE option of the MODIFY DATABASE/USER statement.
The database/user space that the drop makes available is added to the spool space of the immediate owner database/user.
Note: All physical database maintenance is performed automatically. You do not need to restructure or reorganize a database to reuse space or to eliminate pointer chains.
2 - 2 Teradata RDBMS Database Administration
Chapter 2: Building the Teradata RDBMS
This section discusses creating and maintaining data tables for your Teradata applications. The discussion includes summary procedures for creating, copying, dropping, altering, and recreating tables. A summary of the options available when planning your tables is also provided.
For statement syntax and defaults, see Teradata RDBMS SQL Reference, Volume 4. For performance issues, see Teradata RDBMS Performance Optimization.
Use this statement ... To .
CREATE [SET/MULTISET] TABLE create a new permanent table to have unique or duplicate rows, plus: • A table name that is unique within the owning database • A name and data type for each of one or more columns • A primary index (PI) that can be defined as: - Single-column or multi-column - Partitioned (PPI) - Unique or non-unique (UPI or NUPI, UPPI or NUPPI) - A single column defined with the IDENTITY attribute (to achieve row uniqueness during loading using system-generated values) Optionally, you can define: • Fallback protection. • Permanent journaling. • Data block size, minimum data block size, and/or maximum data block size • Percent of each disk cylinder to leave free during data load. • Referential integrity constraints (with or without checking) on column data. • A single or multi-column Primary Key (PK) or alternate key. • One or more single or multi-column Foreign Keys (FK). • Partitioning on non-indexed columns, with or without a range constraint. • One or more single or multi-column secondary indexes (SIs), each of which can be unique (USI) or non-unique (NUSI). • Whether a NUSI should be ordered by value or by hash • A UNIQUE constraint on a column without having to define it in an index. • An IDENTITY attribute on a column (if not the PI), to obtain a system-generated unique number without the overhead of a uniqueness constraint. • Compression of up to 255 distinct constant values per column, with a column limit determined only by the row length (because compressed values are added to the table-header row).