Overview

Imagine the database change management process as a set of interlocking puzzle pieces. One puzzle piece is data modeling and another is database change. This article shows you IBM's solution for how those two pieces fit together. IBM Data Studio Administrator is a database change management tool that can be used on its own as well as with IBM Rational Data Architect, a tool for enterprise data modeling and integration design. Both tools speak the same language when it comes to physical data models, thus integrating processes from design and modeling to database change management. Instead of translating changes between individuals and groups, physical data models put data architects, developers, and database administrators on the same page.

Both Data Studio Administrator and Rational Data Architect use the Eclipse framework. Teams that use both products in their shops can leverage Eclipse Team components to control the flow of information. Furthermore, both Rational Data Architect and Data Studio Administrator can appear in the same Eclipse framework at once (that is, via a "shared shell" installation). In shops where one user wears many hats, the shared shell arrangement allows for a single user interface.

Scenario overview

This article illustrates how a customer might use both products with a scenario. We will use a hypothetical company, JK Enterprises, as our example customer. JK Enterprises has begun to use Data Studio Administrator and Rational Data Architect. Tom, the data modeler at JK Enterprises, is using Rational Data Architect, while Eric, the DBA, is using Data Studio Administrator. Note though, in some smaller shops, one person might use both tools.

Assume that JK Enterprises determines that they need to add completion codes to their projects. They need to make a change to a database to add a completion code table, and they would also like it to be reflected in their logical model as well. The teams are not sure their logical models are up to date, so they choose to create a physical model from the production database first. Eric will create the physical model within Data Studio Administrator and then send the model to Tom. Tom will then use Rational Data Architect to convert the physical model to a logical model and specify the completion code entity, the required relationships, and the appropriate attributes. Then, in consultation with Eric, he will convert the logical model to a physical model within Rational Data Architect. Eric will then use that updated physical model within Data Studio Administrator to migrate the change to a test database. Figure 1 shows how this will work:

This scenario is broken down into the following three major parts:

1. Eric uses Data Studio Administrator to create a physical model of the HR database.
2. Tom uses Rational Data Architect to transform the physical model into a logical model and to make changes to the logical model.
3. Eric uses Data Studio Administrator to migrate the changes that Tom made to the HR database.

You can try out this scenario by ensuring that you have the following prerequisites and then by completing the steps in the sections below.

Prerequisites

This scenario uses Data Studio Administrator Version 1.2 and Rational Data Architect 7.0. It is assumed that the database administrator has Data Studio Administrator installed on his or her machine, and that the data architect has Rational Data Architect installed on his or her machine. (Or, if the same person is both the database administrator and the data architect, both tools are installed on the same machine.)

In addition, the HR database for JK Enterprises must be created in a DB2 Universal Database™ V8 (DB2 UDB) or DB2 V9.1 database. You can use Data Studio Administrator to create the database. Complete the following steps to create the database and populate it with data:

1. Download the DSARDADemo.zip package to a temporary directory. Unzip the package.
2. Using Data Studio Administrator, create a new data design project.
3. Copy the createDSARDADemo.chx from the temporary directory into the SQL Scripts folder in the data design project that you created.
4. Expand the SQL Scripts folder.
5. Right-click the createDSARDADemo.chx file and click Run SQL. The Deploy Change Commands wizard starts.
6. Complete the steps in the Deploy Change Commands wizard to create and populate the HR database:
1. Verify that the appropriate database version is selected.
2. Enter your user name and password.
3. Uncheck Create Deployment Project and Script file and click Finish.

Scenario steps

Part A. Creating a physical model of the HR database

In the first part of the scenario, Eric (the database administrator) creates a new deployment script, which is a Data Studio Administrator resource that keeps track of the change management process. When he creates the deployment script for the HR database, two physical models of the database are created. One of the models is the base model, which represents the current state of the database. The second model is the target model, which is the model that is edited to specify what the database should look like after the changes.

Eric will send the base model to Tom. Eric will later apply the changes that Tom makes in the second part of this scenario to the target model.

If you were in Eric's position, you would complete the following steps to create the deployment script and physical models.

1. From the main menu bar, click File --> New --> Deployment Script. The Deployment Script wizard starts.
2. Complete the steps in the Deployment Script wizard:
   1. Enter "DSARDADemo" as the name of the project, and enter "DSARDADemo_HR" as the name of the deployment script. Click Next.
   2. Select Use Existing Connection and select JKENT. Click Next.
   3. Select Change in Place as the process, and leave Migrate table data unchecked. Click Next.
   4. Enter your user ID and password, if prompted. Click Next.
   5. Select HR as the schema. Click Next.
   6. Verify that all of the elements are selected. Click Next.
   7. Verify the default names for the base and target models that are given for the HR schema. Click Finish. In this scenario, the default names are DSARDADemo_HR_base.dbm and DSARDADemo_HR_target.dbm.

      Data Studio Administrator creates the deployment script and base and target models of the HR schema. Figure 2 shows how they appear in the Data Project Explorer when the Data Models and SQL Scripts folders are expanded:

      
      
      Figure 2. Data Project Explorer
      
      
      

3. At this point, send the base model via email to Tom, the data modeler and architect, who has Rational Data Architect installed. Alternatively, you could check the model into a version control system where Tom can check it out by using Rational Data Architect.

Part B. Transforming the physical model into a logical model and changing the logical model

In the second part of the scenario, Tom adds the physical model of the HR database that he receives from Eric into a data design project. He transforms the physical model into a logical model and then makes the following changes to the logical model using a data diagram:

• Adds a new entity, COMPLETION_CODES, which has two attributes: COMP_CODE and DESCRIPTION.
• Adds a new attribute, COMP_CODE, to the PROJECT entity.
• Creates a foreign key relationship between the COMPLETION_CODES and PROJECT entities, with COMPLETION_CODES as the parent entity.
• Deletes the attribute, DEPTNAME, from the ORGANIZATION entity because DEPTNAME in the ORGANIZATION entity is a duplicate of DEPTNAME in the DEPARTMENT entity.

After the changes are made to the logical model, Tom transforms the logical model into a physical model and sends the physical model with the changes to Eric.

If you were in Tom's position, you would complete the following steps:

1. Start Rational Data Architect.
2. Verify that the preferences are set so that the data mapping between ROWID as a logical data type and CHAR as the physical data type during model transformation is removed. To check the preferences, complete the following steps:
   1. Click Window --> Preferences to display the Preferences window.
   2. Click Data --> Transform --> Data Type Map to display the Data Type Map.
   3. If a row exists where the Logical Data Type is ROWID, delete the row.
   4. Click OK.
3. Copy the physical data model that you received in the email from Eric and paste it into a data design project.
4. Transform the physical data model into a logical model by completing the following steps:
   1. In the Data Project Explorer, under the Data Models folder in the data design project, select the physical data model DSARDADemo_HR_base.dbm, and from the main menu bar, click Data --> Transform --> Logical Data Model. The Transform to Logical Data Model wizard starts.
   2. Complete the steps in the wizard:
      1. On the Target Logical Model page, click Create new model, and then click Next.
      2. On the Logical Data Model File page, accept the defaults for the data design project for the logical model and for the name of the logical model. Click Next.
      3. On the Options page, specify "HR" as the package name. Click Next.
      4. On the Output page, which indicates that the physical model has been transformed to a logical model, click Finish to save the logical model.

      The Logical Data Model Editor is opened and displays information about the logical model. The logical model, DSADEMO_HR_base.ldm is created and displayed in the Data Models folder, as shown in Figure 3:

      
      
      Figure 3. Data Project Explorer
      
      
      
5. Create a data diagram for the logical model. You will use the data diagram to make changes to the logical model.
   1. Expand the logical model DSARDADemo_HR_base.ldm in the Data Models folder.
   2. Right-click Diagrams (under HR), and click New Overview Diagram.
   3. Select the HR schema to include all of the corresponding elements and click OK. The diagram opens in the Data Diagram Editor, as shown in Figure 4:
   
   
   Figure 4. Entity diagram of the HR schema
   
   
   
6. Add an entity for the completion codes that is named COMPLETION_CODES to the model.
   1. Right-click in the background of the Data Diagram Editor, and click Add Data Object -> Entity. An entity is added to the diagram.
   2. In the Properties view, click the General tab. Rename the entity to COMPLETION_CODES.
   3. Click the Attributes tab, and use the New button to add two new attributes. Rename the first attribute to COMP_CODE, specify that it is the primary key, and ensure its data type is CHAR(5). Rename the second attribute to DESCRIPTION and change its data type to VARCHAR(1024).

   Figure 5 shows how the Data Diagram Editor and Properties view looks after these changes:

   
   
   Figure 5. Entity diagram of the HR Schema
   
   
   
7. Create a foreign key relationship between COMPLETION_CODES and PROJECT, where COMPLETION_CODES is the parent entity and PROJECT is the child entity.
   1. From the Palette in the Data Diagram Editor, select Data --> Non-Identifying Optional. Then select the COMPLETION_CODES entity, the parent with the primary key, and drag to the PROJECT entity, the child.

   A foreign key attribute, COMP_CODE, is automatically created in PROJECT.

8. Remove the DEPTNAME attribute from the ORGANIZATION entity.
   1. Select the ORGANIZATION entity.
   2. In the Properties view, click the Attributes tab. Select DEPTNAME, and click Delete to remove it.
9. Save all of the changes to the model.
10. Transform the logical model DSARDADemo_HR_base.ldm into a physical model named DSARDADemo_HR_changes.dbm.
    1. In the Data Project Explorer, go to the Data Models folder where the logical model resides.
    2. Select the logical model DSARDADemo_base.ldm, and click Data --> Transform --> Physical Data Model. The Transform to Physical Data Model wizard starts.
    3. Complete the steps in the Transform to Physical Data Model wizard:
       1. Click Create New Model, and click Next.
       2. On the Physical Data Model File page, accept the default value for the data design project, specify DSARDADemo_HR_changes for the name for the physical model, and specify the database type. Click Next.
       3. On the Options page, verify the options for the transformation. Ensure that HR is specified as the schema name. Click Next.
       4. Click Finish to save the physical data model.
11. At this point, send the physical model with the changes via email back to Eric, the database administrator. Alternatively you could check it into your version control system where Eric can check it out from Data Studio Administrator.

Part C. Applying the changes that were sent to the HR database

In this last part of the scenario, Eric migrates the changes from the physical data model that Tom sent to the target model of the HR database. Eric then generates the change commands that are necessary to apply these changes to the actual HR database and deploys the change commands to the database.

If you were in Eric's position, you would complete the following steps:

1. Start Data Studio Administrator.
2. Copy the physical data model that Tom sent (DSARDADemo_HR_changes.dbm) and paste it into the Data Models folder of the DSARDADemo data design project in the Data Project Explorer.
3. Open the deployment script by double-clicking DSARDADemo_HR.deployxml in the SQL Scripts folder. The deployment script is displayed in the Deployment Script Editor.

   Figure 6 shows the Deployment Script Editor:

   
   
   Figure 6. Deployment Script Editor
   
   
   
4. Migrate the changes in the physical data model that Tom sent (DSARDADemo_HR_changes.dbm) over to the target model of the HR database (DSARDADemo_HR_Target.dbm):
   1. Click the Data Models tab of the Deployment Script Editor, and on the Data Models page, click Add.
   2. In the Source Models selection dialog, drill down to select the model with the changes (DSARDADemo_HR_changes.dbm), and click OK. The model is displayed in the Define Migration Sources field, as Figure 7 shows:
      
      Figure 7. Data Models page
      
      
      
   3. Select the model, and click Migrate. The Comparison Editor is displayed with the changed model on the left and the HR target model on the right.
   4. Use the up and down arrows in the tool bar to tab through the differences in the two models.

      In this demo, the Comparison Editor shows that the differences that you want to move are the deletion of the DEPTNAME column from the ORGANIZATION table, the new foreign key COMPLETION_CODE_PROJ_FK in the PROJECT table, and the new COMPLETION_CODES table.

      When you move the foreign key over, the foreign key column COMP_CODE will be moved automatically. If you move the foreign key in PROJECT before you move the COMPLETION_CODES table, the COMPLETION_CODES table is moved over automatically.

   5. Move or apply these changes to the target HR model by clicking the right arrow when the difference is selected.
   6. Select File --> Save to save all of your changes.
   7. Close the Comparison Editor.
5. Click on the Problems view, and correct any errors that the target HR model might have.

   Attention users of DB2 UDB V8.2: You will get errors that the identifiers that Rational Data Architect created for the new primary and foreign keys are too long. These identifiers cannot exceed 18 characters in DB2 V8.2. Figure 8 shows an example:

   
   
   Figure 8. Problems view
   
   
   

   Shared shell tip

   TIP: If you have Data Studio Administrator and Rational Data Architect installed in the same Eclipse IDE, you may want to ensure that the Design Suggestion validation is turned off so that you don't get a lot of warnings in Step 5.

   To turn this option off, go to Windows > Preferences > Model Validation > Constraints > Physical data model and uncheck design suggestions. Then either close and reopen the model or go to the Data Project Explorer, click on the target model database and right click on Analyze Model.

   To check for and correct the errors:

   1. Limit the problems that are displayed in the Problems view to be for the selected element only (the target HR model) by clicking the Filters icon ( ), clicking On selected element only, and clicking OK.
   2. Double-click the first error that is displayed. Click the Properties tab, and change the name of the primary key to COMP_CODES_PK.
   3. Click the Problems view and double-click the second error that is displayed. Click the Properties tab, and change the name of the foreign key to COMP_PROJ_FK.
   4. Save the changes to the target model.
   5. Click the Problems view again, and verify that the errors are now corrected.

6. Generate the change commands to implement these changes. Click the Overview tab of the Deployment Script Editor, and click Generate. The Generate Change Commands wizard starts.
7. Complete the steps in the wizard:
   1. If prompted, on the User Information page, specify your user ID and password. Click Next.
   2. On the Command Generation Options page, review the commands that are generated initially and verify that the correct changes will be made. Click Next.
   3. On the Specify Data Unload and Reload Information page, specify a data file location for the export and import commands. You must specify the full path. Specify your data file format and accept the default export and import file names. Click Next.
   4. On the Unload and Reload Information page, the tables from which data should be exported and then imported are shown. Click Next.

      In this scenario, to drop the DEPTNAME column from the ORGANIZATION table, the data needs to be exported to ensure that it is not lost. Using Data Studio Administrator, you can export the data, drop the table, re-create a new table, and then import the data into that new table. You might also have a file from which you want to import data into the new table.

   5. On the Customize Unload Commands page, click Default Query, and ensure that DEPTNAME, the column that is being deleted, is not in the SELECT list. Click Next.
   6. On the Customize Reload Commands page, verify the mapping of the export columns to the import columns of the ORGANIZATION table. Click Next.
   7. On the DB2 Maintenance Command page, check whether you want to generate commands to reorg tables, to rebind packages, to generate runstats, or to flush the package cache.

      If you choose to rebind packages, the schema for which the packages should be rebound are selected by default on the Select Schema page.

   8. On the Review Generated Change Commands page, review the commands and click Finish.
   9. If you are prompted to view the change report, you can choose to review it. The Summary of Changes report describes the changes that will be made to the database, the impact of those changes, and the actions that need to be taken due to the changes that you have modeled. There is an option to suppress this prompt, so if you have previously disable it, you would not see the question here. You can view the Summary of Changes Report from the Data Project Explorer.
   10. You can now view the generated change commands on the Change Commands page of the Deployment Script Editor.
8. Deploy the changes to the HR database. On the Overview tab of the Deployment Script Editor, click Deploy. The Deploy Change Commands wizard starts.
9. Complete the steps in the Deploy Change Commands wizard.
   1. On the Deploy Steps page, enter your user ID and password. Leave the checkboxes checked. Click Next.
   2. On the Check Base Model page, click Next.
   3. On the Change Commands page, review the change commands that will be deployed to the database. Click Finish.

   The Data Output view contains the status of the changes that you made to the HR database.

Summary

This article demonstrates one way that you can use Data Studio Administrator and Rational Data Architect together. The same physical data models can be shared between both tools, allowing for an integrated process from design and modeling to database change management. The step-by-step instructions in the article should help new users get started with using these tools together.

Back to top

Download

Information about download methods

Resources

Learn

• Learn more about Data Studio Administrator.
  
  
• Learn more about Rational Data Architect.
  
  
• Visit the developerWorks resource page for Information Management and Rational application development to read articles and tutorials and connect to other resources to expand your RDA skills.
  
  
• Browse the technology bookstore for books on these and other technical topics.
  
  

Get products and technologies

• Download a free version of Data Studio Administrator.
  
  
• Download a free trial version of Rational Data Architect.
  
  
• Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
  
  

Discuss

• Check out developerWorks blogs and get involved in the developerWorks community.
  
  
• Data Studio Forum on developerWorks.
  
  
• Data Studio Community Space on developerWorks.
  
  
• Data Studio Team Blog on developerWorks.
  
  

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

Leila Johannesen is a usability engineer at IBM Silicon Valley Lab in San Jose, CA. She works on the ease of use of the DB2 Tools family

Don Langworthy is a Software Engineer in the Silicon Valley Lab in San Jose, CA. He has been working on DB2 Change Management Expert since 2004. When Don isn't working on DB2 Change Management Expert, he's calling balls and strikes in high school baseball games.

Patti Tonello is an Information Developer for DB2 and IMS Tools group at IBM Silicon Valley Laboratory in San Jose, CA. She works on several DB2 Tools products that facilitate database administration and change management, including DB2 Change Management Expert.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Overview
• Prerequisites
• Scenario steps
• Summary
• Download
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers