Creating the Tables Database - Advanced SQL Engine - Teradata Database

Use the following procedure to create the Tables Database that will contain all data tables.

1. Log on as user DBADMIN.
2. Specify values for the following database parameters:

   Syntax Element	Explanation
   Database Name	Required. The name of the master database containing all tables and data. Recommendation: Use a name that provides a hint about the function of the database, for example, Tables, Tables_Database, or TBL_DB.
   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.
   Permanent	Required. The permanent space in bytes allocated to contain tables and other objects created in the database. Recommendation: For the Tables database, enter a value or an expression resulting in a numeric value equivalent to 80% of permanent space allocated to user DBADMIN. If you create child databases within the Tables database, divide the Tables database perm space among them according to the size of the tables each database contains. Tables do not have perm space allocations. They automatically take the space required to contain their data from the owning database.
   Spool	Recommendation: Not applicable. Queries running against the Tables database draw from the spool space available to the user.
   Temporary Space	Use TEMPORARY = n BYTES to define the space allowed by default for users to create global temporary tables within this database. The default is the largest value that is less than the owner temporary space and that is a multiple of the number of AMPs in the system. n must not exceed the owner temporary space. If no default temporary space is defined for a database, the space allocated for global temporary tables created in that database is the maximum temporary space allocated to the immediate owner of the database. The database/user must have adequate perm space to accommodate the global temporary table header on each AMP. Table header size varies by table definition and the maximum size for a table header is 1 MB.
   Account	Not applicable. Recommendation: Specify accounts at the profile or user level.
   Default Journal	The default location for journal images of tables created in the new or modified database. Recommendation: Specify only if you use journaling. See the syntax elements Before Journal and After Journal in this table.
   Comment	Optional. You can enter text to describe the database.
   Before Journal	Specifies journaling for “before” change row images as follows: Yes – specifies a single journal. No – specifies no journal. Dual – specifies dual journals. This option is not normally specified for the initial database implementation.
   After Journal	Specifies journaling for “after” change row images as follows: Yes – specifies a single journal on a different AMP from changed row. No – specifies no journal. Dual – specifies dual journals. Local – specifies a single journal on the same AMP as changed row. The Local option is only available for non-FallBack tables. This option is not normally specified for the initial database implementation.
   FallBack	Enabled by default. Specifies that the system will automatically create a duplicate of each table stored in the database space to provide backup in the event of a failure. You cannot use the NO FALLBACK option and the NO FALLBACK default on platforms optimized for fallback.

   This example is the minimum recommended specification for creating the Tables_Database. You can specify additional options later using the MODIFY DATABASE statement.

   CREATE DATABASE "Tables_Database" FROM "DBADMIN"
   AS PERM = 2e7 * (hashamp()+1)
   NO BEFORE JOURNAL
   NO AFTER JOURNAL;

3. You can optionally create additional child databases within the Tables database to group tables by function or department, but it is not required.

Reference Information

Reference topics are arranged according to the first step in which the topic appears.