A DBA will need to make many different types of changes to a database over its lifetime. Some will be simple and easy to implement, others much more difficult and complex.
The SQL ALTER statement can be used to make many types of changes to databases. However, other types of changes may require additional steps to implement. It is the DBA’s job to understand the best way to implement any type of database change. Keep in mind that simple changes often become more difficult in the real world. For example, a simple database change is not quite so simple when it needs to be propagated to multiple databases on different servers at multiple locations.
Many types of database object alteration cannot be performed using the basic SQL ALTER statement. As usual, this varies from DBMS to DBMS
and, indeed, from version to version of a single DBMS. Actions that are most likely to not be supported by ALTER include changing the name of a
database object (depending upon the DBMS and version, some objects can be renamed using the RENAME statement), moving a database object to another database, changing the number of table space partitions or data files, removing a partition from a partitioned table space or index, as well as others. In some limited cases, it is possible to use ALTER to change the length of certain types of columns.
For example, in 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.
A single complex change, such as removing or renaming a column, can take hours to implement manually. Changing the name of one column can require hundreds of changes to be scheduled, executed, and verified from development to test to production. 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 in-depth 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 issues can be the basis of justifying the purchase of a database change management tool to streamline and automate database change management. Keep in mind that the list of items above is not exhaustive and that it will differ from DBMS to DBMS. DBA tools exist that manage the change process and enable 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 provide a reduction in the amount of time required to specify what needs to change; a more simple and elegant method of analyzing the impact of database changes; a reduction in technical knowledge needed to create, alter, and drop database objects; as well as the ability to track all changes over time; and an increase in application availability by reducing the time it takes to perform changes.
A database change management tool is one of the first tools acquired by many organizations when they implement a database of any size. Such tools reduce the amount of time, effort, and human error involved in managing database changes. Tools notwithstanding though, rare is the database that never needs to be changed. Planning your change management efforts will help to ensure that your database modifications are successful.