Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    24

    Question Unanswered: How to delete a column in the existing table?

    Hi there,


    DB version : DB2 UDB 7.2
    OS : Win 2K Server

    what is the SQL syntax to delete a column in the existing table?
    Hello

  2. #2
    Join Date
    Feb 2005
    Posts
    12
    Well, all you have to do is

    ALTER TABLE table_name DROP COLUMN column_name CASCADE

    CASCADE option will delete depending indexes and constraints. You can specify RESTRICT as well if you dont want this column to be deleted if those indexes or constraints exist.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There is no command in DB2 to drop a column--in any version. You will have to:
    1) export date from table using select statement without column
    2) drop table
    3) create table minus column
    4) import data from step 1
    5) recreate any indexes, RI, PKs

    Andy

  4. #4
    Join Date
    Apr 2004
    Posts
    36
    AR ,
    How do you get the FKeys. Do we have to take db2look.
    is there any other work around to easily identiy the FKs on that table and from that to another table.

    If we are droping a column on more than one table. Its going to be a lot of work.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes, use db2look to generate the DDL for the FKs, indexes and PKs.
    Edit the output as necessary.

    Yes, dropping a column this way can be a lot of work.

    Andy

  6. #6
    Join Date
    Feb 2005
    Posts
    12
    Quote Originally Posted by ARWinner
    There is no command in DB2 to drop a column--in any version.
    And whats up with ALTER TABLE ... DROP COLUMN???

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Sordax,
    There is no such command in DB2 UDB for LUW.
    Andy

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There is no ALTER TABLE ... DROP COLUMN in DB2 for LUW.

    However, in version 8.2 (I am using FP 8) the Control Center has a function to drop a column.

    I believe that it automatically exports the data, drops the table, recreates the table and keys without the column to be dropped, and reloads the data. It uses a stored procedure with multiple input parameters to do this. You could look at the SQL it generates and do this in batch.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Feb 2005
    Posts
    12
    Oh, I don't know about DB2 for LUW, I'm working with DB2 for iSeries.

  10. #10
    Join Date
    Sep 2004
    Posts
    12
    Are you using any Change Management software on your iSeries? If you had something like TurnOver from SoftLanding Systems, then making a change like this would be easy - however, the underlying steps would still be:

    1. Copy the existing data to a temp file
    2. Drop the current table definition
    3. Recreate the new table without the column
    4. Create the indexes
    5. Build any FK / RIs
    6. Copy back in the data saved off in the temp file

    Doing it manually can be risky so make sure you have good backups.

Posting Permissions

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