16.10 - Creating the Views Database - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

Use the following procedure to create one or more Views Databases to contain all the views and macros required by the users.

  1. Start the client tool of your choice and log on as user DBADM.
  2. Specify values for the following database parameters.
    Syntax Element Explanation
    Database Name The name of the master database containing user-accessible views. To make data more easily accessible you can create several Views databases according to the needs of various departments or user groups, for example:
    • Views by department, such as Finance_Views and Purchasing_Views.
    • Views by user type, such as Batch_User_Views and General_User_Views.

    You can also create Views databases by functional layer, for example:

    • A layer of read-write views where each view mirrors a table, usable for batch loads and updates without direct access to data tables
    • A layer of read-only views that contain selected columns from one or more tables to combine related information from multiple tables or restrict access

    For a description of user types, see Types of Users.

    Owner Required. The name of the user or database that owns the space in which the object is being created.

    Recommendation: Enter the name of the primary administrative user, DBADM, previously set up in Setting Up the Database Administrator User.

    Permanent Space The permanent space in bytes or an expression resulting in a numeric value allocated to contain space-consuming objects created in the database.

    Recommendation: The Views database is not meant to contain data, but it is the best place to locate stored procedures that access the views. You can allocate a small amount of permanent space for stored procedures, for example 100 MB, and then adjust the allocation later using the MODIFY DATABASE statement.

    Spool Space Not applicable. Queries running in the Views database take spool space from the Spool_Reserve database.
    Temp Space Not applicable.
    Account Not applicable.

    Recommendation: Specify account at the profile or user level.

    For information on using accounts, see Working with Accounts.

    Default Journal Not applicable.

    Recommendation:Not applicable, because journaling is not specified for views.

    Comment Optional. You can enter text to describe the database.
    Before Journal Not applicable.
    After Journal Not applicable.
    FALLBACK Not applicable. Fallback is required only for individual mission-critical tables or tables so large that their size prevents timely backup to tape or external disk, and this database contains only views of the data tables.
    This example is the minimum recommended specification for creating a Views_Database. You can specify additional database options in the initial CREATE DATABASE statement, or add them later using the MODIFY DATABASE statement.
    CREATE DATABASE "Views" FROM "DBADMIN"
    AS PERM = 0
    NO FALLBACK
    NO BEFORE JOURNAL
    NO AFTER JOURNAL;
  3. Grant access privileges to the Views database, as shown in Working with Table Access Privileges for Views. After completing the procedure, return to this section and continue with the remaining tasks.

Reference Information

Step Topic Resources for Further Information
3
  • CREATE DATABASE
  • DELETE DATABASE
  • DROP DATABASE
  • MODIFY DATABASE
  • SQL Data Definition Language Syntax and Examples
  • SQL Data Definition Language Detailed Topics
An overview of SQL procedures and how they are stored in the database SQL Stored Procedures and Embedded SQL