18

Is there a way to decrease the column length in DB2?

Say I have a table temp with column col1 defined as VARCHAR(80). I want to reduce it to VARCHAR(60).

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Vicky
  • 16,679
  • 54
  • 139
  • 232

3 Answers3

32

In DB2 9.7 for Linux/UNIX/Windows, you can use the ALTER TABLE statement to reduce the length of a column, assuming that no values in the column exceed the new column size:

ALTER TABLE temp
    ALTER COLUMN col1 SET DATA TYPE VARCHAR(60);

If any values in the column exceed the desired size you must handle that first.

In previous versions of DB2 for Linux/UNIX/Windows, you could not utilize this method to reduce the size of the column. You either had to drop/recreate the table, or go through a process of adding a column, copying data, and removing the old column.

Ian Bjorhovde
  • 10,916
  • 1
  • 28
  • 25
3

As an addition to Ian's answer and Clockwork-Muse's remark:

While it is possible, as Ian pointed out, to use ALTER statements to reduce column length in DB for LUW, this is not the case in DB2 for z/OS as of version 10.

According to this table, only data type changes from VARCHAR(n) to VARCHAR(n+x) are supported, which is a bummer.

Community
  • 1
  • 1
GreenhouseVeg
  • 617
  • 5
  • 13
1

You cannot reduce the length of a column. To achieve this affect you should

  • create a new table with your data and with the attribute that you want.
  • Delete old table
  • Rename the new table

If you want to increase the length, it is possible with ALTER command

 ALTER TABLE temp
      ALTER COLUMN col1
      SET DATA TYPE VARCHAR(60)
Kayser
  • 6,544
  • 19
  • 53
  • 86
  • 2
    ... Except you can use the `ALTER TABLE` command to _shorten_ the column as well. Why would you expect that a command that could _increase_ the length wouldn't be usable to _decrease_ it as well? Your syntax is correct, and should be the statement he needs. Although, of course, data may be truncated. – Clockwork-Muse Mar 06 '12 at 17:26