Dropping Columns !

DB2 V9.x introduced one of the long awaited feature that other RDBMS could easily do…the DROP COLUMN . Since DB2 V9.1 , db2 is able to drop a column from a table without the dba have to drop and recreate the table.  The traditional method of , unloading the data,  dropping and recreating the table, loading the data, recreating the constraints and objects is not necessary.  You can now issue ALTER TABLE… DROP COLUMN … to remove a column. If you have depending objects, db2 is also able to take care of it using the CASCADE option.   Now is there a catch !  Yes..there is…db2 recommends a reorg of the table after dropping a column and after  three drop columns on a table, the table goes into DRP state and any operations on the table gets a SQLCODE 2005N RC 23 .  This means all the three strikes have been taken. 

In my case today,  I had to drop 6 columns, so I had to do two reorgs, if you have many columns to drop, it might be better to take the older route, but if its couple of columns and a small table that can take a small downtime, then drop column route is easier.  Here is what I did :

ALTER TABLE  TECH.SITE  DROP COLUMN COSTOMER_NO CASCADE;
ALTER TABLE  TECH.SITE  DROP COLUMN COSTOMER_NAME CASCADE;
ALTER TABLE  TECH.SITE  DROP COLUMN COSTOMER_PHNO CASCADE;
REORG TABLE TECH.SITE;
ALTER TABLE  TECH.SITE  DROP COLUMN COSTOMER_FAXNO CASCADE;
ALTER TABLE  TECH.SITE  DROP COLUMN COSTOMER_CELLNO CASCADE;
ALTER TABLE  TECH.SITE  DROP COLUMN COSTOMER_EMAIL CASCADE;
REORG TABLE TECH.SITE;