in black and white
Main menu
Share a book About us Home
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 187 188 189 190 191 192 < 193 > 194 195 196 197 198 199 .. 241 >> Next

Correction of data values in the referenced or referencing tables so that full referential integrity exists between the two tables is the responsibility of the user. The rows in the error table can be used to define which corrections must be made. Maintenance of the error table is also the responsibility of the user.

The Archive (ARC) utility provides for the archive and restore of individual tables. It also provides for copying tables from one Teradata RDBMS to another.

When a table is restored or copied into a Teradata RDBMS, the dictionary definition of that table is also restored. The dictionary definitions of both the referenced and referencing table contain the complete definition of a reference.

By restoring a single table, it is possible to create an inconsistent reference definition in the Teradata RDBMS. When a table is restored which is either a referenced or referencing table, the reference is marked in the dictionary definitions as inconsistent. A new function in the ARC utility can be used to validate these references once the restore is done.

Refer to the Teradata Archive/Recovery for Channel-Attached Systems and the Teradata ASF2 Reference manuals.

While a table is marked as inconsistent, no updates inserts or deletes are permitted. The table will be fully usable only when the inconsistencies are resolved.

It is possible that the user either intends to or must revert to a definition of a table which results in an inconsistent reference on that table. There is a special option of the ALTER TABLE statement which can be used to remove these inconsistent references from a table.

The option is:


Teradata RDBMS for UNIX SQL Reference

Advanced SQL

Referential Integrity

FastLoad and MultiLoad

The user executing the statement must have DROP rights on the target table of the statement.

The result of this statement is to remove those internal indexes used to establish references which contain inconsistencies. The base data of the table is not modified.

Foreign key references are not supported for any table which is the target table for a FastLoad or MultiLoad. For further details, refer to:

• Teradata RDBMS for UNIX Database Design & Administration

• Teradata Fastload Reference

• Teradata Multiload Reference


Teradata RDBMS for UNIX SQL Reference
Advanced SQL

Updatable Cursors


Updatable Cursors

Updatable Cursors

Cursors are used by the Preprocessor point to the first row accessed by a SQL query. The Preprocessor then increments the cursor as needed.

The Preprocessor translates and compiles application programs written in C, COBOL, and PL/I containing embedded Teradata SQL. The Preprocessor output is an executable version of the C, COBOL, and PL/I programs. This output is then also linked to the CLIv2 (Call Level Interface Version 2) program for translation for the Teradata RDBMS.

Several features enable ANSI standard updatable cursor functionality:

• FOR CURSOR option in the SELECT statement. This option is not accessible by the user.

• WHERE CURRENT OF [cursor name] option in DELETE and UPDATE statements

• CHECKSUM locking mode option in LOCKING modifier

Refer to the Teradata Application Programming with Embedded Teradata SQL for C, Cobol, and PL/I, for details on implementing these options.

Note: The updatable cursor options are not applicable to and cannot be entered via BTEQ. They are only applicable for entry via the Preprocessor.

Teradata RDBMS for UNIX SQL Reference

Advanced SQL

EXPLAIN Modifier: Examples of Complex Queries


Example l: SELECT

Example 2: SELECT with WHERE on Non-Unique Index

EXPLAIN Modifier: Examples of Complex Queries

The EXPLAIN modifier terms are defined in Chapter 8 under “EXPLAIN Modifier”. Other examples of using EXPLAIN are provided in Teradata RDBMS for UNIX Database Design and Administration, Chapter 17, “Designing the Database for Optimum Performance”.

The Personnel.Employee table has a unique primary index defined on the EmpNo column and a nonunique secondary index defined on the Name column.

The EXPLAIN modifier that precedes the request:

EXPLAIN SELECT Name, DeptNo FROM Employee WHERE EmpNo = 10009;

generates the response:


1) First, we do a single-AMP RETRIEVE step from

Personnel.Employee by way of the unique primary index "PERSONNEL.Employee.EmpNo = 10009" with no residual conditions. The estimated time for this step is 0.04 seconds.

-> The row is sent directly back to the user as the result of statement 1. The total estimated time is 0.04 seconds.

Here, the WHERE condition is based on a column that is defined as a non-unique index. Note that the Teradata RDBMS places a READ lock on the table. The EXPLAIN modifier that precedes the request:

EXPLAIN SELECT EmpNo, DeptNo FROM Employee WHERE Name = 'Smith T';
Previous << 1 .. 187 188 189 190 191 192 < 193 > 194 195 196 197 198 199 .. 241 >> Next