Hello, I have DB2 V8 FP11 database table on AIX 5.2 which contains around 120 million rows. I need to drop two columns in the table..what is the best way to accomplish this?

Paul

Use the SYSPROC.ALTOBJ stored procedure. Look it up in the SQL Reference. If you use the Control Center to "Drop" the columns, it will call the ALTOBJ Stored procedure for you.

Andy

Right, but as there are 120Million rows it will take a long time to accomplish the whole task. Or does anyone knows that it will not require long time for such a number of rows?

Thanks

In that case it's probably simpler (or at least faster) to do a REORG (or actually an UNLOAD followed by a DROP TABLE + CREATE TABLE, followed by a LOAD), but you loose the automatic restore of everything referring the table, like indexes, views, foreign keys in other tables, etc., which you will have to restore manually.

But maybe SYSPROC.ALTOBJ is already doing just that? In that case there will not be anything faster.

Yes, sysproc.altobj does all that work for you behind the covers. All indexes and foreign keys (except those involving the dropped column) are
reinstated. My suggestion would be to have a copy of the production system and do a trial run on that to see how long it would take.

Andy