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 .. 115 116 117 118 119 120 < 121 > 122 123 124 125 126 127 .. 241 >> Next


clause:

USING ssnumfile (INTEGER)

BEGIN TRANSACTION ;

INSERT INTO Employee (SocSecNo) VALUES (:ssnumfile) USING ssnumfile (INTEGER)

INSERT INTO Employee (SocSecNo) VALUES (:ssnumfile) USING ssnumfile (INTEGER)

INSERT INTO Employee (SocSecNo) VALUES (:ssnumfile) END TRANSACTION ;

The following examples illustrate the use a DDL statement in an Scenario 3 explicit transaction. These transactions create a temporary table,

perform an aggregate operation on the result of another aggregate operation, and then drop the temporary table.

Two transactions are used because a DDL statement must be either the only statement in a transaction, or the last statement in a transaction

BT;

CREATE TABLE DeptSumSal

(DeptNo SMALLINT FORMAT '999' BETWEEN 100 AND 900

NOT NULL, SumSal DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99')

PRIMARY INDEX(DeptNo);

ET;

BT;

INSERT INTO DeptSumSal

SELECT DeptNo, SUM(Salary) FROM Employee GROUP BY DeptNo;

SELECT AVG(SumSal) FROM DeptSumSal;

DROP TABLE DeptSumSal;

ET;

The following example is structured as a BTEQ multistatement Scenario 4: Implicit request, and therefore, is processed as a single implicit transaction.

Transaction (BTEQ)

Note the placement of the USING modifier and the semicolons. With this construct, the failure of any WHERE conditions causes the transaction to abort and all completed inserts and updates to be rolled back.

USING varl(CHAR), var2(CHAR), var3(CHAR)

INSERT INTO TestTabU (Cl) VALUES (:var1)

; INSERT INTO TestTabU (Cl) VALUES (:var2)

; INSERT INTO TestTabU (Cl) VALUES (:var3)

; UPDATE TestTabU SET C2 = Cl + 1 WHERE Cl = :var1

; UPDATE TestTabU SET C2 = Cl + 1 WHERE Cl = :var2

Teradata RDBMS for UNIX SQL Reference

8-41
Teradata SQL Syntax Guide

CHECKPOINT

CHECKPOINT

The CHECKPOINT statement places a mark in a journal table which may be used to coordinate future recovery activities.

CHECKPOINT is flagged as non-ANSI.when the SQL Flagger is turned on.

Syntax

checkpoint -

tname

I— , NAMED chkpt_name —I L-I

FF07A007

where:

Syntax Element... Description
tname Specifies the journal table that is to be marked with the checkpoint entry.
chkpt_name Specifies a label that may be used to reference the checkpoint entry in database recovery activities. • chkpt_name should be unique. However, if the chkpt_name duplicates an existing entry in the journal, the entry may be qualified by the system-assigned event number. • If a chkpt_name is not specified, the checkpoint entry must be referenced in recovery activities by its event number (see Usage Notes).

If an explicit transaction or a multistatement request contains a Usage Notes CHECKPOINT statement, that CHECKPOINT statement must

precede any INSERT, UPDATE, or DELETE statements in the transaction or request.

To checkpoint a journal table, the user must meet one of the following criteria:

• have CHECKPOINT privilege on the journal table

• have CHECKPOINT privilege on the database containing the journal table

• be an owner of the database containing the journal table

• be an immediate or indirect owner of the journal table.

The CHECKPOINT statement causes the Teradata RDBMS to place a read lock on all data tables that write journal images to the table

8-42

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CHECKPOINT

named in the “CHECKPOINT” statement. This lock causes any new transactions to wait until the checkpoint operation is complete. It also causes the checkpoint operation to await the completion of outstanding update transactions.

This action guarantees that the checkpoint saved in the journal represents a clean point in the transaction environment. When the checkpoint operation completes, the locks are released.

Each checkpoint entry is assigned an event number by the Teradata RDBMS. This number may be returned as a result of CHECKPOINT processing; it is also stored, along with other information about statement execution, in a Data Dictionary table. You can review the table data through the DBC.Events system view.

Example

When this statement is executed, all update activity is ceased on tables that write journal images to the journal table, (JnlTable). A record containing the DailyPayMaint label is placed into the journal table. The label may then be used in rollforward or rollbackward operations.

The following statement can be used to place a checkpoint entry into a journal table:

CHECKPOINT AccountDB.JnlTable, NAMED DailyPayMaint;

Teradata RDBMS for UNIX SQL Reference

8-43
Teradata SQL Syntax Guide

COLLECT STATISTICS

COLLECT STATISTICS

Function

The COLLECT STATISTICS statement collects statistical data for one or more columns of a table that may be used by the Teradata Optimizer in optimizing data access.

The purpose of COLLECT STATISTICS is to provide data that is used internally by the Teradata RDBMS to optimize data access. The statistical data that is collected is retained in an internal format for use during the optimizing phase, and is not available as user-readable data.
Previous << 1 .. 115 116 117 118 119 120 < 121 > 122 123 124 125 126 127 .. 241 >> Next