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


In this case, the value 1587" was found in the secondary index subtable, and the corresponding data row was selected and returned. However, the data for account 1761 had been changed by another user while the selection was in process.

Returns such as this are possible even if the data is changed only momentarily by a transaction that is ultimately aborted. The ACCESS lock is most useful to those who simply want an overview of data and are not concerned with consistency.

This example demonstrates the proper use of a row lock.

Example 3: LOCK ROW

1 CREATE TABLE customer (cust_id int/phone int/ fax int/ telex int)

PRIMARY INDEX (cust_id)/ UNIQUE INDEX(fax)/ INDEX(telex):

CREATE TABLE sales (custcode int/ zip int/ salesvol int);

User A:

BEGIN TRANSACTION;

LOCK ROW EXCL SELECT phone from customer where cust_id=12345; UPDATE customer set phone=3108292488 where cust_id=12345;

User As row lock prevents another user from accessing the same record. In the following, user As row lock, in conjunction with Lock Table, prevents user B from accessing the same record:

LOCKING TABLE sales for READ/ LOCKING ROW for WRITE SELECT telex from customer where fax=0;

UPDATE customer set telex=0 where fax=0;

SELECT zip from sales where custcode=111;

SELECT salesvol from sales where custcode=222;

END TRANSACTION;

User B:

BEGIN TRANSACTION;

LOCK ROW WRITE SELECT * from customer where cust_id=12345 INSERT INTO customer (12345/ 3108284422/ 3108684231/ 5555);

END TRANSACTION;

User Bs LOCK ROW statement waits until user As transaction ends before it can be completed.

8-206

Teradata RDBMS for UNIX SQL Reference
Function

Syntax

Teradata SQL Syntax Guide

MODIFY DATABASE

modify database

The MODIFY DATABASE statement changes the options that were specified when a database was created.

MODIFY DATABASE is flagged as non-ANSI when the SQL flagger is enabled.

Teradata RDBMS for UNIX SQL Reference

8-207
Teradata SQL Syntax Guide

MODIFY DATABASE

where:

Syntax Element ... Specifies . . .
dbname the name of the database to be modified.
AS an introduction to the list of options for modifying the database.
PERMANENT a keyword introducing the allocated fixed space.
number a new value (n) for fixed space allocation, in bytes. number may be entered as an integer, a decimal value, or as a floating point value.
BYTES an optional unit assigned to the fixed space value.
SPOOL a keyword introducing the allocated spool space.
number a new value (n) for the maximum space allocation for spool files, in bytes. number may be entered as an integer, a decimal value, or as a floating point value.
BYTES an optional unit assigned to the spool space.
ACCOUNT a keyword introducing the account identifier.
acctid a new identifier for the account to be charged for the space used by this database. acctid can be up to 30 characters (provided they are not Japanese characters, in which case acctid can be up to 30 bytes, using any of the supported character sets), and must be enclosed by apostrophes.
NO FALLBACK PROTECTION a new default for duplicate copy protection of each data table subsequently created in the database. The current fallback setting for existing data tables remains unchanged. The FALLBACK keyword used alone implies PROTECTION.

8-208

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

MODIFY DATABASE

Syntax Element ... Specifies . . .
NO DUAL BEFORE JOURNAL a new default for the number of before change images to be maintained for data tables subsequently created in the database. The NO keyword terminates any current journaling default. If the JOURNAL keyword is specified without NO or DUAL, single-copy journaling is implied. If journaling is specified, a DUAL journal is maintained for data tables with FALLBACK protection. Existing images are not affected until the corresponding table is updated. This option may appear twice in the same statement: once to specify a BEFORE or AFTER image, and again to specify the alternate type. If only one type is specified, then the current default is modified only for that type. If both BEFORE and AFTER are specified then the two must not conflict. The JOURNAL keyword without AFTER or BEFORE indicates that both types of images are to be maintained. In this case, the current default for either or both types is modified accordingly. For example, if only AFTER JOURNAL is specified, the current default for before-change images remains in effect.

Teradata RDBMS for UNIX SQL Reference

8-209
Teradata SQL Syntax Guide

MODIFY DATABASE

Syntax Element ... Specifies . . .
NO DUAL LOCAL NOT LOCAL AFTER JOURNAL the type of image to be maintained for the table; any existing images are not affected until the table is updated. NO, DUAL, LOCAL, or NOT LOCAL: the NO and DUAL options specify the number of after-change images to be maintained for the table. NOT LOCAL and LOCAL specify whether single after-image journal rows for non-fallback data tables are written on the same virtual AMP (LOCAL) as the changed data rows, or on another virtual AMP in the cluster (NOT LOCAL).
Previous << 1 .. 163 164 165 166 167 168 < 169 > 170 171 172 173 174 175 .. 241 >> Next