Results 1 to 8 of 8

Thread: delete a column

  1. #1
    Join Date
    Dec 2007
    Posts
    7

    Unanswered: delete a column

    is it possible to delete a column of a table! if so how can i?

    thanx

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    At least in DB2 for z/os there is no SQL-statement to drop a column from a table. You have to DROP and reCREATE it

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Code:
    ALTER TABLE ... DROP COLUMN ...
    Whether this works depends on your DB2 version and underlying platform.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It works on DB2 V9 for UNIX, Linux, Windows. If you don't have version 9, you have to drop the table and recreate it without the column. If that is not possible, the just ignore the column since DB2 (or Oracle) doesn't actually physically remove it from each row when you drop a column (at least until you do a reorg).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Dec 2007
    Posts
    7
    im using z/os so can't i even do it with the alter command?
    i suppose i can create a new column with the alter though? isn't it?

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    ALTER TABLE (it's a SQL statement - not a command) on DB2 z/OS does not allow you to drop a column. Adding a column is supported, however.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ravi30
    is it possible to delete a column of a table! if so how can i?
    Superfluous columns in a table are normally not a problem for DML access.
    If it's important to "hide" a particular column, you may RENAME the original table and CREATE a VIEW with the old table name, referring the new table name, where that view has one column less than 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/

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    is there SYSPROC.ALTOBJ system store procedure on DB2/zOS? This store procedure exist from v8 on LUW and can be helpful.
    Hope this helps,
    Grofaty

Posting Permissions

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