When the data requirements of an organization change, the databases used to store the data must also change. If the data is not reliable and available, the system does not serve the business—rather, it threatens the health of the business. So, we need infallible techniques to manage database changes—and we need techniques that are not just fail-safe but also automated, efficient, and easy to use. Unfortunately, today’s database systems do not make managing database change particularly easy.
Relational databases are created using Data Definition Language (DDL) statements: CREATE, DROP, and ALTER. The CREATE statement is used to create a database object initially, and the DROP statement is used to remove a database object from the system. The ALTER statement is used to make changes to database objects. But many aspects of database objects cannot be changed by using the ALTER statement, instead requiring a complex sequence of DROP and CREATE statements to achieve.
Further exacerbating the problem, the exact specifications for what can and cannot be changed using ALTER differs from DBMS to DBMS. Some of the actions that are most likely to not be supported by ALTER include: moving a table or table space to another database; moving a table from one table space to another; rearranging the order of columns in a table; modifying the data type and length of a column; removing columns from a table; changing the definition of a primary key or a foreign key; adding a column to a table that cannot be null; modifying the columns in a view; changing the columns of an index; and modifying the contents of a trigger.
Database change management can be complicated and therefore requires knowledgeable DBA staff and robust system management software to be handled appropriately.
In some limited cases, it is possible to use ALTER to change the length of certain types of columns. For example, in DB2 and Oracle you can alter a character column to a larger size, but not to a smaller size. Additionally, it may be possible to change a column from one numeric data type to another. DB2 allows the modification of a column’s data type, as long as the change is within the same data type family (numeric to numeric, character to character, or datetime to datetime). For example, it is legal to change a column from SMALLINT to INTEGER using ALTER, but not from SMALLINT to DATE. In general, though, significant changes to the data type and length of a column usually require the table to be dropped and recreated with the new data type and length.
When making changes to a database requires an object to be dropped and recreated, the DBA must cope with the cascading DROP effect. A cascading DROP refers to the effect that occurs when a higher-level database object is dropped: All lower-level database objects are also dropped. Thus, if you drop a database, all objects defined in that database are also dropped. The cascading DROP effect complicates the job of changing a database schema.
Making physical changes to actual database objects is merely one aspect of database change. Myriad tasks require the DBA to modify and migrate database structures. One daunting challenge is to keep test databases synchronized and available for application program testing. The DBA must develop robust procedures for creating new test environments by duplicating a master testing structure. Furthermore, the DBA may need to create scripts to set up the database in a specific way before each test run. Once the scripts are created, they can be turned over to the application developers to run as needed.
Another challenge is recovery from a database change that was improperly specified, or backing off a migration to a prior point in time. These tasks are much more complicated and require knowledge of the database environment both before and after the change or migration.
These challenges justify the existence of database change management tools that streamline and automate database change management. This type of tool manages the change process and enables the DBA to simply point and click to specify a change. The tool then handles all of the details of how to make the change. Such a tool removes from the shoulders of the DBA the burden of ensuring that a change to a database object does not cause other implicit changes. Database change management tools reduce the amount of time, effort, and human error involved in managing database change.
Of course, this discussion of database change management has been brief. A thorough investigation of the topic would reveal many additional nuances, such as metadata management, integration database, and application changes, regulatory compliance requirements, managing database change requests, the impact of change of recovery management, database comparison, and so on. The bottom line is that database change management can be complicated and therefore requires knowledgeable DBA staff and robust system management software to be handled appropriately.
Craig S. Mullins is president of Mullins Consulting, Inc. He’s an IBM Gold Consultant and the author of two best-selling books, DB2 Developer’s Guide and Database Administration: The Complete Guide to DBA Practices & Procedures. Website: www.mullinsconsulting.com.