Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 119 120 121 122 123 124 < 125 > 126 127 128 129 130 131 .. 241 >> Next

DEFAULT JOURNAL TABLE = dbname.tname Identifies the default table that is to receive the journal images of data tables created in the new database. tname must be defined if journaling is requested, but it need not reside in the new database. tname is automatically created in the new database if dbname is not specified, or the new database is specified. If a different database is specified, then that database must exist and tname must have been defined as its default journal table.

An option can only be defined once. The PERMANENT or PERM option must be specified.

Privileges given to the creator and owner are WITH GRANT OPTION.

To create a database, a user must have the CREATE DATABASE privilege on the immediate owner database. The creator and owner receive all privileges on the newly created database.

The newly created database also receives all relevant privileges on itself.

Teradata RDBMS for UNIX SQL Reference

8-55
Teradata SQL Syntax Guide

CREATE DATABASE

Rules for Using CREATE DATABASE

Local Journaling

Privileges given to the database are not WITH GRANT OPTION.

No privilege is needed to specify a DEFAULT JOURNAL TABLE in a database other than the database being created. However, when a table is created that specifies a journal, then the user creating the table must have INSERT privilege on the journal table.

The following rules apply to the CREATE DATABASE statement.

• The combined total number of databases and users for any one system is limited to 32,767.

• A database may contain only one journal table. However, any table in a particular database may use a journal table in a different database.

• When a database contains a journal table, the journal table shares the database’s PERMANENT storage with any data tables created in that database.

• If necessary, the defined PERM or SPOOL space is changed to the next higher value that is a multiple of the number of AMPs on the system.

• When a CREATE DATABASE statement is executed, an EXCLUSIVE lock is placed on the database being created.

The LOCAL single AFTER image journal is supported analogously to single BEFORE image journal:

The Access Rights required to create or drop LOCAL single AFTER image journal are the same as for the analogous operation on single BEFORE image journal.

LOCAL single AFTER image journaling is restricted to non-fallback data tables.

Archive/Recovery rules for LOCAL single AFTER image journal and single BEFORE image journal are the same, except that LOCAL single AFTER image journal is used with ROLLFORWARD only, while single BEFORE image journal is used with ROLLBACK only.

MultiLoad, FastLoad and Archive/Recovery are impacted by the use of LOCAL journaling.

See also the Teradata RDBMS for UNIX Database Design and Administration, Chapter 12, “Permanent Journaling”.

8-56

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE DATABASE

The following examples illustrate the use of CREATE DATABASE:

Examples

The following statement creates a database named Personnel from Example I database Administration:

CREATE DATABASE Personnel FROM Administration AS PERMANENT = 5000000 BYTES,

FALLBACK,

BEFORE JOURNAL, DUAL AFTER JOURNAL,

DEFAULT JOURNAL TABLE = Personnel.FinCopy;

The FALLBACK keyword specifies that for each table created in the Personnel database, the default is to store a secondary (duplicate) copy in addition to the primary copy.

The JOURNAL option specifies that the default journaling for each data table is to maintain a single copy of the before-change image and dual copies of the after-change image. A duplicate before-change image is maintained automatically for any table in this database that uses both the fallback and the journal defaults.

The DEFAULT JOURNAL TABLE clause is required because journaling is requested. This clause specifies that a new journal table named “FinCopy” is to be created in the new database.

To create a new database for the Finance department, use the Example 2 “CREATE DATABASE” statement:

CREATE DATABASE finance FROM sysadmin AS

PERMANENT = 60000000 ,SPOOL = 12 0000000 ,FALLBACK PROTECTION ,AFTER JOURNAL ,BEFORE JOURNAL

,DEFAULT JOURNAL TABLE = finance.journals ,ACCOUNT = 'ACCTG'

The Finance database is created from the space available in Sysadmin. The 60,000,000 value represents bytes of storage. To create a database, the initiator must have CREATE DATABASE privileges on the FROM entry. The new database receives all privileges that have been granted to the initiator.

In the example, the FROM clause allocates space for finance from the Sysadmin space rather than from user space; therefore, Sysadmin is Finance’s immediate owner, and Marks is not in the hierarchy of ownership. However, Marks is granted automatic creator privileges on Finance, which include the right to create other objects in Finance’s space.

Teradata RDBMS for UNIX SQL Reference

8-57
Previous << 1 .. 119 120 121 122 123 124 < 125 > 126 127 128 129 130 131 .. 241 >> Next