Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jan 2003
    Posts
    7

    Unanswered: dropping a column in DB2

    hi!
    i was searching for informations about how to drop a column in db2, and this is the only solution I've got:

    <!--
    ALTER TABLE <old_name> RENAME TO <another_name>

    Then recreate the old table without the deleted column(s):
    SELECT col1, col2, col4 INTO TABLE <old_name> FROM <another_name>

    Then delete the old table:
    DROP TABLE <another_name>
    -->

    Is this true? Is this the best approach?
    And why doesn't DB2 support a statement like "ALTER TABLE xpto DROP COLUMN xpto_column"?

    Regards,
    Giovanni Giazzon

    ps.: no war.

  2. #2
    Join Date
    Aug 2002
    Posts
    24

    Re: dropping a column in DB2

    For your first question.....the answer 'You are doing right'. We dont have any procedure to DROP a particular column. For more information, refer RDBMS basic rules.

    For your second question, you need to contact IBM.

    Thanks
    Laca

  3. #3
    Join Date
    Mar 2003
    Posts
    35

    Re: dropping a column in DB2

    Originally posted by giazzon
    hi!
    i was searching for informations about how to drop a column in db2, and this is the only solution I've got:

    <!--
    ALTER TABLE <old_name> RENAME TO <another_name>

    Then recreate the old table without the deleted column(s):
    SELECT col1, col2, col4 INTO TABLE <old_name> FROM <another_name>

    Then delete the old table:
    DROP TABLE <another_name>
    -->

    Is this true? Is this the best approach?
    And why doesn't DB2 support a statement like "ALTER TABLE xpto DROP COLUMN xpto_column"?



    Regards,
    Giovanni Giazzon

    Hi Giovanni,
    Yes, first way you are doing is right, but in db2 udb 7.2 we have an option to drop the column through
    Alter table <tbl_name> drop <column_name>
    go through the db2 udb 7.2 manual for performance or sql reference.
    with regards,
    muthu
    ps.: no war.

  4. #4
    Join Date
    Sep 2002
    Posts
    30
    Hi nmkumaran,

    >> Yes, first way you are doing is right, but in db2 udb 7.2 we have an
    >> option to drop the column through
    >> Alter table <tbl_name> drop <column_name>

    I can't find it from the SQL Reference guide on V7.2 and V8.1. Does it document in the Readme file of the fixpak?

    Regards,

    Patrick

  5. #5
    Join Date
    Jan 2003
    Posts
    7
    Thanks for all your support.
    Muthu, I'll be looking for this statement (alter table <table> drop <column>) in the UDB reference. But It didn't work through a SQL query tool.

    regards,
    Giovanni

  6. #6
    Join Date
    Mar 2003
    Posts
    35

    Thumbs up It works in AS/400 db2

    Hi patrick,
    Here is the URL. This URL gives the exact syntax for alter table and how to drop the column.
    http://publib.boulder.ibm.com/cgi-bi....4.3?SHELF=&DT
    Check this one.
    thanks,
    muthu


    Originally posted by wongpcl
    Hi nmkumaran,

    >> Yes, first way you are doing is right, but in db2 udb 7.2 we have an
    >> option to drop the column through
    >> Alter table <tbl_name> drop <column_name>

    I can't find it from the SQL Reference guide on V7.2 and V8.1. Does it document in the Readme file of the fixpak?

    Regards,

    Patrick

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605

    Re: It works in AS/400 db2

    Hi,

    Is there any command to drop column in DB2 UDB.

    Thanks,
    Grofaty

    Originally posted by nmkumaran
    Hi patrick,
    Here is the URL. This URL gives the exact syntax for alter table and how to drop the column.
    http://publib.boulder.ibm.com/cgi-bi....4.3?SHELF=&DT
    Check this one.
    thanks,
    muthu

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605

    Re: It works in AS/400 db2

    Hi,

    Is there any command to drop column in DB2 UDB.

    Thanks,
    Grofaty

    Originally posted by nmkumaran
    Hi patrick,
    Here is the URL. This URL gives the exact syntax for alter table and how to drop the column.
    http://publib.boulder.ibm.com/cgi-bi....4.3?SHELF=&DT
    Check this one.
    thanks,
    muthu

  9. #9
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    AFAIK, the only platform that supports "DROP COLUMN" is AIX. For DB2 UDB Linux, Unix, OS/2, Windows and OS/390 you CANNOT drop a column.
    The reference from muthu is the " DB2 for AS/400 SQL Reference V4R3".

    HTH.
    Rodney Krick

  10. #10
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78
    Im running DB2 UDB V7.2 on AIX 4.3.3

    I tried many ways to do the 'ALTER TABLE .. DROP COLUMN..' but it always return an error saying that the 'COLUMN' is an unexpected token and suggest you to replace it by 'CONSTRAINT'.

    Am I doing anything wrong?
    I have found naught about DROP COLUMN in the manuals.
    Thus, I think it is for AS/400 platform only.
    Correct me if Im mistaken.

    Fernando

    Originally posted by RKrick
    AFAIK, the only platform that supports "DROP COLUMN" is AIX. For DB2 UDB Linux, Unix, OS/2, Windows and OS/390 you CANNOT drop a column.
    The reference from muthu is the " DB2 for AS/400 SQL Reference V4R3".

    HTH.

  11. #11
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    Fernando,

    I'm not running AIX, so I cannot test it. On the IBM-newsgroup I've found the following
    <quote>
    DB2 UDB for iSeries supports

    ALTER TABLE SOME_TABLE DROP COLUMN SOME_COLUMN CASCADE

    </quote>
    news://news.software.ibm.com:119/ake...oulder.ibm.com
    Rodney Krick

  12. #12
    Join Date
    Mar 2009
    Posts
    2

    Dropping a column

    Suppose you have a table Tab1(Col1, Col2, Col3) and you want to delete the column Col2 from it, follow the steps:

    1) Create a new table Tab2(Col1, Col3)

    2) Copy Tab1 to Tab2

    Insert into table Tab2
    (Select Col1,Col3 from Tab1)

    3) Drop table Tab1

    4) Create the table Tab1(Col1,Col3)

    5) Copy Tab2 to Tab1
    insert into table Tab1
    (Select * from Tab2)

    6) Drop table Tab2

  13. #13
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Bibek,

    Are you for real? You have found a thread that is 6 yrs old and then on top of it giving a solution that should get you fired.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  14. #14
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you want to drop a column, you do it the standard way:
    Code:
    ALTER TABLE ... DROP COLUMN ...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  15. #15
    Join Date
    Nov 2009
    Posts
    2

    Error Code -668 after ALTER

    After issueing the DROP COLUMN command, the table seems to be in a corrupt state and will not accept additional DDL. Here is a simple reproduction of the issue. We are using JDBC to create some database objects for our product.

    CREATE TABLE THETABLE (
    COLUMN1 CHAR(38) NOT NULL,
    COLUMN2 VARCHAR(255))

    ALTER TABLE THETABLE DROP COLUMN COLUMN2

    --This command fails
    CREATE INDEX THETABLE_IDX on THETABLE (COLUMN1)
    [Error Code: -668, SQL State: 57016] Operation not allowed for reason code "7" on table "THETABLE".
    What fixes this??

    I thought to try REORG TABLE but two problems:

    1> Appears to requires SYSADM permission, so our application cannot automate this simple modification.
    2> Doesn't seem to work over JDBC.

    REORG TABLE THETABLE;
    [Error Code: -104, SQL State: 42601] An unexpected token "TABLE" was found following "REORG ". Expected tokens may include: "JOIN <joined_table>".
    Any comments would be appreciated.
    Last edited by Bachio; 11-04-09 at 20:15.

Posting Permissions

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