Chapter 7. Database Change Management and Schema Evolution
IN THIS CHAPTER
In Chapters 5 and 6 we examined how to create useful and efficient DB2 database objects. After we create databases, table spaces, tables, indexes, and so on, we build application programs to access the DB2 data and move everything into production. Eventually, we will need to change the database to meet changing business needs.
One of the bigger causes of database downtime is when changes are made to DB2 database structures. This is so because many of the most common changes required of DB2 objects cannot be supported with simple
s. Instead, DBAs are required to
the object and then re-
it with the desired changes.
Such changes can be
. Suppose you need to extend the length of a column in a table, perhaps because business conditions have changed, necessitating longer values. For example, (for V7 and all previous releases) to change a
, the following steps need to occur:
Unload the data, and then extract the DDL and authorizations for the table you are about to change and all its dependent objects (indexes, views, synonyms, triggers, and so on).
Drop the table.
Modify the DDL for the table to change the length of the particular column from 10 to 15
statement to re-create the table.
statements for the dependent objects (indexes, views, synonyms, triggers, and so on).
Rebuild the authorizations for the table by running the appropriate
Re-create all dependent objects and rebuild their authorizations.
Reload the data, taking care to build the
statement properly, because there is a new column right in the middle of the other
Don't forget to run
to gather statistics for optimization and run
to back up the data in its new format after it is loaded.
all affected plans and packages.
Test everything to make sure your changes were implemented correctly.
You can see how this
manual process can be very
, and the data is unavailable for the duration of this procedure. Furthermore, if you
any single item or step, the resulting database structures will not be accurate and problems will arise.
Of course, not all database changes require such drastic steps. Many changes can be made using a simple
to change specific aspects of a DB2 object. Sometimes the object will need to be
and started for the change to take effect, sometimes not. DB2 V8 features online schema evolution to begin the process of making more types of database changes possible without requiring objects to be dropped and re-created.
Third-party ISV tools are available that make the process of modifying DB2 database structures easier and safer. These products provide a simple
interface that allows the
the changes to be made. The tool then automates the
process. Examples of such products include BMC Software's Change Manager and Computer Associates' RC/Migrator. More information on products such as these is
in Chapter 39, "Components of a Total DB2 Solution."
DB2 V8 begins the process of making it easier to implement database changes with fewer steps and less downtime. IBM calls the changes being made to DB2 to facilitate simpler and quicker database changes
online schema evolution
. For example, as of V8 you can change a
column to a larger
simply by using
. The remainder of this chapter will focus on the improved schema changes supported by DB2 Version 8.