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

Restrictions:

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
Altering a table by using the ALT line command

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 DB2 Administration Menu (ADB2) panel, select option 1, and press Enter.
On the System Catalog (ADB21) panel, specify any selection criteria at the bottom, specify option T, and press Enter.

On the Tables, Views, and Aliases (ADB21T) panel, issue the ALT line command against the table to be changed, and press Enter:

Figure 1. Tables, Views, and Aliases panel (ADB21T)

 ADB21T in --------------- DB2X Tables, Views, and Aliases --------- Row 1 of 1 
 Command ===>                                                  Scroll ===> PAGE 
                                                                                
 Commands: GRANT  MIG  ALL                                                      
 Line commands:                                                                 
  C - Columns  A - Auth  L - List  X - Indexes  S - Table space  D - Database   
  V - Views  T - Tables  P - Plans  Y - Synonyms  SEL - Select prototyping      
  ? - Show all line commands                                                    
                                                                                
 Sel   Name               Schema   T DB Name  TS Name    Cols        Rows Chks C
       *                  *        * *        *             *           *    * *
 ----- ------------------ -------- - -------- -------- ------ ----------- ---- -
 ALT   DEPT               DSN11010 T DSN8D10A DSN8S10D      5          14    0  
 ******************************* END OF DB2 DATA *******************************

On the ALTER Table (ADB27C) panel, specify any new attribute values.

Start of change In the following example, the schema, name, and owner are changed: End of change

Optional: To change additional attributes, such as a period definition for the table, issue the TBLOPTS command, and press Enter. Then, on the Alter - Table Options (ADBP7TOP) panel, specify any additional changes to the table, and press Enter:

Tip: For changes to some of these attributes, such as KEY LABEL, you must run the REORG utility to materialize the change. See Pending data definition changes (Db2 12 for z/OS).

On the ALTER Table (ADB27C) panel, issue the NEXT command, and press Enter.
Generate an ALT job.