Need to reorganize tables into different table spaces based on the size of the tables. As far as I am aware, you need to drop and recreate the table in the new table space in DB2 UDB. You can however, alter the buffer pool allocated to the table space. DB2 UDB does not provide a ALTER TABLE command to move a table to another table space like Oracle.

Pls correct me if I am wrong.

TIA.

You are correct. You cannot alter a table to define a new tablespace location. But you do the following:

Create a new table like the old one (CREATE LIKE) and specify the new tablespace location. Use the same schema name, but a different table name.

Move the data with a INSERT INTO new-table SELECT * FROM old-table

Rename the old table to something different.

Rename the new table to the name of the old table.

Create any Primary and foreign keys as exist in the old table. Create any other indexes that exist on the old table.

Drop the old table.

Check out the SQL Reference Vol 2 manual for exact syntax.

How would we copy the constaints the old table had?

By using db2look to extract their definition, then running the resulting script.

If you happen to be on DB2 9.7, you can use ADMIN_MOVE_TABLE procedure.

HTH

Sathyaram

Not really. It does not move referential integrity constraints, as far as I know.

Sorry, generating the DDL and using it on the new table doesn't allow me to remove the old one. the constraints are still there

To remove the old constraints, you would need to drop them via an ALTER TABLE statement.

To recap, if the objective is to move a table from one table space to another, you would need to do the following (one of the methods):

Create a new table like the old one (CREATE LIKE) and specify the new tablespace location. Use the same schema name, but a different table name.

Move the data with a INSERT INTO new-table SELECT * FROM old-table

Rename the old table to something different.

Rename the new table to the name of the old table.

Create any Primary and foreign keys as exist in the old table. Create any other indexes that exist on the old table. db2look can be used for this.

Drop the old table.

Hope this helps.