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 .. 164 165 166 167 168 169 < 170 > 171 172 173 174 175 176 .. 241 >> Next

DEFAULT JOURNAL TABLE= dbname. tname DROP DEFAULT JOURNAL TABLE= tname a redefinition for the current journal table or removes its status as the default for the database being modified. The DROP keyword removes the default status of the journal table currently defined as the default for the database being modified. If the journal table resides in the database being modified, DROP also deletes the table from the system. An error message is returned if the DROP request would delete a journal table that is being used by active data tables. The tname parameter is required if this clause is specified without the DROP keyword. If a database is not specified, then the database being modified is assumed. If the database being modified does not have a journal table, tname is created. If a different database is specified, then it must already exist and tname must have been defined as its default journal table. Specifying this option does not change the status of existing data tables in the modified database.
Several options can be included in a single MODIFY DATABASE statement. No order is imposed, but an error is reported if options are duplicated or conflicting options are included.

To use the MODIFY DATABASE statement, the user must have the Privileges Required DROP DATABASE privilege on the referenced database.

MODIFY DATABASE cannot be used to modify users.

When a MODIFY DATABASE statement is executed, an exclusive lock is placed on the database that is being modified.

8-210

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

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

One or more options may be changed in the same MODIFY statement. A changed option applies to all subsequent operations that occur for the database.

If an existing option is not changed by a MODIFY statement, that option remains the same.

Two modify statements are needed to change a default journal table Changing the Default that resides in the database being modified. The first statement is

Journal Table Residing in used to DROP the present journal table from the system. An error

the Modified Database message is returned if the journal table is being used by active data

tables.

After the table is dropped, a second statement is required to define a new default journal table.

A dropped journal table cannot be restored. Archives containing Dropped Journal Table data tables that reference the dropped journal are no longer valid

Cannot be Restored and cannot be used.

Two or more databases/users can share a journal table. However, if the sharing databases/users retain as their default the name of a journal table that has been dropped from the system, subsequent CREATE TABLE statements that use that default will return an error message.

_ The LOCAL single AFTER image journal is supported analogously

Local Journaling to single BEFORE image journal:

• 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. DROP DATABASE privilege is required.

• LOCAL single AFTER image journaling is restricted to nonfallback 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.

Two or More Databases/Users Can Share Journal Table

Teradata RDBMS for UNIX SQL Reference

8-211
Teradata SQL Syntax Guide

MODIFY DATABASE

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

The following examples illustrate the use of MODIFY DATABASE:

Examples8

The following statement may be used to change the permanent Example 1 space allocation for the Personnel database to 6,000,000 bytes:

MODIFY DATABASE Personnel AS PERMANENT = 6000000 BYTES;

Two MODIFY statements are needed to change the default journal Example 2 8 table if it resides in the database being modified.

For example, suppose the journal table “FinCopy” resides in the Personnel database. To change the default journal table from “FinCopy” to “Jrnll", the present default journal table must be dropped by entering the statement below. This statement removes “FinCopy” as the default and also drops it from the system.

If any existing tables use “FinCopy” as their journal table, the statement returns an error message.

MODIFY DATABASE Personnel AS DROP DEFAULT JOURNAL TABLE ;

Now that the present default journal has been dropped, this Example 3 statement may be used to define a new default journal table:

MODIFY DATABASE Personnel AS DEFAULT JOURNAL TABLE = Jrnl1 ;
Previous << 1 .. 164 165 166 167 168 169 < 170 > 171 172 173 174 175 176 .. 241 >> Next