Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: By mistake

  1. #1
    Join Date
    Jun 2007
    Posts
    197

    Smile Unanswered: By mistake

    hi all
    if by mistake we add wrong column in table and we have backup of last week is there any way to drop column from table in db2

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    you can't drop column by command "alter table drop column...".
    But you can export table to ixf format without 'droped' column and then import it back.
    1. export to c:\file.ixf of ixf select col1, col2... from schema.table
    2. drop table schema.table
    3. import from c:\file.ixf of ixf create into schema.table

    But you must be careful - dropping a table results in lost of foreign keys, triggers etc if exist on table.

    Hope this helps,
    Grofaty

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    v9 allows drop column
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    rahul_s80,
    can you please write a sintax.
    Thanks,
    Grofaty

  5. #5
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Example 15: Assume that you have a table named SALARY_DATA that is defined with the following columns:

    Column Name Data Type
    ----------- ---------
    EMP_NAME VARCHAR(50) NOT NULL
    EMP_ID SMALLINT NOT NULL
    EMP_POSITION VARCHAR(100) NOT NULL
    SALARY DECIMAL(5,2)
    PROMOTION_DATE DATE NOT NULL
    Change this table to allow salaries to be stored in a DECIMAL(6,2) column, make PROMOTION_DATE an optional field that can be set to the null value, and remove the EMP_POSITION column.

    ALTER TABLE SALARY_DATA
    ALTER COLUMN SALARY SET DATA TYPE DECIMAL(6,2)
    ALTER COLUMN PROMOTION_DATE DROP NOT NULL
    DROP COLUMN EMP_POSITION
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  6. #6
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Syntax is obvious in V9:
    Code:
    ALTER TABLE <table-name> DROP COLUMN <column-name>
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by grofaty
    Hi,
    you can't drop column by command "alter table drop column...".
    But you can export table to ixf format without 'droped' column and then import it back.
    1. export to c:\file.ixf of ixf select col1, col2... from schema.table
    2. drop table schema.table
    3. import from c:\file.ixf of ixf create into schema.table

    But you must be careful - dropping a table results in lost of foreign keys, triggers etc if exist on table.

    Hope this helps,
    Grofaty


    so according to you

    suppsose if we have a table ISMPCS
    and col1,col2,col3,col4
    and we want to drop col4
    then first we export the table ISMPCS in ixf format with col1,col2,col3
    then drop previous table after that import new table

    so what is the syntax to import table ISMPCS with col1,col2,col3

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    ankur02018, first of all check what is your DB2 database version. Execute command 'db2level' in DB2 Command prompt.

    If it is version 9 then you can use "alter table ISMPCS drop dolumn col4" - I have checked this out now with DB2 v9 on Linux/Intel and it works fine.

    If you DB2 version is older then v9 then you should follow my tips.

    Quote Originally Posted by ankur02018
    so what is the syntax to import table ISMPCS with col1,col2,col3
    You have to specify the columns that you need with export command. For example: "export to c:\file.ixf of ixf select col1, col2, col3 from ISMPCS". Did you noticed there is no col4 in select statement. File.ixf contains only col1, col2 and col3, but no more col4. When you import file.ixf you already don't have col4 created.

    Hope this helps,
    Grofaty
    Last edited by grofaty; 07-10-07 at 04:02.

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you are not on V9, you should use the ALTER_OBJ stored procedure provided by DB2. That will help you with a lot of the issues associated with dropping a column.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Jan 2003
    Posts
    1,605
    stolze,
    I have been using ALTER_OBJ stored procedure, but this procedure can make quite a mess if you don't 100% know what are you doing.

    BTW, procedure exports data, change definitions and import data back.

    Also DB2 v8 fp11 has a bug where ALTER_OBJ stored procedure does not work. I know this for sure, because this is the database level in over production database.

    According to the type of the question ankur02018 has asked, I thing ALTER_OBJ will be to complicated to use.

    If someone decides to study ALTER_OBJ stored procedure I recommend the following links:
    http://groups.google.co.uk/group/com...3dea192?&hl=en
    http://publib.boulder.ibm.com/infoce...n/r0011934.htm

    Hope this helps,
    Grofaty

  12. #12
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    easiest of all
    use db2cc GUI , it will allow you to drop column in a table by calling alter_obj
    n doing all exports,imports, rename at the back of it
    just click and it will do all for you
    DBA's paradise :-)
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Not quite "DBA's paradise" - V9 is much closer than the ALTER_OBJ work-around.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  14. #14
    Join Date
    Jul 2007
    Posts
    4
    Sorry guys..But can some expert please help me on my thread of SQL1272N ..this has halted our system and needs to start it ASAP...
    Sorry once again...please help.

  15. #15
    Join Date
    Jun 2007
    Posts
    197

    Smile

    here is simplest way


    first export in .csv file

    then drop column and remake required script

    run the script

    and in last import .csv file into the table

Posting Permissions

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