Modifying Schema, View, Table, and Routine Definitions › Dropping and Recreating a Table

Dropping and Recreating a Table

Considerations for Dropping/Adding a Table

Many types of changes can only be implemented by dropping and redefining a table. There are two major considerations involved with this process:

• Preserving the table's data
• Re-establishing the table's relationships with other tables and views

This section outlines two approaches that can be used to drop and recreate a table:

• Method 1—Uses a combination of DDL and DML statements to perform the operation
• Method 2—Uses DDL and utility statements

Considerations

Select the approach based on the size of the table and the importance of minimizing the time during which the table cannot be accessed. Consider the following:

• Method 1 requires there be enough space in the database to hold two copies of the data simultaneously. It also builds indexes and validates relationships as the data is being inserted into a new table, potentially requiring a large number of row locks and journal images.
• Method 2 reloads the data in local mode using the LOAD utility statement. Therefore, the table and all other tables in the same area cannot be accessed while the load is taking place.

For these reasons, Method 1 is more appropriate for small tables, while Method 2 is more suited for large tables.