Re-creating a table

You can re-create a Db2 table to decrease the length attribute of a string column or the precision of a numeric column.

To re-create a Db2 table:

1. If you do not have the original CREATE TABLE statement and all authorization statements for the table (for example, call the table T1), query the catalog to determine its description, the description of all indexes and views on it, and all users with privileges on it.
2. Create a new table (for example, call the table T2) with the attributes that you want.
3. Copy the data from the old table T1 into the new table T2 by using one of the following methods:
   1. Issue the following INSERT statement:

      

      INSERT INTO T2
        SELECT * FROM T1;

      

   2. Load data from your old table into the new table by using the INCURSOR option of the LOAD utility. This option uses the Db2 UDB family cross-loader function.
4. Issue the statement DROP TABLE T1. If T1 is the only table in an explicitly created table space, and you do not mind losing the compression dictionary, if one exists, you can drop the table space instead. By dropping the table space, the space is reclaimed.
5. Commit the DROP statement.
6. Use the statement RENAME TABLE to rename table T2 to T1.
7. Run the REORG utility on the table space that contains table T1.
8. Notify users to re-create any synonyms, indexes, views, and authorizations they had on T1.

What to do next

If you want to change a data type from string to numeric or from numeric to string (for example, INTEGER to CHAR or CHAR to INTEGER), use the CHAR and DECIMAL scalar functions in the SELECT statement to do the conversion. Another alternative is to use the following method:

1. Use UNLOAD or REORG UNLOAD EXTERNAL (if the data to unload in less than 32 KB) to save the data in a sequential file, and then
2. Use the LOAD utility to repopulate the table after re-creating it. When you reload the table, make sure you edit the LOAD statement to match the new column definition.

This method is particularly appealing when you are trying to re-create a large table.