 | Level: Intermediate Carolyn Henry (henryca@us.ibm.com), Information Developer, IBM
Marcia Miskimen (mjmiski@us.ibm.com), Software Engineer, IBM
Jayashree Ramachandran (jayshree@us.ibm.com), Developer, IBM
Sailaja Bandlamoori (bandlam@us.ibm.com), Testing Lead, IBM
26 Apr 2007
Updated Aug 2008 Have you ever wondered when the database was changed? With
Data Studio Administrator wonder no more! Data Studio Administrator helps you
keep track of changes, work seamlessly with other team members, reverse or undo changes, and audit any changes made to your database. This article describes a scenario where the DBA organization uses Data Studio Administrator along with the Eclipse Team project to enhance their collaboration and ensure a consistent audit path. Learn about connecting to a library control system and saving change management projects, models, and scripts in library control, as well as auditing the changes. Also, discover how to retrieve files from library control and use a deployment script to undo the change.
NOTE: This article is an update of a previously published article about DB2 Change Management Expert. This article reflects the new name, IBM Data Studio Administrator, and other changes that are available with Data Studio Administrator Version 1.2.
Introduction
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.
The overall process
The following diagram illustrates the overall process of changing a database using Data Studio Administrator and a version control system:
Figure 1. Overall process of changing a database with Data Studio Administrator
Data Studio Administrator and Eclipse
Data Studio Administrator is a tool that can help DBAs 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.
Data Studio Administrator is an Eclipse-based tool. Eclipse is an open source, 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 Data Studio Administrator. For more information about the Eclipse platform, see the Resources section of this document. A successful version control process within Data Studio Administrator includes using the Eclipse Team functionality.
Eclipse Team integration is a critical component of the Data Studio 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 Studio Administrator project (including all of the resources that it contains) should be checked into version control and tagged or labeled.
You can also archive your Data Studio 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.
The relationship between Data Design Projects, databases, and version control
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 Visioning System (CVS) or IBM Rational Clear Case. This article uses CVS for most of the examples.
Data Studio Administrator uses projects to group the different resources that you need to make a change. A Data Design Project typically tracks 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. A single Data Design Project can be used to manage multiple databases if those databases are essentially copies of a primary database. This is referred to as multiple provisioning, which means that the change is first constructed for a single database and then deployed to multiple databases.
Version control systems that plug into Eclipse, such as CVS or IBM Rational Clear Case, provide the best integration with Data Studio Administrator. However, because Data Studio 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 Data Studio Administrator resources. You can also manage your changes without a formal version control system. This article describes one such situation in the How to use Data Studio Administrator without using a version control system section.
Using Data Studio Administrator with a version control system
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 Data Studio Administrator using DB2 V9.1. The example is broken down into the following four parts:
- Jaya makes a change to the database.
- Jaya shares the project by committing it to the version control system.
- Eric checks out the project and makes additional changes.
- Jaya is unhappy with the changes that Eric made and reverses them.
Important: This scenario uses the DSADEMO database. You can install the DDL (CreateDSADEMO.chx) to create and setup this database by downloading the sample02.zip file from the Download section of this article and extracting the contents of the sample02.zip file into a local directory. The following are the steps for setting up the database:
- Create a Data Design Project by selecting File -> New -> Data Design Project and name it test.
Figure 2. New Data Design Project
- In the Data Project Explorer view of Data Studio Administrator, import the CreateDSADEMO.chx file into the project test. Expand the contents of the SQL Scripts folder in the test project, right-click the CreateDSADEMO.chx file and select Run SQL. Verify that the appropriate database version is selected. Enter your username and password, leave the check box for Create Deployment Project and Script file unchecked, and click Finish.
In the Database Explorer view, verify that the DSADEMO database was created and that a connection exists. You can now continue to complete the steps throughout this article.
Part 1. One of the DBAs, Jaya, makes a change to the database.
As Jaya, you would complete the following steps:
- Create a new deployment script called TestAudit. A deployment script is a Data Studio Administrator resource that keeps track of the change management process. You can specify the location and name of the database to be changed in the New Deployment Script wizard. Choose Change in Place process and leave Migrate Table Data unchecked. Choose only the HR schema. Two models will be created for the database that you specify. Models are representations of the database. One of the models, known as the base model represents the current state of the database, and the other model, known as the target model is the one that you can edit to define the new changes.
- Working from the Outline view, make the changes to the target
model. For example, create a new column called LOCATION of type
CHAR(128) to the CL_SCHED table. The new column can be added in
the Data Model Editor in the top-center of the screen. Save the
model after the changes are made. Figure 3 shows this step.
Figure 3. The data column 'LOCATION' in the CL_SCHED table
- Open the deployment script. Generate change commands by clicking
Generate in the Deployment Script Overview. The Generate Change Commands wizard is displayed. In addition to the change commands, the wizard also creates data-preservation commands. It is mandatory to specify a location on the file system for the data files that will be generated upon deployment. Any conflicts between the import and export column data types can be resolved by choosing auto-cast in the wizard. Data Studio Administrator also creates a Summary of Changes Report, which you can use to review or audit your changes.
Figure 4. The list of change commands that are generated by the Generate Change Commands wizard
Part 2. Jaya's changes are complete and she is ready to share the project by adding it to the version control system. The changes 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.
- Install the CVS Server and set up a repository.
- Open the CVS Repository Exploring perspective in Data Studio Administrator. This perspective includes a view called CVS Repositories, where you can add various repository locations.
- Add the repository location for the Data Studio Administrator Data Design Project by right-clicking in the CVS Repositories view and selecting New -> Repository Location. The following dialog is displayed:
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 perspective. Select the test 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 Data Studio 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.
Part 3. Eric, another DBA, can check out the project to make additional changes. Eric completes the following steps:
- Open the CVS Repository Exploring perspective. Select the test project in the CVS Repository view, right-click, and select Check Out. This creates a copy of the project in the current workspace. You can now modify the project and any associated files.
- Edit the target model. Drop the EMP_PHOTO table in the model and regenerate the change commands. You can refer to the steps in Part 1 of this process.
- Review the database model after completing the changes. If the model does not have the desired result, the new changes can be undone by reverting to the version that Jaya created. To undo the changes, right-click the project (or an individual resource in the project) in the Data Project Explorer, select Replace With -> Latest from Head. This step adds the EMP_PHOTO table back to the target model.
Note: The new changes made to the EMP_PHOTO table are local and will not be checked in to CVS until they are explicitly committed.
- Open the target model in the Data Model Editor and modify it again. The following is an example of the changes that you could make to the model:
- Add a new table in the HR schema, called COMPLETION_CODE with columns CODE of type INTEGER, and DESC of type VARCHAR(128). Make the column CODE the primary key in the table COMPLETION_CODE.
- Add a new column called CODE of type INTEGER to the PROJECT TABLE. Let the CODE column in the PROJECT table be defined as nullable.
- Create a foreign key relationship between the column CODE in PROJECT table and the primary key column CODE in the COMPLETION_CODE table.
After the changes are complete, save the model and regenerate the change commands. The undo commands will also be generated.
- On the Deployment Script Overview tab, select Deploy to deploy your changes to the target database.
Note: When you deploy in Data Studio Administrator, the changes are logged to a deployment log file in the workspace. This deployment log should also be checked into CVS with the test project.
- The changes will be deployed against the connection specified in the overview page of the Deployment Script Editor. If the connection does not exist in the workspace, then you must create a new connection with the same name for the target database. You can do this by selecting Connections -> New Connection from the Database Explorer view. The New Connection wizard will appear.
Figure 8. The New Connection wizard
- Right-click the test 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.
Part 4. Our first DBA, Jaya, checks out the project and reviews the changes that Eric made. If Jaya wants to undo the changes that Eric deployed to the database, she would perform the following steps:
- Open the deployment script and select Undo on the Overview tab of the Deployment Script Editor.
- Either reset the deployment script to start the change process from scratch, or modify the models again to generate the change commands that she wants for deployment.
You can reset the deployment script by opening the deployment script and
selecting Deploy -> Reset from the menu bar. This launches a
wizard that helps you reset the deployment script.
How to use Data Studio Administrator without using a version control system
If you do not have access to a version control system, can you still use Data Studio 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 Data Studio Administrator? The Data Project information is stored in the Data Studio 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.
Can I share the entire workspace with another user?
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.
How do I share the files between multiple DBAs?
There are several ways to share the project files. The simplest, and the 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 Data Studio 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 DDL, the deployment script contains changes from both Jaya and Eric (Eric could have removed or modified Jaya's changes but he did not in this case).
In Part 3, Eric drops a table, 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 Data Studio Administrator, there is some amount of local history kept in your workspace. In this particular scenario, Eric could proceed with dropping the EMP_PHOTO table from the target model, then put it back by right-clicking on the target model from the Data Project Explorer view and 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 deployment scripts, and use the Data Studio 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.
Conclusion
Using Data Studio 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 Data Studio Administrator with a well planned system of your own. Hopefully, this article has encouraged you to furhter explore how Data Studio Administrator can meet your needs.
Download | Description | Name | Size | Download method |
|---|
| Sample Project, DSADEMO | sample02.zip | 4.74KB | HTTP |
|---|
Resources Learn
Get products and technologies
-
Build your next development project with
IBM
trial software, available for download directly from developerWorks.
-
Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
Discuss
About the authors | 
| | Carolyn Henry is an Information Developer in the DB2 and IMS Tools group at IBM Silicon Valley Laboratory in San Jose, CA. She has worked on the DB2 Change Management Expert team since 2004 |
| 
| | Marcia Miskimen is a Software Engineer in the Information Management Tools group at IBM Silicon Valley Laboratory in San Jose, CA. She has worked in multiplatform tools support area since 2003. |
| | | Jayashree Ramachandran is a Developer in the Information Management Tools group at IBM Silicon Valley Laboratory in San Jose, CA. She has worked on the DB2 Change Management Expert team since 2004. |
| | | Sailaja Bandlamoori is a Testing Lead in the Information Management Tools group at IBM Silicon Valley Laboratory in San Jose, CA. She has worked on the DB2 Change Management Expert team since 2006, before that she worked in DB2 for z/OS client/server System Test. |
Rate this page
| |
