16.10 - Creating the Tables 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 the Tables Database that will contain all data tables.

  1. Log on as user DBADM.
  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, DBADM.

    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 DBADM.

    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 = nBYTES 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. nmust 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 Not applicable, but 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.

    Recommendation:Fallback is required only for individual mission-critical tables or tables so large that their size prevents timely backup to tape or external disk.

    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 FALLBACK
    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

Step Topic Resources for Further Information
3 and 4 Syntax and options for:
  • CREATE DATABASE
  • DELETE DATABASE
  • DROP DATABASE
  • MODIFY DATABASE
  • SQL Data Definition Language Syntax and Examples
  • SQL Data Definition Language Detailed Topics