Creating the Views Database - Teradata Database

Creating the Views Database

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

1 Start Teradata Administrator and log on as user DBADMIN.

2 From the main screen, click the Preview Mode button to specify display of generated SQL.

3 Click Tools>Create>Database. The Create Database dialog box appears.

4 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 Finance_Views Personnel_Views_and_Macros Purchasing_Views Views by user type General_User_Views Update_User_Views Batch_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” on page 104.
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, DBADMIN, previously set up in “Setting Up the Database Administrator User” on page 30.
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 User Accounts” on page 156.
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. Recommendation: Uncheck Fallback box.

Note: 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.

5 Click the Create button to create the database. The Query Window displays a generated CREATE DATABASE statement similar to the following:

CREATE DATABASE "Views" FROM "DBADMIN"

AS PERM = 0

NO FALLBACK

NO BEFORE JOURNAL

NO AFTER JOURNAL

6 In the Query Window, click the Run Query button . A Query Complete message appears in the bottom left corner of the main window when the query executes.

7 Close the Query Window and the Create Database dialog box.

8 Grant access privileges to the Views database, as shown in “Working with Table Access Privileges for Views” on page 59. After completing the procedure, return to this section and continue with the remaining tasks.

Reference Information

For step...	Information on...	Is available in...
All	options for setup and operation of Teradata Administrator	Teradata Administrator User Guide
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