Altering tables
You can make changes to tables, such as adding columns, changing the name, or dropping constraints. Depending on the change that you want to make, use either the AL or ALT line command.
About this task
You can use Db2 Admin Tool to make the following changes to a table:
- Change the database, table space, owner, or name of a table
- Modify the definitions of table columns (with some restrictions)
- Change the sequence of the columns in a table
- Drop columns
- Insert new columns
- Drop or add unique, check, and foreign key constraints
- Modify table attributes such as auditing, data capture, validation procedure, restrict on drop, index access, and append processing
- Modify the data organization of the table
- Activate or deactivate row and column access control
- Drop or add column masks
- Add system or business time periods
- Drop or add versioning
- Add or alter partitions
- Add partitioning keys
- Drop or add clone tables
- Changes to column names are retrofitted to views. All other column actions are not retrofitted, and any changes to a column's data type are not verified against the views.
- All columns that comprise the partitioning columns of the table cannot be dropped.
- A warning is displayed if you attempt to modify columns in the primary key. With the UP line command (update primary key), you can circumvent the warning. You can use the ADDFK primary command to propagate the primary key update to foreign-key related tables.
- If you modify columns that are in a foreign key, Db2 Admin Tool does not automatically modify the primary key of the parent tables. To propagate the column updates to primary and foreign keys, use the ADD primary command from the ALTER Table (ADB27C) panel to initiate the Alter Tables dialog, where RI-related tables or other tables can be included in the ALTER JCL stream.
- Db2 Admin Tool informs you when a specific data type conversion is allowed. See Db2 Admin Tool data type conversions.
- If you modify a table that has a security label column or LOB columns or if you are creating a work statement list, you cannot specify HPU in the Unload Method field on the ALTER - Build Analyze and Apply Job (ADBPALT) panel. For work statement lists, you must specify UNLOAD.
- The HPU PARMLIB parameter must be set to the default value.
Use the AL line command to make changes that are supported by the ALTER TABLE statement. For example, you can add a primary key or partitioning key, add or insert a partition, alter a partition, rotate a partition, or drop a column. These changes are called non-intrusive changes. A non-intrusive alter is one in which the table does not have to be dropped and re-created. When you use the AL line command, an ALTER statement is generated.
Use the ALT line command to make table changes that you cannot make with an ALTER statement, such as renaming the table or adding a partition. In this case, the table is dropped and recreated and said to be redefined. The ALT line command can also make some, but not all, changes that are supported by ALTER statements. Additionally, ALT can process multiple objects and run utilities.
Altering a table by using the AL line command
Procedure
To alter a table by using the AL line command:
- On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
- On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
- On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
- On the Alter Table (ADB21TA) panel, type S next to the change that you want to select it, and press Enter.
- Complete the subsequent panels, and press Enter to run the ALTER TABLE statement.
Altering a table by using the ALT line command
Procedure
To alter a table by using the ALT line command:
-
On the ALTER Table
(ADB27C) panel, specify
any new attribute values.
In the following example, the schema, name, and owner are changed:
Figure 2. ALTER Table (ADB27C) panel 
ADB27C in ------------------- DB2N ALTER Table --------------- Row 1 to 5 of 5 Command ===> Scroll ===> PAGE New schema . . BDB > Old schema: DSN12010 New name . . . BDBCATVT > Old name : DEPT New owner . . ADMNEW > Type: (U/R) Old owner : ADMOLD Partitions . : 1 New DB . . DSN8D12A Rows per page: 47 New TS . . DSN8S12D Commands: NEXT CONSTRAINTS TBLOPTS SHORTNAMES ADDPART HASH Line commands: I - Insert U - Update D - Delete R - Repeat LAB - Label COM - Comment M - Move A - After B - Before X - Index RES - Reset update ? - Show all line commands Col Old Sel Column Name No Col Type Length Scale N D C X ColNo * * * * * * * * * * --- ------------------------------ --- -------- ----------- ----- - - - - ----- DEPTNO 1 CHAR 3 0 N N P 1 DEPTNAME 2 VARCHAR 36 0 N N 2 MGRNO 3 CHAR 6 0 Y Y F X 3 ADMRDEPT 4 CHAR 3 0 N N F X 4 LOCATION 5 CHAR 16 0 Y Y 5