Menu
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).
727
In generic terms, you use the ALTER TABLE command followed by the table name, then the MODIFY command followed by the column name and new type and size. Here is an example: ALTER TABLE tablename MODIFY columnname VARCHAR(20) ; The maximum width of the column is determined by the number in parentheses.
it is not possible to decrease the size of column. same table it is not possible.
In this case, you need to use ALTER TABLE statement to increase column size. ALTER TABLE table_name MODIFY column_name varchar(new_length); In the above command, you need to specify table_name whose column you want to modify, column_name of column whose length you want to change, and new_length, new size number.
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.
198
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
