Changing data types by dropping and re-creating the table

Some changes to a table cannot be made with the ALTER TABLE statement.

About this task

For example, you must make the following changes by redefining the column (that is, dropping the table and then re-creating the table with the new definitions):
  • An original specification of CHAR (25) to CHAR (20)
  • A column defined as INTEGER to SMALLINT
  • A column defined as NOT NULL to allow null values
  • The data type of an identity column

Procedure

To change data types:

  1. Unload the table.
  2. Drop the table.
    Attention: Be very careful about dropping a table. In most cases, recovering a dropped table is nearly impossible. If you decide to drop a table, remember that such changes might invalidate a package.

    You must alter tables that have been created with RESTRICT ON DROP to remove the restriction before you can drop them.

  3. Commit the changes.
  4. Re-create the table.

    Begin general-use programming interface information.If the table has an identity column:

    • Choose carefully the new value for the START WITH attribute of the identity column in the CREATE TABLE statement if you want the first generated value for the identity column of the new table to resume the sequence after the last generated value for the table that was saved by the unload in step 1.
    • Define the identity column as GENERATED BY DEFAULT so that the previously generated identity values can be reloaded into the new table. End general-use programming interface information.
  5. Reload the table.