Way to decrease column length in DB2

sql database db2
Vicky picture Vicky · Mar 6, 2012 · Viewed 52.8k times · Source

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

Answer

Ian Bjorhovde picture Ian Bjorhovde · Mar 6, 2012

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.

Related questions

How to update multiple columns in single update statement in DB2

I want to update multiple columns of a table in DB2 with single Update statement. Any hint or idea will be appreciable. Thanks.

Read More
Calculating how many days are between two dates in DB2?

I need to get the difference in days between two dates in DB2. I tried a couple of different queries but nothing seems to be working. So basically what i need to get is something like this. SELECT DAYS (CURRENT …

Read More
Does DB2 have an "insert or update" statement?

From my code (Java) I want to ensure that a row exists in the database (DB2) after my code is executed. My code now does a select and if no result is returned it does an insert. I really don't …

Read More