Way to decrease column length in DB2

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.

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)

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.