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 .. 129 130 131 132 133 134 < 135 > 136 137 138 139 140 141 .. 241 >> Next


• If neither BYTES nor KILOBYTES is specified, BYTES is assumed as the default.

As is the case elsewhere in Teradata SQL, BYTES can be abbreviated to BYTE. The word KILOBYTES can be abbreviated to KBYTE. Neither KILOBYTES nor KBYTES is a reserved word in Teradata SQL.

At any give time, the DATABLOCKSIZE value returned in response to a SHOW TABLE command is the value specified in the CREATE TABLE (or most recently entered ALTER TABLE) command. See also the descriptions of “ALTER TABLE” and “SHOW MACRO SHOW TABLE SHOW VIEW”.

8-86

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE TABLE

MINIMUM or MAXIMUM DATABLOCKSIZE

FREESPACE

Specifying Data Protection

Providing for Fallback Data

Since the RDBMS allows data block sizes in the range 6144 bytes (12 sectors) to 32256 bytes (63 sectors):

• MINIMUM DATABLOCKSIZE option is identical to specifying DATABLOCKSIZE = 6144 BYTES

• MAXIMUM DATABLOCKSIZE is identical to specifying DATABLOCKSIZE = 32256 BYTES.

As is the case elsewhere in Teradata SQL, MINIMUM and MAXIMUM can be abbreviated as MIN and MAX, respectively.

FREESPACE = n specifies the value for the percent freespace attribute, assuming that n is an integer constant. Consequently, n percent free space will remain on each cylinder during bulk load operations on this table. The PERCENT clause does not affect the semantics.

• The percent freespace attribute must be in the range 0 to 75 percent.

• If n is decimal or floating point constant, an integer value is obtained in the same manner as in other similar cases existing in Teradata SQL, namely, by taking the result of truncating n.

• At any give time, the FREESPACE value returned in response to a SHOW TABLE command is the value specified in the CREATE TABLE (or most recently entered ALTER TABLE) command. See the descriptions of the “ALTER TABLE” and “SHOW MACRO SHOW TABLE SHOW VIEW” statements.

Refer to Teradata RDBMS for UNIX Database Design and Administration for further information on optimizing data protection.

Specific protection for the data in a table is provided by the FALLBACK and the JOURNAL options. The FALLBACK option causes a duplicate copy (the fallback copy) to be maintained by the Teradata RDBMS. The fallback copy is used if the primary copy becomes unavailable. The JOURNAL option provides data protection through system-generated before- and after-journals that contain the data that changed. These journals are used to restore a table or to reverse the changes that were made to a table.

If you have specified FALLBACK (either explicitly or by default) in creating the table, the Teradata RDBMS automatically maintains a duplicate (fallback) copy of the data in the table. This fallback copy is used if the primary copy becomes unavailable.

To create a fallback copy of a new table, include the FALLBACK option in your CREATE TABLE statement. If there is to be no fallback copy of a new table in a database or user space for which

Teradata RDBMS for UNIX SQL Reference

8-87
Teradata SQL Syntax Guide

CREATE TABLE

Providing for a Permanent Journal

FALLBACK is in effect, specify the NO FALLBACK option as part of the CREATE TABLE statement defining the new table. Alternatively, if the default is desired, do not specify the option for the table.

Unless specified as part of the CREATE TABLE statement, protection for the data in a new table defaults to that specified for the database in which the table is created.

A permanent journal table is created when the default JOURNAL table option and a journal table name are specified as part of the CREATE/MODIFY DATABASE/USER statement. The named table resides in the database or user space that defined it. In addition, the specified JOURNAL parameters serve as the defaults for all the data tables created in that database or user space.

In defining a new table, you may override the established journaling defaults by specifying the JOURNAL options as part of the CREATE TABLE statement. Thus you may specify the journal table, the type of image (before-change or after-change, or both) and whether dual images are to be maintained for the created table, no matter the option established when the containing database or user was created.

8-88

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE TABLE

The following examples illustrate the use of CREATE TABLE:

Examples

The following CREATE TABLE statement defines the Employee

Example 1 table.

CREATE TABLE Employee

(EmpNo SMALLINT FORMAT '9(5)' CHECK (EmpNo >= 10001 AND EmpNo <= 32001)

NOT NULL,

Name VARCHAR(12) NOT NULL,

DeptNo SMALLINT FORMAT '999' CHECK (DeptNo >= 100 AND DeptNo <= 900),

JobTitle VARCHAR(12),

Salary DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99'

CHECK (Salary >= 1.00 AND Salary <= 999000.00),

YrsExp BYTEINT FORMAT 'Z9' CHECK(YrsExp >= -99 AND YrsExp

<=99),

dob DATE FORMAT 'MMMbDDbYYYY' NOT NULL,
Previous << 1 .. 129 130 131 132 133 134 < 135 > 136 137 138 139 140 141 .. 241 >> Next