Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1

    Unanswered: alter table with different length in field.

    Hi Cooleagues,

    I have DB2 V8 on AIX 5.3, this table prueba.

    id integer
    nombre char(20)
    dir1 varchar(40)
    dir2 varchar(40)
    I need to change the dir1 field to varchar(100).
    Somebody can say me how to do this change in table?

    Thank you for advanced.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Alter Table Your-Table-Name alter column dir1 set data type varchar(100) ;

    Then check if that table has any dependents that became invalidate as a result of this change (views, MQT, stored procedures etc). You might need to revalidate them.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    That information is contained in the ALTER TABLE command. Please consult the V8 documentation.
    Last edited by Marcus_A; 04-27-12 at 11:56.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Aug 2011
    Posts
    45
    You cannot change this directly in version 8.
    you have to drop the table and recreate it with new ddl having new length of field.

    Regards,
    harsh

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by harsh.v View Post
    You cannot change this directly in version 8.
    you have to drop the table and recreate it with new ddl having new length of field.

    Regards,
    harsh
    That is not correct. A VARCHAR column can be increased in size in 8.2 (maybe 8.1 also, not sure).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow

    Quote Originally Posted by Marcus_A View Post
    That is not correct. A VARCHAR column can be increased in size in 8.2 (maybe 8.1 also, not sure).
    In any version of DB2 we can alter the column to column with the same name and bigger size if we do not change type of the column.

    From V9 we can change CHAR to VARCHAR and VARCHAR to CHAR.

    Lenny

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Lenny77 View Post
    In any version of DB2 we can alter the column to column with the same name and bigger size if we do not change type of the column.

    From V9 we can change CHAR to VARCHAR and VARCHAR to CHAR.

    Lenny
    In version 8.2 (maybe earlier) one can increase the length of VARCHAR column without any outage or any reorg required.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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