Hi there,


DB version : DB2 UDB 7.2
OS : Win 2K Server

what is the SQL syntax to delete a column in the existing table?

Well, all you have to do is

ALTER TABLE table_name DROP COLUMN column_name CASCADE

CASCADE option will delete depending indexes and constraints. You can specify RESTRICT as well if you dont want this column to be deleted if those indexes or constraints exist.

There is no command in DB2 to drop a column--in any version. You will have to:
1) export date from table using select statement without column
2) drop table
3) create table minus column
4) import data from step 1
5) recreate any indexes, RI, PKs

Andy

AR ,
How do you get the FKeys. Do we have to take db2look.
is there any other work around to easily identiy the FKs on that table and from that to another table.

If we are droping a column on more than one table. Its going to be a lot of work.

Yes, use db2look to generate the DDL for the FKs, indexes and PKs.
Edit the output as necessary.

Yes, dropping a column this way can be a lot of work.

Andy

And whats up with ALTER TABLE ... DROP COLUMN???

Sordax,
There is no such command in DB2 UDB for LUW.
Andy

There is no ALTER TABLE ... DROP COLUMN in DB2 for LUW.

However, in version 8.2 (I am using FP 8) the Control Center has a function to drop a column.

I believe that it automatically exports the data, drops the table, recreates the table and keys without the column to be dropped, and reloads the data. It uses a stored procedure with multiple input parameters to do this. You could look at the SQL it generates and do this in batch.

Oh, I don't know about DB2 for LUW, I'm working with DB2 for iSeries.

Are you using any Change Management software on your iSeries? If you had something like TurnOver from SoftLanding Systems, then making a change like this would be easy - however, the underlying steps would still be:

1. Copy the existing data to a temp file
2. Drop the current table definition
3. Recreate the new table without the column
4. Create the indexes
5. Build any FK / RIs
6. Copy back in the data saved off in the temp file

Doing it manually can be risky so make sure you have good backups.