Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Posts
    456

    Unanswered: Alter table drop column

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Sep 2002
    Posts
    456
    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

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •