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 .. 141 142 143 144 145 146 < 147 > 148 149 150 151 152 153 .. 241 >> Next


The last line of the example above would read:

DELETE FROM ResUseView ALL ; COMMIT;) ;

The “slow path” method is used when no COMMIT statement is stated in the macro, or the request that executes the macro.

Teradata RDBMS for UNIX SQL Reference

8-123
Teradata SQL Syntax Guide

DELETE

The DELETE...ALL statement in the following BTEQ request also Example 7 invokes “fast path” processing. Note that the statements are

structured as a single multiple-statement request, and therefore, are processed as an implicit transaction. Note that DELETE is the last statement in the series.

SELECT * FROM DBC.LogOnOff WHERE LogDate = DATE AND UserName = 'Administrator' ; SELECT LogTime,

UserName, Event, AccountName FROM DBC.LogOnOff WHERE LogDate = DATE AND UserName NOT IN ('Administrator', 'SecAdmin', 'Oper') ; SELECT LogTime, Event, LogicalHostId, IFPNo FROM DBC.LogOnOff WHERE LogDate = DATE AND UserName = 'Oper' ; DELETE FROM DBC.LogOnOff ALL ;

In ANSI mode, the COMMIT statement must follow the DELETE statement. The last line of the above example would read:

UserName = 'Oper' ; DELETE FROM DBC.LogOnOff ALL ; COMMIT;

8-124

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

DELETE DATABASE, DELETE USER

DELETE DATABASE, DELETE USER

Function

The DELETE DATABASE or DELETE USER statements delete all data tables, views, and macros from a database or user. The database or user remains on the Teradata RDBMS as a named space of a particular size.

DELETE DATABASE, DELETE USER are flagged as non-ANSI, when the SQL flagger is enabled.

Syntax

DELETE -| DEL where: T- DATABASE —³ name I USER 1 CThh FF07R018
Syntax Element Specifies . . .
name the name of the database or user from which all tables, views, and macros are to be deleted.

Locks

When a DELETE DATABASE, DELETE USER statement is executed, an EXCLUSIVE lock is placed on the database or user from which objects are being deleted.

Privileges Required

To delete objects from a database or user space via DELETE DATABASE or DELETE USER, the user must have the DROP privilege on the referenced database/user.

DELETE USER can delete a database, and DELETE DATABASE can delete a user.

Access Rights Are Removed With DELETE

The DELETE DATABASE/USER statement also removes all access rights to the objects that were deleted. Because the Dump and Restore utility does not automatically recover these access rights, they must be reestablished following the restore operation.

Teradata RDBMS for UNIX SQL Reference

8-125
Teradata SQL Syntax Guide

DELETE DATABASE, DELETE USER

The DELETE DATABASE/USER statement will not delete a journal DELETE DATABASE/ table. The user must enter a MODIFY DATABASE/MODIFY USER

USER Does Not Delete statement to remove a journal table.

Journal Tables

After all the data tables, views, and macros have been deleted from After DELETE Structure a database/user, the structure remains intact (as though its CREATE

Remains Intact statement had just been executed) and can be used in a subsequent

restore operation. The DELETE DATABASE/USER statement is used when a database or user has been only partially restored and is not in a usable state.

The following statement can be used to delete all tables, views, and Example macros from the UsedCars database:

DELETE DATABASE UsedCars;

8-126

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

DROP DATABASE, DROP USER

DROP DATABASE, DROP USER

The DROP DATABASE or DROP USER statements can drop empty databases or users only; all objects associated with that database or user must have already been dropped.

When a database or user is dropped, the disk space is freed.

DROP DATABASE, DROP USER are flagged as not ANSI, when the SQL flagger is enabled.

Syntax

DROP-

DATABASE-— USER —

name

where: FF07A019
Syntax Element... Specifies . . .
name the name of the database or user that is to be dropped.

To drop a database or user, a user must have the DROP DATABASE Privileges or DROP USER privilege on the database or user. The database or

user that is being dropped cannot own other databases or users, and must be at the bottom of the database hierarchy.

The object type is not checked; DROP DATABASE and DROP USER have the same effect.

If a database or user contains a journal table in their disk space, then Journal Tables the database/user cannot be dropped until the journal table is

removed from the system. A MoDiFY DATABASE /USER statement must be used to drop the journal table.

The drop operation performs the following functions:

DROP Functions

• Sets an exclusive lock on the database or user.

• Adds the disk space that the drop makes available to that of the immediate owner database.

After a database is dropped, it cannot be recovered by using the Dump and Restore utility unless the database is restored.

Teradata RDBMS for UNIX SQL Reference
Previous << 1 .. 141 142 143 144 145 146 < 147 > 148 149 150 151 152 153 .. 241 >> Next