DB2 10.5 for Linux, UNIX, and Windows

Modifying column definitions

Use the ALTER TABLE statement to drop columns, or change their types and attributes. For example, you can increase the length of an existing VARCHAR or VARGRAPHIC column. The number of characters might increase up to a value dependent on the page size used.

About this task

To modify the default value associated with a column, once you have defined the new default value, the new value is used for the column in any subsequent SQL operations where the use of the default is indicated. The new value must follow the rules for assignment and have the same restrictions as documented under the CREATE TABLE statement.

Note: Generate columns cannot have their default value altered by this statement.

When changing these table attributes using SQL, it is no longer necessary to drop the table and then re-create it, a time consuming process that can be complex when object dependencies exist.

Procedure

To modify the length and type of a column of an existing table using the command line, enter:
```
   ALTER TABLE table_name
      ALTER COLUMN column_name
      modification_type
```
For example, to increase a column up to 4000 characters, use something similar to the following:
```
   ALTER TABLE t1 
      ALTER COLUMN colnam1 
      SET DATA TYPE VARCHAR(4000)
```
In another example, to allow a column to have a new VARGRAPHIC value, use a statement similar to the following:
```
   ALTER TABLE t1 
      ALTER COLUMN colnam2 
      SET DATA TYPE VARGRAPHIC(2000)
```
You cannot alter the column of a typed table. However, you can add a scope to an existing reference type column that does not already have a scope defined. For example:
```
   ALTER TABLE t1 
      ALTER COLUMN colnamt1 
      ADD SCOPE typtab1
```
To modify a column to allow for LOBs to be included inline, enter:
```
   ALTER TABLE table_name
      ALTER COLUMN column_name
      SET INLINE LENGTH new_LOB_length
```
For example, if you want LOBs of 1000 bytes or less to be included in a base table row, use a statement similar to the following:
```
   ALTER TABLE t1 
      ALTER COLUMN colnam1 
      SET INLINE LENGTH 1004
```
In this case, the length is set to 1004, rather than 1000. This is because inline LOBs require an additional 4 bytes of storage over and above the size of the LOB itself.

To modify the default value of a column of an existing table using the command line, enter:

   ALTER TABLE table_name
      ALTER COLUMN column_name
      SET DEFAULT 'new_default_value'

For example, to change the default value for a column, use something similar to the following:

   ALTER TABLE t1 
      ALTER COLUMN colnam1 
      SET DEFAULT '123'