Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2007
    Posts
    2

    Unanswered: Re-name a column

    Hi,
    Can you pl tell me the syntax to rename a column. I have tried using - ALTER TABLE COMPANY CHANGE "ELITE ID" ELITE_ID VARCHAR (255);-
    this but it didn't work.
    Also you can tell how to drop a column froma table. i used - ALTER TABLE COMPANY drop "ELITE ID" ;- this .
    Thanks

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    try:

    ALTER TABLE tablename RENAME COLUMN oldname TO newname

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You care to tell us which version of DB2 you are using and on which platform? Then we can provide you a link to the manuals where the syntax is described and explained.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jun 2007
    Posts
    197

    Smile

    If you are using DB2 7.1 or DB2 8.1 thenb there is no query to rename column
    only you have to drop table

    but if you are using DB2 9.1 then you can rename column

  5. #5
    Join Date
    Jul 2007
    Posts
    2

    Re-name Column

    I am using DB 8. Is there any way to drop that column.

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    in v8 you can:
    1. export table ... select only columns you need
    2. drop table
    3. import table
    4. create any foreign keys, triggers, etc if exist

    You can also use ALTOBJ stored procedure to do this automatically. But it is not so easy to use it...

    Hope this helps,
    Grofaty

  7. #7
    Join Date
    Jun 2007
    Posts
    197

    Smile

    You can also use ALTOBJ stored procedure to do this automatically. But it is not so easy to use it...

    Hope this helps,
    Grofaty[/QUOTE]


    can you please tell step by step process to alter column through ALTOBJ stored procedure

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Open the DB2 Control Center and use the procedure from there. The GUI makes it much easier to provide the correct parameters for the procedure invocation.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Jun 2006
    Posts
    471
    this is the syntax (see info center for details)
    CALL SYSPROC.ALTOBJ( 'APPLY_STOP_ON_ERROR',
    'CREATE TABLE T1 (CL1 VARCHAR(5), CL2 BIGINT)', -1, ?)
    replace the create table stmnt with the correct stmnt
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    the command is pretty simple, but if something goes wrong it can be pain in the a... to recover to previous state. I had a problem of DB2 dropping some object (foreign keys if I remember correctly) and I need to restore database to test computer to see what was the original sintax.

    Bottom line is ALTOBJ can solve simple problems, but complex problems can lead to more manual work as it would without this procedure.

    So I always prefer export/import functionality...
    Hope this helps,
    Grofaty

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Read Serge's description of ALTOBJ in this link

    http://www.thescripts.com/forum/thread514528.html

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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