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 Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 45 46 47 48 49 50 < 51 > 52 53 54 55 56 57 .. 218 >> Next


If NO CYCLE is in effect, reaching the MAXVALUE limit (or MINVALUE limit if INCREMENT is negative) results in a failure message and the job is aborted. To prevent this, make sure the limit can accommodate all the rows in the current bulk load job.

After an identity-column table is populated, deleting all its rows and reinserting new rows will not cause the numbering to restart from START; numbering continues from the last number generated for that table. To renumber the rows from the START value, instead of deleting rows do this:

2 - 10 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Populating Tables

Step Action
1 Drop the table.
2 Recreate it as an empty table.
3 Load the rows into the recreated table.

After a restart or abort, the numbering sequence may have gaps because:

Assigned numbers that were rolled back after an abort are not generated again for the same rows when the job is resubmitted.

Unassigned numbers in the cache are lost.

To fill the gaps, supply numbers with GENERATED BY DEFAULT. To fill the gaps and omit duplicate values, make the identity column the UPI for the table.

Excluding Duplicates

To ensure uniqueness of both identify values and rows, you can protect your data from recycled numbers, restart replications, or exceeded limits, as follows:

IF you want to ensure ...

THEN use .

unique identity values

CREATE TABLE options for:

A LIMIT definition that will not be exceeded during one job

NO CYCLE

IF . THEN define the identity column .
gaps are acceptable with GENERATED ALWAYS
you need to fill gaps as either: GENERATED BY DEFAULT One of the following: - The UPI for the table - UNIQUE

unique data rows

TPump in ROBUST mode.

For more details on how and why duplicates can occur, see "Identity Columns, Duplicate Column Values, and Duplicate Rows" in Teradata RDBMS SQL Reference, Volume 4.

2 - 10

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Populating Tables

IF you want to ensure . THEN use .
that the rows of a copied table exactly replicate the original, including the same identity values GENERATED BY DEFAULT to define the identity column INSERT.. .SELECT *... to copy the contents of the old table into the new table

Bulk Loading Identity Column Tables

The following kinds of bulk loading are supported:

Single-statement INSERT requests through multiple concurrent session (for example, BTEQ IMPORTs into the same table)

Multi-statement INSERT requests through multiple concurrent sessions (for example, TPump (ROBUST mode is recommended))

MultiLoad and FastLoad do not support identity column tables.

Caution: A SELECT on DBC.IdCol within a transaction might hold up bulk inserts until the entire transaction completes, because DBC.IdCol must be accessed for UPDATE each time the pool of numbers reserved for bulk inserts is exhausted.

For details pertinent to using each utility for loading identity column tables, see Appendix B: "Import/Export Utilities".

Managing Identity Column Tables

The following table summarizes functions you may need to perform on target tables with identity columns.

Function Description
CREATE TABLE . (column_name AS IDENTITY .) The identity attribute may be applied to any column in a table apart from an existing single-column PI. However, you can define the identity column itself as the single-column PI for the table. When you define the IDENTITY attribute, you specify: Whether values are to be: - Always generated by the system. Rows bulk-loaded with GENERATED ALWAYS are guaranteed to have system-generated numbers. The uniqueness of identity values is assured, but you cannot fill any gaps. (See "Bulk Loading Identity Column Tables" on page 2-69.) - Not cycled (default). If numbering exceeds the increment limit, a failure message is generated and the job is aborted. The increment for each row value. (You define the batch size of the pool with IdColBatchSize in the DBS Control GDO) For complete details on the use of each clause, see "CREATE TABLE (Column Definition Clause)" in Teradata RDBSM SQL Reference, Volume 4.

2 - 10 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Populating Tables

Function Description
ALTER TABLE You can alter a table to: Drop the IDENTITY attribute and retain the column You cannot alter a table to: Drop the identity column of a table Add an identity column to an existing table Add the IDENTITY attribute to an existing column For more information, see "Column and Table Constrains" under "SQL Data Definition" in Teradata RDBMS SQL Reference, Volume 3.
Atomic UPSERT Atomic UPSERTs can be performed on a table with a non-PI identity column. Atomic UPSERTs are not possible on a table with an identity-column PI, because the PI values for the UPDATE and the INSERT statements must be known and must be the same so that both statements can be processed on the same AMP. When the PI of the target table is an identity column, the value of the number generated for the INSERT is not known.
Previous << 1 .. 45 46 47 48 49 50 < 51 > 52 53 54 55 56 57 .. 218 >> Next