Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: increasing column length

    Can I increase the length of a column without affecting the data??

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Only if it is a varchar.

    Andy

  3. #3
    Join Date
    Nov 2007
    Posts
    72
    not sure with which version of db2 ....V8 or V9 you can use altobj stored procedure ...give it a try ...

  4. #4
    Join Date
    Dec 2007
    Posts
    288
    ALTER TABLE KCDWHDUT.T9PST
    ALTER COLUMN PRCES_PGM_NM
    SET DATA TYPE CHAR(50)

    I tried this and it seemed to work and NOT affect the data

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Careful with ALTOBJ: it may not be needed in this case.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by itsonlyme44
    ALTER TABLE KCDWHDUT.T9PST
    ALTER COLUMN PRCES_PGM_NM
    SET DATA TYPE CHAR(50)

    I tried this and it seemed to work and NOT affect the data
    What do you mean with "not affect the data"? Did you verify that the results being returned when you query the table will have more spaces padded to the end? If that's the case, everything is fine.

    Additionally, you may want to run a REORG on the table.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by itsonlyme44
    Can I increase the length of a column without affecting the data??
    Code:
    ALTER TABLE ALTER COLUMN colname SET DATATYPE VARCHAR(1000)
    (At least, when you are on DB2 v8 for z/OS or later; not sure whether this is possible on other platforms.)

    Suppose the original datatype is CHAR(100); the data will be preserved, that is,
    all varchar fields will have a length of 100 (i.e., possibly with blanks at the end), but after the ALTER, updated or inserted fields may be of lengths between 0 and 1000. (Or chose anything else than 1000.)
    Similarly, a CHAR(100) or a VARCHAR(100) may be altered into a CHAR(1000); the new length must be at least the old length.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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