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

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 23 24 25 26 27 28 < 29 > 30 31 32 33 34 35 .. 218 >> Next

add or drop nonpartitioning columns Note: Be careful the change does not affect normalization of the database or any views, macros, indexes, procedures, or applications that reference a dropped column. ALTER TABLE
change the default format or title attribute of one or more columns

2 - 10 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS


IF you want to . THEN use .
rename nonpartitioning columns Note: Synchronize this change with any views, macros, indexes, procedures, or applications that reference these columns. ALTER TABLE
change the data type or nullability of one or more nonpartitioning columns Note: If you use any of the changed columns in joins, the columns of the other tables must agree in data type and notation.
add or remove FALLBACK protection
change the size of the table DATABLOCKSIZE
change the cylinder FREESPACE percent
add, change, or drop column-level or table-level constraints
add, drop, or change a reference or check constraint
add or drop a partition or partitioning range
change the LOG and ON COMMIT options for a global temporary table Note: Altering a global temporary table is not allowed while materialized instances of the table exist.
create, change, or drop a Permanent Journal (PJ) table Note: If data tables refer to a dropped journal table at dump time, the archive cannot be used. For a database or user: CREATE USER, CREATE DATABASE, MODIFY USER, or MODIFY DATABASE For changing the journaling of the referencing data tables: ALTER TABLE
add or drop one or more SIs on the table CREATE INDEX or DROP INDEX
add or drop a JI on the table CREATE JOIN INDEX or DROP JOIN INDEX
add or drop a HI on the table CREATE HASH INDEX or DROP HASH INDEX

2 - 10

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS


Recreating a Table

You need to recreate a data table in order to:

Change a default PI to a defined PI. |

Change a NUPI to a UPI in a populated table when there is no USI.

Redefine the partitioning on thePPI of a populated table. (In some cases, you can still use ALTER TABLE; see "Creating and Modifying Indexes" on page 2-40)

Change a data type attribute that affects existing data. (For rules on changing data types, see "ALTER TABLE" in Teradata RDBMS SQL Reference, Volume 4.)

Define or delete COMPRESS storage attributes for an existing column. |

Add a column that would exceed the maximum for the number of columns defined during the life of a table.

Use the SHOW TABLE statement to display the current table definition, which you can then modify. Be sure to define a different name, such as "NewTable".

An INSERT.. .SELECT statement is a quick way to load the current data rows into the new table.

However, the data type of every column in the new table must be compatible with the value of the corresponding field in the existing table. If incompatibilities exist, you can use separate INSERTs for each row.

The procedure for recreating a table (named Personnel.Employee for the purposes of the examples) is as follows:

Step Action
1 Catalog the access rights of the old table with the following query: SELECT username, accessright, grantauthority, columnname , allnessflag FROM dbc.allrights WHERE tablename = 'Employee' AND databasename = 'Personnel'; Note: Save the output for later use; you will need to recreate the access rights on the new table.
2 Create a new table with a different, obviously temporary name, such as Temp, with the changed definitions. To display the DDL for the current table, submit a SHOW TABLE. If you use BTEQ, you can change the text of the display using the BTEQ edit commands, then submit the new table definition using the BTEQ SUBMIT command.

2 - 10 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS




If any data types are not compatible, use an INSERT statement for each row.

If the data types are compatible, you can transfer all data rows from the old table to the new table with a single INSERT ... SELECT statement; for example:



FROM employee ;

Use DROP JOIN INDEX and DROP HASH INDEX to remove any join and hash indexes defined on the old table. You can use SHOW INDEX index_name to modify, if needed, and then save the definition of each index.

Drop the old employee table: DROP TABLE employee ;

Note: When the table is dropped, any explicit access rights are also dropped, because the Data Dictionary references objects by ID rather than by name.

Rename the temporary table: RENAME TABLE temp TO employee



Use the index definitions from step 4 to recreate any join and hash indexes you want to maintain for the new table.

Submit GRANT statements to re-establish the access rights you cataloged and saved in step 3 on the new version of Employee table.





Planning Tables

Previous << 1 .. 23 24 25 26 27 28 < 29 > 30 31 32 33 34 35 .. 218 >> Next