Deep analysis DB2 modification table

We believe that you are familiar with the DB2 table modification operation. The following describes some examples of DB2 table modification for your reference.

DB2 Modify Table
Use the alter table statement to change column attributes, such as void, LOB options, scope, constraints, compression attributes, and data types.

For example, enter:

Alter table employee alter column workdept set default '201312'

On DB2 V9 for Linux, UNIX and Windows, the alter table statement has been improved. Now it can be used to perform the following operations:

● Use the new drop column clause to delete a COLUMN

● Use the alter column set data type clause to modify COLUMN attributes

● Use the set not null or drop not null clause to modify the NULL attribute of a column

When using SQL to modify these table attributes, you no longer need to delete the table and recreate it. This was originally a very time-consuming process, and it may be complicated when the object dependency exists. In addition to the new features mentioned above, you can also modify the TABLE statement before DB2 V9:

● Add columns. The new column is the last column in the table. That is to say, if n columns exist at the beginning, the column to be added will be column n + 1. Adding a new column cannot make the total number of bytes of all columns exceed the maximum record size.

● Modify the default value associated with the column. After a new default value is defined, the new value will be used for columns that are indicated to use this default value in any subsequent SQL operations. The new value must comply with the assignment rules, and is subject to the same restrictions as the records in the create table statement.

Here are a few examples of using the alter table statement to modify a TABLE:

Example 1 Add the Managing_Bank column to the ACCOUNT table:

Alter table V9R0M0. account add column Managing_Bank VARCHAR (15)

Note:

This feature is available in DB2 Universal Database Version 8.

Example 2 Delete the Instruction_ID column in the TRANSACTION table:

Alter table V9R0M0. transaction drop column Instruction_ID

Example 3 change the data type of the Account_ID column in the ACCOUNT and TRANSACTION tables from SMALLINT to INTEGER:

Alter table dev. account alter column Account_ID SET DATA TYPE INTEGER

Alter table dev. transaction alter column Account_ID SET DATA TYPE INTEGER

Example 4 Delete the not null attribute of the Credit_Line column in the ACCOUNT table:

Alter table dev. account alter column Credit_Line DROP NOT NULL

Example 5 increase the size of the Description column in the TRANSACTION table:

Alter table dev. transaction alter column Description set data type varchar (60)

Note:

This feature is available in DB2 Universal Database Version 8.

Example 6 modify the default value of colnam1 in Table t1:

Alter table t1 alter column colnam1 set default '201312'

Note:

This feature is available in DB2 V8.

DB2 mount command performance factors

How to back up all tables in DB2

How to Implement DB2 row-to-column Conversion

Implementation of Online DB2 backup

Implementation of deleting duplicate data in DB2