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 .. 118 119 120 121 122 123 < 124 > 125 126 127 128 129 130 .. 241 >> Next


Syntax

COMMIT 1 L WORK H TT-4 FF07A046
Syntax Element... Description
WORK If the SQL flagger is on, the absence of WORK causes the statement to be flagged.

COMMIT Is Explicit

There are no implicit transactions in ANSI mode. The COMMIT must always be explicitly stated, and be the last statement of a transaction in order for the transaction to terminate successfully.

COMMIT Is Not Valid in Teradata Mode

COMMIT and BTEQ

In Teradata mode, the COMMIT statement is not allowed. If used, it returns a failure and aborts the transaction.

If you use COMMIT in a BTEQ script with either the .SESSION or the .REPEAT command, you must send the COMMIT statement along with the repeated SQL statement as one request.

If you send the repeated request without the COMMIT, one of the requests is eventually blocked by other sessions and the job hangs because of a deadlock.

Teradata RDBMS for UNIX SQL Reference

8-51
Teradata SQL Syntax Guide

COMMIT

The following dummy example illustrates how this should be done:

BTEQ Example

1 .session trans ansi

.sessions 10

.logon TDPID/USER,PASSWD

.import data file = <data file name>

.repeat I

using i(integer), j(integer)

insert into <table name> (col1, col2)

values (:1, :j); COMMIT WORK;

.quit

The following examples illustrate the use of COMMIT:

Examples

The INSERT statement in the following example opens the Example 1 transaction. COMMIT closes the transaction.

INSERT INTO employee (name, empno)

VALUES ('Sinclair P', 101)

WHERE dept = '400';

COMMIT;

The following UPDATE initiates the transaction and COMMIT Example 2 WORK terminates it:

UPDATE parts SET part_num = 2 0555 WHERE location = 'seoul';

COMMIT WORK;

8-52

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE DATABASE

Function

Syntax

CREATE DATABASE

The CREATE DATABASE statement creates a database in which other objects may be created.

CD is the abbreviation for CREATE DATABASE.

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

CREATE DATABASE -----------name—,-------------------------------------------p AS -@

'— FROM dbname —'

where:

Syntax Element. . . . Specifies . . .
name the name of the new database.
dbname the name of the immediate owning user/ database. The default is the username associated with the current session.

Teradata RDBMS for UNIX SQL Reference

8-53
Teradata SQL Syntax Guide

CREATE DATABASE

Syntax Element Specifies . . .
PERMANENT = n BYTES the total number of bytes (n) to be reserved for permanent storage of the new database. The space is taken from unallocated space in the database of the immediate owner. n may be entered as an integer, a decimal value, or as a floating point value. This option must be specified. There is no default.
SPOOL = n BYTES the number of bytes (n) to be allocated for spool files. The default is the largest value that is not greater then the owner’s spool space and that is a multiple of the number of AMPs on the system. n must not exceed the owner’s spool space. n may be entered as an integer, a decimal value, or as a floating point value.
ACCOUNT = acctid the account to be charged for the space used by this database. If not specified, it defaults to the account identifier of the immediate owner database. acctid can be up to 30 characters and must be enclosed by apostrophes.
NO FALLBACK PROTECTION whether to create and store a duplicate copy of each table created in the new database. The default value is NO FALLBACK. This setting may be overridden for a particular data table when the table is created (refer to “CREATE TABLE” statement). The FALLBACK keyword used alone implies PROTECTION.
NO DUAL BEFORE JOURNAL the number of before change images to be maintained by default for each data table created in the new database. The JOURNAL keyword without NO or DUAL implies single-copy journaling. If journaling is specified, a DUAL journal is maintained for data tables with FALLBACK protection. The JOURNAL keyword without BEFORE implies both types (BEFORE and AFTER) of images.

8-54

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE DATABASE

Privileges

Syntax Element Specifies . . .
NO DUAL LOCAL NOT LOCAL AFTER JOURNAL the type of image to be maintained by default for data tables created in the new database. The JOURNAL keyword without NO or DUAL implies single-copy journaling. If journaling is specified, a DUAL journal is maintained for data tables with FALLBACK protection. 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). See also Usage Notes below, “Local Journaling”. The JOURNAL keyword without AFTER implies both types (BEFORE and AFTER) of images. The default is no journaling If only AFTER JOURNAL is specified, then a before-change image is not maintained. If both types are specified, the two specifications must not conflict. This setting may be overridden for a particular data table when the table is created (refer to “CREATE TABLE” statement).
Previous << 1 .. 118 119 120 121 122 123 < 124 > 125 126 127 128 129 130 .. 241 >> Next