Chapter 7. Database Change Management and Schema Evolution

Chapter 7. Database Change Management and Schema Evolution

IN THIS CHAPTER

• Online Schema Changes

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 ALTER s. Instead, DBAs are required to DROP the object and then re- CREATE it with the desired changes.

Such changes can be tedious and error-prone . 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 CHAR(10) column to CHAR(15) , the following steps need to occur:

1. 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).
   
   

2. Drop the table.
   
   

3. Modify the DDL for the table to change the length of the particular column from 10 to 15
   
   

4. Run the CREATE statement to re-create the table.
   
   

5. Run the CREATE statements for the dependent objects (indexes, views, synonyms, triggers, and so on).
   
   

6. Rebuild the authorizations for the table by running the appropriate GRANT statements.
   
   

7. Re-create all dependent objects and rebuild their authorizations.
   
   

8. Reload the data, taking care to build the LOAD statement properly, because there is a new column right in the middle of the other columns .
   
   

9. Don't forget to run RUNSTATS to gather statistics for optimization and run COPY to back up the data in its new format after it is loaded.
   
   

10. REBIND all affected plans and packages.
    
    

11. Test everything to make sure your changes were implemented correctly.
    
    

You can see how this intensive manual process can be very time-consuming , and the data is unavailable for the duration of this procedure. Furthermore, if you miss 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 ALTER to change specific aspects of a DB2 object. Sometimes the object will need to be stopped 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.

NOTE

Third-party ISV tools are available that make the process of modifying DB2 database structures easier and safer. These products provide a simple menu-driven interface that allows the user to indicate the changes to be made. The tool then automates the DROP and re- CREATE process. Examples of such products include BMC Software's Change Manager and Computer Associates' RC/Migrator. More information on products such as these is offered 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 CHAR column to a larger size simply by using ALTER . The remainder of this chapter will focus on the improved schema changes supported by DB2 Version 8.