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:

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.
Use the COMMIT statement to commit the changes to the object.
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 statement
Table spaces	Altering table spaces	ALTER TABLESPACE statement
Tables	Altering Db2 tables	ALTER TABLE statement
Views	Altering Db2 views	ALTER VIEW statement
Indexes	Altering Db2 indexes	ALTER INDEX statement
Storage groups	Altering Db2 storage groups	ALTER STOGROUP statement
Stored procedures	Altering stored procedures	ALTER PROCEDURE statement (external procedure) ALTER PROCEDURE statement (SQL - external procedure) (deprecated) ALTER PROCEDURE statement (SQL - native procedure)
User-defined functions	Altering user-defined functions	ALTER FUNCTION statement (external function) ALTER FUNCTION statement (compiled SQL scalar function) ALTER FUNCTION statement (inlined SQL scalar function) ALTER FUNCTION statement (SQL table function)