Altering your database design

After using a relational database for a while, you might want to change some aspects of its design.

To alter the database design you need to change the definitions of Db2 objects.

If possible, use the following SQL ALTER statements to change the definitions of Db2 objects.

When you cannot make changes with ALTER statements, you typically must use the following process:

  1. Use the DROP statement to remove the object.
    Attention: The DROP statement has a cascading effect. Objects that are dependent on the dropped object are also dropped. For example, all authorities for those objects disappear, and packages that reference deleted objects are marked invalid by Db2.
  2. Use the COMMIT statement to commit the changes to the object.
  3. Use the CREATE statement to re-create the object.

The following table provides links to task and reference information for altering specific types of Db2 objects.

Table 1. Where to find information about altering Db2 database objects
Object type Task information SQL statement reference
Databases Altering Db2 databases ALTER DATABASE
Table spaces Altering table spaces ALTER TABLESPACE
Tables Altering Db2 tables ALTER TABLE
Views Altering Db2 views ALTER VIEW
Indexes Altering Db2 indexes ALTER INDEX
Storage groups Altering Db2 storage groups ALTER STOGROUP
Stored procedures Altering stored procedures
User-defined functions Altering user-defined functions