Maintaining a database is really all about change. You need to ensure that your database applications work as intended, that changes roll out smoothly, and that if something goes wrong, you can investigate the problem. The database can log some activities, but logs can be difficult to analyze. In the end, logs provide an incomplete picture of database changes. Why not borrow some technology from software development to help manage your changes? The traditional approach to keeping track of changes in software development is to use some type of change management system, process, or tool. This approach has many names: configuration management, change management, source code control, library control, version control and so on. For this article, we will use the term version control.
The process of managing changes to an application or to the product code itself is quite mature. Most programmers are familiar with checking in and checking out their code, and know what versions go with which software release. But are the other players in the application development cycle aware of the importance of these factors? Do they use version control tools? What about the architect's design, the project manager's plan, the writer's documentation, or the tester's scenarios and results? What about the database administrator? An application is more than just the code. All the parts that comprise a release should be kept together. Any object that is part of the application can, and should, be part of the version control tool or process.
Figure 1 illustrates the overall process of changing a database using Optim Database Administrator and a version control system:
Figure 1. Overall process of changing a database with Optim Database Administrator
Optim Database Administrator can help DBAs manage their database systems. It allows DBAs to configure the database, keep track of their changes, work together with other DBAs who contribute different changes, audit and manage the history of those changes, and reverse or undo any changes they no longer need.
Optim Database Administrator is based on open source Eclipse, which provides a platform-independent software framework for delivering rich-client applications. The Eclipse platform enables other tool developers to easily build and deliver integrated tools. This framework was used to develop the Integrated Development Environment (IDE) for Optim Database Administrator. For more information about the Eclipse platform, see the Resources section. A successful version control process within Optim Database Administrator includes using the Eclipse Team functionality.
Eclipse Team integration is a critical component of the Optim Database Administrator version control capabilities. The Eclipse Team component provides a mechanism that allows the repository tools to integrate the full, rich, functionality of their repository solution into the Eclipse workbench. The example in this article illustrates the Eclipse Team functionality. For more information about Eclipse Team, see the Resources section of this document.
After a change to a database is made, the data design project (including all of the resources that it contains) should be checked into version control and tagged or labeled.
You can also archive your Optim Database Administrator projects using the Eclipse Team capability. You should archive your projects to track your changes. You can archive during the process of change development, even before you deploy any of your changes. This way, you can work in iterations, other team members or DBAs can participate and contribute changes, and others can review and modify the changes that have been made.
You can use version control in different ways to manage your database change projects. You can use a formal or informal version control system. A version control system could be as simple as the file system on your machine or a full-blown offering such as Concurrent Versioning System (CVS) or IBM Rational® Clear Case. This article uses CVS for most of the examples.
Optim Database Administrator uses projects to group the different resources that you need for making a change. Data Design Project is a repository for all the resources (scripts, models, log files, etc.) pertaining to a change, and it is also useful in tracking the life cycle of a database. The project can be shared using the Eclipse team functionality so that several DBAs can collaborate on a change. Changes are represented by Data Design Projects at particular points in time. Once a change is deployed, the resources are usually committed to the version control system and tagged or labeled. You can use the tag or label to get back to the point where the changes were saved, to undo a change, or to audit a particular change.
In more complex databases, a Data Design Project can be used to manage the life cycle of a particular database application. In some shops, tables or schemas are broken up and managed by particular DBAs or DBA teams. Data Design Projects can be adapted to match these environments. In this way, a single database can be broken up and managed by several Data Design Projects.
Version control systems that plug into Eclipse, such as CVS or IBM Rational Clear Case, provide the best integration with Optim Database Administrator. However, because Optim Database Administrator stores all of its data files and folders on the local file systems, even version control systems that do not integrate with Eclipse can be used to manage database change resources. You can also manage your changes without a formal version control system. This article describes one such situation in the How to use Optim Database Administrator without using a version control system section.
This example demonstrates how you can use version control for auditing your changes and for coordinating changes made by different DBAs. The figures in this example show Optim Database Administrator using DB2 V9.1. The example is broken down into the following four parts:
- Part 1: Jaya makes a change to the database
- Part 2: Jaya shares the change resources by committing it to the version control system
- Part 3: Eric checks out the project, and makes additional changes and then deploys them
- Part 4: Jaya is unhappy with the changes that Eric made and reverses them
This scenario uses the GSDB database. You need to also use the sample database named GSDB. Follow these steps to create the database:
- Get the zip file from the Download section of this article, and extract the GSDB_Database.sql file from it.
- Open a DB2 command window.
- Navigate to the location where you saved the GSDB_Database.sql file.
db2 -td~ -f GSDB_Database.sql
Upon launching Optim Database Administrator, connections to the databases appear automatically in the Data Source Explorer, as shown in Figure 2. When using these connections for the first time, you are required to enter your user name and password information in order to connect to the databases.
Figure 2. Connections in Data Source Explorer
Jaya creates a new table called GOSALES.PROD_PROMO for storing information about rebates available on select products. To make this change, she uses ODA's Change Management script editor. As Jaya, you would complete the following steps:
- Create a new change management script.
In the Data Source Explorer View, expand the connection GSDB (GSDB needs to be in the connected state). Right-click on the folder Change Management, and select the menu item New Database Change..., as shown in Figure 3a.
Figure 3a. Create a new Change Management wizard
This opens the New Change Management wizard. In this wizard, select the schemas called GOSALES and GOSALESCT, and then click Finish.
When the wizard is closed, a new script file called GSDB.changexml is created. Two physical data model files are also created. One of the model files, known as the base model, represents the database before the change. The second file, called the target model, will contain all the new changes made by the DBA in the editor. The change commands are generated based on these two model files. The changexml script file and the database model files are created in a new project called GSDB, and they can be accessed using the Data Project Explorer view.
The newly created change management script file is automatically opened in the editor for the DBA to begin the change process.
- Create the table PROD_PROMO.
In the editor, define the new table (PROD_PROMO) that is to be created in the database. To do this, add the table to the Objects to be changed list by clicking on the add icon (represented by a + sign) and then selecting the database object type Tables in the pop-up list (see the figure 3b below). Use the schema GOSALES for the new table.
Figure 3b. Create new table, PROD_PROMO, using the Change Management Editor
- After the table has been added to the "Objects to be changed" list,
select the table in the editor, and make the following changes in the
- If the new table has a default name (such as Table1), then rename it to PROD_PROMO.
- Add the following columns by clicking on the new icon
(represented by a green + (plus) sign), as shown in
- PROMO_CODE of type INTEGER
- PROD_NUMBER of type INTEGER
- START_DATE of type DATE
- END_DATE of type DATE
- DISCOUNT_PCNT of type INTEGER
- DESCRIPTION of type VARCHAR(128)
Figure 4a. Rename the new table and add new columns to it
- Make the column PROMO_CODE the primary key in the table PROD_PROMO by selecting the Primary Key check-box for the column.
- Add the table space USERSPACE1 to the table GOSALES.PROD_PROMO using the Table Space tab in the Properties view, as shown in Figure 4b.
Figure 4b. Specify the tablespace for PROD_PROMO
- Create a foreign key relationship between the column PROD_NUMBER
column in the table GOSALES.PROD_PROMO and the primary key column
PRODUCT_NUMBER in GOSALES.PRODUCT. To do this, add a new Foreign Key
constraint to the "Objects to be changed" list with GOSALES.PROD_PROMO
as the base table and GOSALES.PRODUCT as the parent table. To do so,
click on the Add icon, and then select the database object type
Foreign Key Constraint, as shown in Figure 4c. You will be
prompted to specify the base and the parent tables.
Figure 4c. Create Foreign Key relationship between tables GOSALES.PROD_PROMO and GOSALES.PRODUCT
- Next, select the newly added foreign key constraint in the editor and
specify the key columns using the Properties view. Choose the
Details tab in the properties view. For the Child,
add PROD_NUMBER as the foreign key by clicking on the Key
Columns browse button. For the Parent, choose the
primary key defined on PRODUCT_NUMBER in the Unique Constraint or
Index combo box. This is shown in Figure 4d:
Figure 4d. Add key columns to the Foreign Key Constraint
In the General tab, within the Properties view, select the radio button called Non-Identifying, as shown in Figure 4e:
Figure 4e. Update Foreign Key properties
Save the changes.
When the changes are committed in the version control system, they can later be extracted from the version control system and used to continue the change management process. This also allows other administrators to audit the changes, if necessary. The changes made from two or more DBAs can easily be combined and coordinated.
CVS is the version control system in this example. Here are the steps needed to add the project to CVS.
- Install the CVS Server and set up a repository.
- Switch to the CVS Repository Exploring perspective. This perspective includes a view called CVS Repositories, where you can add various repository locations.
- To add the repository location for the Optim Database
Administrator Data Design Project, right-click in the CVS
Repositories view and select New > Repository
Location, as shown in Figure 5:
Figure 5. The Add CVS Repository dialog
- Enter the information for the required fields and select
Finish. You will now see the repository that you added
in the CVS Repository view.
Figure 6. The Repository Exploring view with your new repository location
You can drill-down to browse through the contents in the repository.
- Switch back to the Data Administration perspective. Select the
GSDB project that you want to check into CVS,
right-click on it and select Team -> Share Project.
The Share Project wizard is displayed.
Figure 7. The Share Project wizard
- Select the existing repository location (you have already added it in the previous step). Accept all of the defaults and click Finish. Remember: You can check in all the Optim Database Administrator resources as ASCII type. Any DBA with permission to access this specific repository location is now be able to check out the project, review it, and make changes.
As Eric, you would complete the following steps:
- Check out the project.
- Open the CVS Repository Exploring perspective.
- Select the GSDB project in the CVS Repository view, right-click, and select Check Out. This creates a copy of the project in the current workspace.
- Switch back to the Database Administration prespective. The checked-out files from CVS should now be available in the Data Project Explorer view. You can now modify the project and any associated files. Before opening the file GSDB.changexml in the editor, ensure that the connection GSDB exists and is in the connected state.
- Specify additional changes to be made.
- Open the change management script file, GSDB.changexml, by double-clicking on it in the Data Project Explorer view. This file is located under the SQL Scripts folder in the Data Project view.
- Select the table PROD_PROMO in the editor.
- In the Properties view, rename the PROMO_CODE column to
- Save the changes.
- Assume that Eric realized that these changes were wrong. To revert back to the version in CVS, close all the editors, right-click on the project GSDB (or an individual resource in the project) in the Data Project Explorer view, and then select the context menu Replace With > Latest from Head. This will undo all the changes made since the files were checked out. This feature (point-in-time UNDO) is very handy, especially when large number of changes have to be undone.
- After files have been replaced, you would make the following
additional changes, as Eric:
- Re-open the file GSDB.changexml, and then add a new column
called PROMO_CODE of type INTEGER to the table
GOSALESCT.CUST_ORD. Add the table to be altered to the
"Objects to be Changed" list by clicking on the Alter
icon and then selecting the table CUST_ORD, as shown
in Figure 8a:
Figure 8a. Select table GOSALESCT.CUST_ORD to be altered
Select the table in the editor, and add the new column using the Properties view, as shown in Figure 8b:
Figure 8b. Add new column PROMO_CODE to the table GOSALESCT.CUST_ORD
- Create a non-identifying foreign key relationship between the column PROMO_CODE in GOSALESCT.CUST_ORD table and the primary key column PROMO_CODE in the table GOSALES.PROD_PROMO table.
- Re-open the file GSDB.changexml, and then add a new column called PROMO_CODE of type INTEGER to the table GOSALESCT.CUST_ORD. Add the table to be altered to the "Objects to be Changed" list by clicking on the Alter icon and then selecting the table CUST_ORD, as shown in Figure 8a:
Save the changes and generate the commands by clicking on the Preview Commands button in the change management editor. Verify that the generated commands are okay.
- The generated commands include commands to preserve data in
case of a destructive table change. In addition, maintenance
commands such as
rebindare also included to restore the state of the database after command deployment.
- If the generated commands have some errors (such as incorrect export and import column mapping), you will be prompted to use the Data Options wizard, which will guide you through the steps for rectifying the problems. For more information on Change Management using Optim Database Administrator, see the Resources section of this document.
- The generated commands include commands to preserve data in case of a destructive table change. In addition, maintenance commands such as
- Apply the changes to the database.
Click on the button Run... in the editor to deploy the commands against the database GSDB. The deployment status message will be displayed in the editor and also in the SQL Results view, as shown in Figure 9. The status message section in the editor also includes a progress bar that gives an estimate of the time left to complete command deployment.
Figure 9. Command Deployment
- Save the files and close the editor. To copy the new changes to CVS, right-click on the GSDB project ,and select Team > Commit to check the changes into CVS.
Now the entire team can review the changes that Jaya and Eric have made.
Even though changes are usually deployed on a database only after it has been approved, there may be situations when you realize the need to back out a change after it has been made. This part describes one such situation, where the first DBA decides to UNDO the changed deployed.
Jaya would perform the following steps to undo the changes that Eric deployed to the database:
- Check out the project from CVS (if the project already exists in the workspace, then replace with the latest from CVS.
- Ensure the connection GSDB exists and is in the connected state.
- Open the change management script and click on the Undo... button in the editor (the Undo button is in the Messages section in the editor).
- After the undo commands have been deployed, reset the change management script (using Change Management > Reset in the main menu) to start the change process from scratch.
If you do not have access to a version control system, can you still use Optim Database Administrator? Of course! You might still be required to adhere to some controls for auditing and tracking purposes, so how would you do this if your DB2 changes are all stored in Optim Database Administrator? The Data Project information is stored in the Optim Database Administrator Workspace, which you define at startup. The workspace is a set of directories on your local disk, so you can just keep those files together as the set of files for the version.
Let's use the example from this article, but assume that you do NOT have a version control system.
Part of the benefit you get from using a repository to store your changes is the rich history that is available in the workspace. You might need the history for auditing purposes or to back out a change. If you deal only with the project files themselves, it is up to you to keep track of what changes were done when, and by whom.
Technically, yes, it is possible to just share the whole workspace, for example on a shared drive. However, if you attempt to open the workspace while someone else has it open, you will receive an error message that says the file is in use. Another disadvantage of sharing a workspace is that all the settings are also shared, so you could lose any customizations if someone else changes a setting. It is not recommended to share the workspace.
There are several ways to share the project files. The simplest approach described here, is to export the entire project as an archive (ZIP file), and have the other user import the project into their workspace. Following the scenario above, where Jaya and Eric are working on some database changes, Jaya is now at the end of Part 1 after all the change commands are generated. Instead of checking the files into version control, Jaya now needs to preserve the changes and make them available to another DBA working on the project. Jaya would perform the following steps:
- Select the project, then select File -> Export to bring up the Export dialog.
- Expand the General folder, select Archive File and click Next.
- On the next screen, select the project, the output file location, filename and the format (ZIP or TAR) and click Finish.This creates the ZIP file on disk.
- Jaya can exit Optim Database Administrator, and make the exported ZIP file available to Eric.
Eric now needs to work on the project. Instead of checking it out of CVS, Eric must bring that project into his workspace manually. Eric would perform the following steps:
- In the workspace, select File -> Import.
- Expand the General folder and select Existing Projects into Workspace.
- Use the Select archive file to browse to the location of the archive file that Jaya exported. The projects inside that archive will appear in the Projects list box.
- Select the project and click Finish.
Now, the project is in Eric's workspace and he can proceed with the changes, as described in Part 3 of the process. When Eric makes the changes and generates the commands, the change management script contains changes from both Jaya and Eric (Eric could have removed or modified Jaya's changes, but did not in this case).
In Part 3, Eric renames a column, but changes his mind and reverts back to the version of the project in CVS. If you do not use a version control system, how can you do this?
In Optim Database Administrator, there is some amount of local history kept in your workspace. In this particular scenario, Eric could proceed with rename column PROD_NUMBER change specification, then revert this change on the target model and the change management script from the Data Project Explorer view by selecting Replace with Local History. Eric can revert back to a prior change. This might not work in all cases, and only works while in the workspace; that is, Eric and Jaya cannot revert back to each others' changes.
In Part 4 of the process, after Eric has exported the project to an archive (ZIP/TAR) file, Jaya can bring the project back into the workspace. Jaya should delete the existing project from the workspace and import the new project archive made by Eric. This project will have all the changes from Part 1 and Part 3. Jaya, however, will not have access to any local history for changes that Eric made, so reverting to prior changes is more difficult.
You can see from this scenario why a version control system is so important when you are working in a team environment.
An alternate approach to sharing changes by way of the entire project file is to share the individual models or change management scripts, and use the Optim Database Administrator merging and migration features to integrate the changes. This approach requires more attention to detail, but it can offer additional flexibility when dealing with multiple or more complex changes. Using either approach, you do not have the rich history capabilities available with a version control system.
Using Optim Database Administrator with a version control system provides a compelling resource to manage your business needs. Using both of these tools can help you streamline the sometimes arduous task of keeping track of all of the changes involved in the application development cycle. Even without an established version control system, you can still achieve the benefits by using Optim Database Administrator with a well-planned system of your own. Hopefully, this article has encouraged you to further explore how Optim Database Administrator can meet your needs.
|GSDB sample database for this article||GSDB_database.zip||33KB||HTTP|
Optim Database Administrator:
Learn more about Optim Database Administrator.
Eclipse.org: Find more information
Data migration and change management using Data Studio
(developerWorks, April 2009): Follow along with a DBA who uses Data Studio
Administrator to manage changes made to a production database table by
copying the objects and data to a test system, making the desired changes,
then propagating the changes back into the live database
Projects and Plug-ins:
Get more information about the Eclipse Team functionality.
"Share and Share Alike"
(developerWorks, September 2003): Learn about several methods of
repository and non-repository code sharing, and see the advantages and
disadvantages of each.
- developerWorks Information Management zone:
Learn more about Information Management. Find technical documentation,
how-to articles, education, downloads, product information, and
- Stay current with
developerWorks technical events and webcasts.
Get products and technologies
Optim Database Administrator: Download a 30-day
trial of Optim Database Administrator.
- Build your next
development project with
IBM trial software,
available for download directly from developerWorks.
- Check out the
Management experts blog.
Optim Database Administrator Forum
Integrated Data Management Community Space
- Participate in
and get involved in the
with your personal profile and custom home page, you can tailor
developerWorks to your interests and interact with other developerWorks
Sailaja Bandlamoori is the QA Team Lead for Data Studio Administrator in the Information Management Data Studio group at IBM Silicon Valley Laboratory in San Jose, CA. She has worked on the Data Studio Administrator product since 2006. Prior to that, she worked in DB2 for z/OS client/server System Test.