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 Optim Database Administrator is a database administration tool that can be used on its own as well as with IBM InfoSphere 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 stay on the same page.

Both Optim Database Administrator and InfoSphere Data Architect use the Eclipse framework. Teams that use both products in their shops can also leverage Eclipse Team components to control the flow of information. Furthermore, both InfoSphere Data Architect and Optim Database 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 Optim Database Administrator and InfoSphere Data Architect. Tom, the data modeler at JK Enterprises, is using InfoSphere Data Architect, while Eric, the DBA, is using Optim Database 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. Tom will create the physical model within InfoSphere Data Architect. He will convert that 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 InfoSphere Data Architect. Eric will then create a new database change using that updated physical model within Optim Database 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 tasks:

1. Tom uses InfoSphere Data Architect to create a physical model of the HR database.
2. Tom uses InfoSphere Data Architect to transform the physical model into a logical model. He makes changes to the logical model, and then transforms that model back into a physical data model.
3. Eric uses Optim Database 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 Optim Database Administrator Version 2.2.3 and InfoSphere Data Architect 7.5.3. For simplicity, it is assumed that Optim Database Administrator, InfoSphere Data Architect, and the database instance are all on the same machine.

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

1. Using Optim Database Administrator, create the JKENT database, and then switch to the Database Administration perspective.
1. At the top of the Administration Explorer view, from the tool bar menu, click New.
2. Select New Database to launch the New Database wizard.
3. Click Next. On windows the default instance is DB2 and the default port is 50000.
4. Click Finish after you have finished filling in the fields.
   
   Figure 2. New Database
   
   
   
2. The New Database editor opens, name the database JKENT and fill in the location (on Windows the default location is c:\). Click Run.
3. Once the database is successfully created, in the Administration Explorer, you will see localhost under the All databases folder. Under localhost will be the DB2 instance, under the instance you'll find the JKENT database.
4. Download the ODAIDADemo.zip package to a temporary directory. Unzip the package.
5. In the Administration Explorer toolbar click on the New tool menu again and this time select New SQL Script to open the SQL and XQuery Editor. There's a tool bar at the top of the editor. If you see No Connection in the editor tool bar, click it and select the JKENT database.
1. In the editor tool bar click the Import action and select the createODAIDADemo.sql script from the temporary directory and click Open.
2. Again in the editor tool bar click the Run SQL tool action.
6. You may need to enable the Change Management capability. From the File menu, click New. If you don't see Change Management Script, you must first enable change management by doing the following:
1. From the Windows menu, click Preferences, then expand General and click Capabilities.
2. Click the Advanced button.
3. Expand Data, and click the Change Management Capability check box.

Scenario steps

Part A. Creating a physical model of the HR database

In this first part Tom (the Data Architect) will transform a database into a physical data.

To perform the same tasks Tom did, do the following tasks:

1. In the main toolbar, click on the down arrow next to the Task Navigator and select Data Modeling.
2. In the Task Launcher, click the Design tab. If the Task Launcher is not already open, launch it by choosing Optim Task Launcher from the main Help menu.
3. From the Design tab, select the Reverse engineer a database to a model use case. Follow the instructions on the right-hand side to create a data design project called ARCH_JKENT.
4. Create a new physical data model inside the ARCH_JKENT project called ARCH_JKENT_HR by reverse engineering the JKENT database. Reverse engineer the HR schema, and then click Finish.

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

In the second part of the scenario, Tom 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 InfoSphere 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. From the Windows menu, click Preferences.
   2. Click Data Management, Transform, and then Data Type Map to display the Data Type Map.
   3. If a row exists where the Logical Data Type is ROWID, then delete the row.
   4. Click OK.
3. Transform the physical data model into a logical model by completing the following steps:
   1. To start the Transform to Logical Data Model wizard, from the Data Project Explorer click the Data Models folder in the data design project, select the physical data model ARCH_JKENT_HR.dbm, and then from the main menu bar, click Data, Transform, and then Logical Data Model.
   2. Complete the steps in the wizard:
      1. From the Target Logical Model page, click Create new model, and then click Next.
      2. From 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. From the Options page, specify HR as the package name, then click Next.
      4. From 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, ARCH_JKENT_HR.ldm is created and displayed in the Data Models folder, as shown in Figure 3.

      
      
      Figure 3. Data Project Explorer
      
      
      
4. 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 ARCH_JKENT_HR.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 then click OK. The diagram opens in the Data Diagram Editor, as shown in Figure 4.
   
   
   Figure 4. Entity diagram of the HR schema
   
   
   
5. 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, and then Entity to add an entity to the diagram.
   2. In the Properties view, click the General tab. Rename the entity to COMPLETION_CODES.
   3. Click the Attributes tab, and click New 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
   
   
   
6. 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, click Data, and then click Non-Identifying Optional.
   2. Select the COMPLETION_CODES entity and the parent with the primary key, and then drag them to the PROJECT entity, which is the child.

   A foreign key attribute, is automatically created in PROJECT.

7. 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.
8. Save all of the changes to the model.
9. Transform the logical model ARCH_JKENT_HR.ldm into a physical model named ARCH_JKENT_HR_changes.dbm.
   1. In the Data Project Explorer, go to the Data Models folder where the logical model resides.
   2. To start The Transform to Physical Data Model wizard, select the logical model ARCH_JKENT_HR.ldm, click Data, Transform, and then click Physical Data Model.
   3. Complete the following steps in the Transform to Physical Data Model wizard:
      1. Click Create New Model, and then click Next.
      2. From the Physical Data Model File page, accept the default value for the data design project, specify ARCH_JKENT_HR_changes for the name for the physical model, and specify the database type. Click Next.
      3. From 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.
10. 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 Optim Database Administrator.

Part C. Applying the changes 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 JKENT database. Eric (the Database Administrator) creates a new change management script, which is an Optim Database Administrator resource that keeps track of the change management process.

Eric then generates the change commands that are necessary to apply these changes to the JKENT database. He deploys the change commands to the database.

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

1. Start Optim Database Administrator.
2. From the main menu bar, click Help and then select Optim Task Launcher.
1. Click the Administration Tab and then select Migrate using the compare editor.
2. Click Yes if you are asked to change the Database Administration perspective.
3. Follow the instructions in the help view.
3. At this point, you may need to enable the Change Management capability.
1. From the File menu, click New. If you don't see the Change Management Script, then you must first enable change management.
2. To enable change management, from the Windows menu, click Preferences.
3. From the Preferences wizard, expand General and click Capabilities.
4. Click the Advanced button. Expand Data and check Change Management Capability.
4. In the Administration Explorer expand the JKENT database. Select the Change Management folder, and click New Database Change.
5. Copy the physical data model that Tom sent (ARCH_JKENT_HR_changes.dbm), and paste it into the Data Models folder of the JKENT data design project in the Data Project Explorer.
6. Open the change management script by double-clicking JKENT.changexml in the SQL Scripts folder. The change management script is displayed in the Change Management Script Editor as shown in Figure 6.
   
   Figure 6. Change Management Script Editor
   
   
   
7. Migrate the changes in the physical data model that Tom sent (ARCH_JKENT_HR_changes.dbm) over to HR.
   1. From the Change Management menu of the JKENT.changexml editor, select Compare and Migrate Objects. If it isn't enabled, make sure the change management script editor has focus.
   2. From the Migrate Objects to Target Model wizard, located the Migration Source page and select Physical data model. In the selection dialog, drill down to find the model with the changes, for example, ARCH_JKENT_HR_changes.dbm, and click Next.
   3. From the Choose Source Objects page, select the schemas folder, check the HR schema, and click Add.
      
      Figure 7. Choose Source Objects page of the Migrate Objects to Target Model wizard.
      
      
      
   4. Click Next until the Comparison Editor is displayed in a dialog window with the changed model on the left, and the HR target model on the right.
   5. 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.

   6. Move or apply these changes to the target HR model by clicking the right arrow when the difference is selected.
   7. Once the changes have been incorporated, click Finish.

      Shared shell tip

      TIP: If you have Optim Database Administrator and InfoSphere 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, from the Windows menu, click Preferences, Model Validation, Constraints, Physical data model, and clear the design suggestions check box. Then either close and reopen the model or go to the Data Project Explorer, click the target model database, and then right click Analyze Model.

8. Generate the change commands to implement these changes. From the Change Management Script Editor, click the Overview tab, and then click Preview Commands to start the Generate Change Command dialog to view the generated change commands.
9. Deploy the changes to the HR database. From the Commands section of the Change Management Script Editor, click Run to start the Run Change Commands wizard.
10. Click Finish to deploy the commands.

    The SQL Results view contains the status of the changes that you made to the HR database.

Back to top

Summary

This article demonstrates one way that you can use Optim Database Administrator and InfoSphere 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 Optim Database Administrator.
  
  
• Learn more about InfoSphere 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 Optim Database Administrator.
  
  
• Download a free trial version of InfoSphere 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.

Jeff Ruggles is a product manager in the Optim Database Tools group at IBM Silicon Valley Laboratory in San Jose, CA. He has worked on the product since 2003. When not working with customers, or on Optim tools, Jeff enjoys rock climbing and trail running.

Rate this article

Comments

Back to top

Table of contents

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

Next steps from IBM

• Try: Data Studio
• Demo: Use Ajax with DB2
• Briefing: Virtual tech briefings on integrated data management
• Community: DB2
• Buy: Data Studio

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers