Alex Rivera | Logout

Way to decrease column length in DB2

Asked 2012-03-06T06:40:52.343
19

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).

sql database db2
Edit
Report

1 Answer

33

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.

answered 2012-03-06T17:24:14.067

Your Answer