Here are sample SQL queries on how to use ALTER COLUMN without dropping a table in DB2.
More: Oracle SQL-Step by step SQL
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.
How to Use ALETR COLUMN
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 typtab1To 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'More ideas on SQL for next career
