Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2008
    Posts
    5

    Red face Unanswered: Drop Column DB2 V8 Z/OS

    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

  2. #2
    Join Date
    Dec 2008
    Posts
    76
    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.

  3. #3
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    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
    IBM Certified Database Associate, DB2 9 for LUW

  4. #4
    Join Date
    Dec 2008
    Posts
    5

    Talking

    Hi Everyone

    Thanks for this i will follow the instructions given

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Bren99
    Is it possible to drop a column from a DB2 table...and if so will the table require a reorg etc after the drop.
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Peter.Vanroose
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

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

Posting Permissions

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