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 .. 167 168 169 170 171 172 < 173 > 174 175 176 177 178 179 .. 241 >> Next


Databases/Users Can journal table that has been dropped from the system, subsequent

Share a Journal Table CREATE TABLE statements that use the corresponding default will

return an error message.

The COLLATION option determines the ordering of data characters during comparison operations and when sorting data in response to a SELECT query that includes an ORDER BY or WITH...BY clause.

The MULTINATIONAL parameter defines that the default collation sequence for the user is one of the International sort orders (see the ORDER BY clause of the “SELECT” statement).

During any session, the user can override the COLLATION attribute by executing a SET SESSION COLLATION statement.

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.

See also the Teradata RDBMS for UNIX Database Design and

Administration, Chapter 12, “Permanent Journaling”.

The following examples illustrate the use of MODIFY USER:

Examples

The following statement can be used to change the permanent space Example 1 allocation for user Peterson:

MODIFY USER Peterson AS

PERMANENT = 6000000 BYTES;

Teradata RDBMS for UNIX SQL Reference

8-219
Teradata SQL Syntax Guide

MODIFY USER

Example 2

The following statement can be used to change user Chin’s default database and password:

MODIFY USER Chin

AS DEFAULT DATABASE = Personnel, PASSWORD = Nitram ;

Example 3

Two MODIFY statements are needed to change the default journal table if it resides in the user being modified.

For example, suppose the journal table “FinCopy” resides in user Jones. To change the default journal table from “FinCopy” to “Jrnll,” the present default journal table must be dropped and then a new journal table must be created.

The first statement removes FinCopy as the default and drops it from the system (if there are any existing tables that use it as their journal table, the statement will return an error):

MODIFY USER Jones

AS DROP DEFAULT JOURNAL TABLE ;

When the current journal table has been dropped, the second statement creates a new default journal table:

MODIFY USER Jones

AS DEFAULT JOURNAL TABLE = Jrnl1 ;

Example 4

If the current journal table does not reside under the user being modified, the following statement could be used to change the default journal table from FinCopy to Jrnll:

MODIFY USER Peterson

AS DEFAULT JOURNAL TABLE = Jrnl1 ;

Example 5

You do not need access privileges to change the PASSWORD, STARTUP, FALLBACK, or DEFAULT DATABASE definitions in space you own. Otherwise, the user submitting a MODIFY statement must have the DROP privilege on the user or database being changed.

By way of example, although Marks is not the creator of his own space and does not own himself, he can submit a MODIFY USER statement to change his password, modify his startup string, or redirect his default database. Marks may resize Finance, as well.

Change password and other options on user Marks:

MODIFY USER marks AS PASSWORD = design

,SPOOL = 1500000

,STARTUP = 'EXEC paystat;'

,DEFAULT DATABASE = payroll

8-220

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

ORDER BY Clause

ORDER BY Clause

The ORDER BY clause of the SELECT statement specifies how result data is to be sorted.

Refer to Chapter 7, “Queries: The SELECT Statement,”, “ORDER BY”, for a detailed description of the ORDER BY clause.

Teradata RDBMS for UNIX SQL Reference

8-221
Teradata SQL Syntax Guide RENAME

RENAME

Function

The RENAME statement renames an existing table, view, or macro. RENAME is flagged as non-ANSI, when the SQL flagger is enabled.

Syntax

RENAME-

MACRO --TABLE -- VIEW -

- oldname

TO

AS

where:

FF07A034

Syntax Element ... Specifies . . .
oldname the existing name for the macro, table, or view.
TO keywords introducing the new name for the
AS macro, table, or view.
newname the new name for the macro, table, or view.

Usage Notes

To use the RENAME statement, the user must have DROP privileges on the macro, table, or view to be renamed, and the appropriate CREATE privileges on the containing database.

• When a table is renamed, only the table name is changed. All statistics and privileges belonging to the table remain with it under the new name.

• When a macro, table, or view is renamed, an exclusive lock is placed on the item being renamed.
Previous << 1 .. 167 168 169 170 171 172 < 173 > 174 175 176 177 178 179 .. 241 >> Next