Hi
I am new to DB2 and am wondering is it possible to drop a column from a DB2 table...and if so will the table require a reorg etc after the drop.

Thanks

That requires an unload, drop the table and recreate it, and load. You may add a column (with some restrictions), but not drop a column.

better way...and this is what is usually done.....

create a new table A similar to the existing table B, however without the dropped column.
Load the new table A with data from existing table B.
Drop the existing table B.
Rename the table A as B.

But then you'll also have to consider which column you are dropping and there can be cases where
the column is used as a constraint for other columns
column is used as a foreign key
coulmn is a pk
etc etc

In such cases you'll get some error messages but make sure you are dead sure about what you are doing and the fact that the column is nowhere else going to be used as otherwise inconsistencies will creep in

AS for reorg, you can easily check whether you require one by running runstats.... better if you do it though

Hi Everyone

Thanks for this i will follow the instructions given

Or better yet, tell the application to ignore the column and just keep working as usual. Unless, its a huge column that eats up too much disk space, just leave it alone.

Dave

If the column is a *text* field (char(n) or varchar(n)), you could reduce its size by changing its datatype to VARCHAR(n) [same n as before] and then filling the column with empty text ('').
If you're at version 9, you could then make the column "invisible", i.e., it will no longer show up with "SELECT * FROM tbl".
Alternatively, RENAME the table then create a view with the original table name, pointing to the renamed table, but with just the columns you want to be visible.

In all cases, you will indeed need a REORG and possibly a REBIND of all static SQL that was using the table.

The view approach doesn't need a REORG, though.

It does, if you precede it with the "make column smaller" step, which I was assuming.
(So, my "alternatively" refers just to my second sentence "If ...", not to my first sentence ;-)