How to use ALTER Table, best examples to save time

The below are the list of selected examples or syntax that you can use to change column attributes, 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.

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'

More ideas on SQL for next career

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.