Modifying SQL-Defined Databases › Methods for Modifying

Methods for Modifying

You can use the following methods to change an SQL-defined database:

• Single DDL statement

  You use a single DDL statement to make the change. The change takes effect immediately. For example, you use a single DDL statement when adding a check constraint.

• Multiple DDL statements

  You use multiple DDL statements to make the change. The particular SQL DDL statements you use depend on the type of change being made. For example, to change certain index characteristics (such as the order in which index keys are stored) you can use these SQL statements:

  • DROP INDEX
  • CREATE INDEX

  The change takes effect upon completion of these statements.

• Combination of DML and DDL statements

  You use a combination of DML and DDL statements to modify a definition. This method often involves dropping, redefining, and reloading a table to make the change.

  Once the data has been reloaded, the change takes effect. For example, to move a table to a new area, you use DML or utility statements to:

  1. Create a new table in the new area (DDL CREATE)
  2. Copy the rows of data to the new table (DML INSERT)
  3. Delete the existing table (DDL DROP)

Choosing a Modification Method

In some cases, you may choose the method to use. In other cases, the method is dictated by database factors such as whether the table contains data or whether it participates in a referential constraint.

Each modification is discussed in detail in the following chapters.

Inform Your Users

Some changes you make to the database will have a direct impact on your users. For example, if you drop a table or a view, users will no longer have access to the data.

Before you make a change such as dropping a table, you can use SELECT statements to determine where the entity to be changed is used. Specifically, look for:

• Views that reference the table
• Referential constraints in which the table participates
• Access modules that access the table

This indicates the potential impact the change may have and provides information about determining the best method to use to make the change.