Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2009
    Posts
    14

    Question Unanswered: Expanding a Column in a table

    Version 8.2 on Linux.
    Background: I have not used db2 since 7.1 and earlier.
    Question: Can DB2 now allow you to expand a column in place, if not which is a smoother transition, adding a new column and moving the data to the new column and then renaming the column and dropping the old column. Or creating a new tablewith the expanded column and importing the data, then re-naming the table.

  2. #2
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Depends what data type and if you have any RI or other ref linked to it.

    If you decide to go with new object. then:
    1. create new table.
    2. load from cursor from old to new.
    3. rename old.
    4. rename new.
    5. test
    6. drop old
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If by expand, you mean make larger, like increasing the size of a varchar, then you can do that kind of thing in place with just the ALTER TABLE command.

    Andy

  4. #4
    Join Date
    Jun 2009
    Posts
    14

    Question More information

    More information. After digging around and drawing back db2 commands with cob webs attached. The table is actually only has two columns with no primary key or indexes. both columns are char(25) and both nullable. The column that I need to expand is the last column in the table.

    old: column name CHAR(25)
    new column name CHAR(50).

    This can be complete by altering the column in place.

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Yes. have you looked at the SQL Ref 2 manual as Andy suggested?

    alter table tabschema.tabname alter column col2 set DATA TYPE char(4)"
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Sep 2003
    Posts
    237
    You can alter only VARCHAR columns; the record size does not change right away; but you CANNOT alter CHAR columns because then each record has to change causing all kinds of havoc.
    mota

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by dbamota
    You can alter only VARCHAR columns; the record size does not change right away; but you CANNOT alter CHAR columns because then each record has to change causing all kinds of havoc.
    Strange, because I did just that yesterday.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Cougar,
    The OP is on V 8.2 and the only thing you could alter was varchar. It has changed in V9 and beyond.

    Andy

  9. #9
    Join Date
    Sep 2003
    Posts
    237
    How many records were there in the table? Was there an index?how long did it take to make the change?
    mota

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by ARWinner
    Cougar,
    The OP is on V 8.2 and the only thing you could alter was varchar. It has changed in V9 and beyond.

    Andy
    I guess I missed db2 level. Thank you.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by dbamota
    How many records were there in the table? Was there an index?how long did it take to make the change?
    Does it really matter in this case? Yes, there considerations that have to be taken into account.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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