Data migration and change management using Optim Database Administrator V2.2.3

IBM Optim Database Administrator for DB2 for Linux, UNIX, and Windows provides DBAs with powerful ways to manage and control complex changes made to their databases, while preserving the underlying data. In Version 2.2.3, it's even easier to do this using new copy and paste capabilities for migration. In this article, follow along with a DBA who uses Optim Database 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 environment.

[28 Oct 2010: This article has been updated from its original April 2009 publication to include the renaming of the product from Data Studio Adnministrator to Optim Database Administrator, and to include details on various updates and changes to the product. --Ed.]

Tony Leung (leungtk@us.ibm.com), Architect, IBM  

Tony Leung photoTony Leung is an architect in the Optim Database Tools group at IBM Silicon Valley Laboratory in San Jose, CA.



Jeff Ruggles (jeffrer@us.ibm.com), Product Manager, IBM

Jeff Ruggle's photoJeff 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.



Arlan Finestead (arlanf@us.ibm.com), Software Engineer, IBM

Arlan FinesteadArlan is a software engineer with IBM, working on the Data Studio Administrator product. For the past 8+ years at IBM, he has been involved in tool and language development for Database administration.



Sangeetha Srinivasan (srinivsa@us.ibm.com), Software Engineer, IBM

Sangeetha SrinivasanSangeetha Srinivasan is a developer in IBM Silicon Valley Lab. She has worked on the Data Studio Administrator product since 2006. Prior to this position, she worked on DB2 for z/OS native stress testing. She holds a Masters in computer engineering.



28 October 2010 (First published 16 April 2009)

Also available in Chinese

Introduction

This article illustrates how Erik, a DBA at a fictitious company, JK Enterprises, uses IBM Optim Database Administrator to apply changes to the production database in response to requests he receives from his team members. Without Optim Database Administrator, Erik would have to manually write scripts to migrate and test the changes in the test system, which could be both time consuming and error prone. Optim Database Administrator can help Erik perform these complex changes in little time with little effort.


Prerequisite

This article uses IBM Optim Database Administrator Version 2.2.3. If you want to follow along with Erik, you need to have Optim Database Administrator installed on your machine. (Download Optim Database Administrator V2.2.3.)

Note: This scenario uses the GSDB database to simulate JK Enterprises's production database. You need to also use the sample database named GSDB. Follow these steps to create the database:

  1. Get the zip file from the Download section of this article, and extract the GSDB_Database.sql file from it.
  2. Open a DB2 command window.
  3. Navigate to the location where you saved the GSDB_Database.sql file.
  4. Enter: db2 -td~ -f GSDB_Database.sql

To follow along with the scenario, you need to also create a "test" database. To do so, use the following command in the db2 Command Line Processor (CLP): - db2 CREATE DATABASE GSDBDEV USING CODESET UTF-8 TERRITORY US

Upon launching Optim Database Administrator, connections to the production and test databases appear automatically in the Administration Explorer, as shown in Figure 1. 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 1. Connections in Administration Explorer
Connections in Administration Explorer

Erik's database change request (scenario overview)

Erik, a DBA at JK Enterprises, receives a request to add mobile numbers to all the customers of the company. In order to accomplish this, he first makes a copy of the production system to a test system where he can make and test his changes. After he tests the changes, he propagates the changes back to the production database. The following steps show how Erik can easily accomplish these tasks using Optim Database Administrator.


Create a schema in the test database

To accomplish the task of making the proposed change request to the production system, Erik first needs to set up a test environment in which he can make his changes without risk of impacting the production system. The change request focuses around a single schema in the production database. To mirror this in the test database, Erik decides to create a schema named CHANGES in the test database GSDBDEV to hold the change he will be making.

To create a schema named CHANGES, perform the following steps:

  1. Locate the Schemas folder under the GSDBDEV database in the Administration Data Explorer.
  2. Right-click on the Schemas folder, and choose the option New Schema.
    Figure 2. Create schema in test database
    Create schema in test database
  3. Select Data Object Editor as the editor to use for the change. (Note: Don't worry if you don't see this dialog. Just proceed to the next step. The dialog does not pop-up when the chanage mangement capability is disabled.)
    Figure 3. Data Object Editor is selected as the editor to use
    Data Object Editor is selected as the editor to use
  4. Enter CHANGES in the Name field.
  5. Click on Preview DDLto show the DDL to be executed.
  6. Click on Run DDL to create the new schema.
    Figure 4. Creating schema CHANGES in Data Object Editor
    Creating schema CHANGES in Data Object Editor

The CHANGES schema now shows when the Schemas folder is selected, as shown in Figure 5:

Figure 5. CHANGES schema in test database
CHANGES schema in test database

Create a copy of CUST table in the test database

With the new schema created in the test GSDBDEV database, Erik uses the new copy and paste capability in Optim Database Administrator to make a copy of the CUST table from the production GSDB database into the test GSDBDEV database. The copy and paste action does the following:

  • Automatically creates a change management script if one is needed
  • Copies the database objects from the production system into the change management editor for manipulating
  • Optionally sets up the necessary data preservation commands that copy data associated with the database objects from the production system into the test database

To copy the CUST table from GSDB to GSDBDEV, perform the following steps:

  1. Navigate to the Tables folder under database GSDB.
  2. Find the CUST table in the list of tables.
  3. Right-click on the CUST table, and choose the Copy action.
    Figure 6. Copy action on CUST table
    Copy action on CUST table

    Enabling Change Management

    If you do not see the "Paste using change management" option, change management may be disabled. To enable the Change Management capability, go to Windows > Preferences. Inside the "Preferences" wizard, expand General > Capabilities. Click the Advanced button. Expand Data, and check Change Management Capability.

  4. Navigate back to the GSDBDEV database, select the Schemas folder, and select the CHANGES schema from the list of schemas.
  5. Right-click on the CHANGES schema, and select the Paste option.
    Figure 7. Paste option on CHANGES schema
    Paste option on CHANGES schema
  6. Select Paste by using Change Management in the pop-up dialog, and then click on Next.
    Figure 8. Paste Options dialog
    Paste Options dialog
  7. Select Copy database objects and data, then click on Finish.
    Figure 9. Copy options
    Copy options

    Note: It's not always required, or even allowed, in some companies to copy data from production to a test system. If you just want to copy the data objects, select the Copy database objects only option.


Add MOBILEPHONE to CUST table using Change Management Script Editor

Once the paste operation completes, a new change management script editor named GSDBDEV.changexml is displayed within Optim Database Administrator (see Figure 10). This editor contains the CUST table that was pasted into the CHANGES schema.

Figure 10. GSDBDEV.changexml with CUST table
GSDBDEV.changexml with CUST table

Let's now modify the CUST table by adding an additional column MOBILEPHONE.

Data Project Explorer projects

For each change operation, you must have a Data Project Explorer project. A project holds various Optim Database Administrator files and resources. A change management script file, ending with the extension ".changexml", is used by the Change Management Script editor to record and track the changes made to the database objects. In a new or "clean" workspace, a new Data Project Explorer project is automatically created by the paste operation to hold your changes. Each change management script file is linked to a specific database and schema(s).

To add the MOBILEPHONE column to the CUST table, perform the following steps:

  1. Select the CHANGES.CUST object in the Change Management Editor's "Objects to be Changed" list, as shown in Figure 11.
  2. Select the Columns tab in the properties section on the right side of the editor.

    Note: The Columns tab shows you all of the columns currently associated with this table. You can manipulate the columns using this tab.

  3. Click on the new column icon (a diamond with a plus sign) to create a new column: new column icon
  4. Name the column by changing the default name from Column1 to MOBILEPHONE ,and change the type to varchar with length of 32.

    Note: In some situations, the UI does not immediately refresh with the new column information. Click on the General tab, then go back to the Columns tab, and the new column information will be displayed.

    Figure 11. MOBILEPHONE column added to CUST table
    MOBILEPHONE column added to CUST table
  5. Click on the Preview Command button to see the generated commands for the table paste operation and for adding the new column.
    Figure 12. Generated DDL in commands section
    Generated DDL in commands section

    While viewing the DDL, if you notice changes that should be made before running the script (such as table space container paths that should be changed), you can perform these changes in the editor, or by modifying the Data Project Explorer model object. Additionally, the Data Options button lets you customize how the data is exported/imported (important especially when dealing with XML data) and what types of additional commands are generated along with the DDL (such as Runstats). If a dialog is presented stating there are data preservation errors, choose the Data Options button, and change the import option to LOAD.

    Figure 13. Change import option to LOAD
    Change import option

Impact analysis

If your database applications are developed in Java technology and your developers are using Optim Development Studio, it's much easier to tell if a database change will impact a particular application. For example, using the SQL outline, you can easily find which queries are using the CUST table and look at the associated Java source code to see if there are queries that would be affected (such as SELECT *) by the addition of a new column.

  1. Click on the Run button to run the commands. The changes are now applied to the GSDBDEV database.

    Optional: To validate successful execution of the generated commands, look in the Messages Section of the change management script editor.

Erik is now at a point where he can test the changes made to the test GSDBDEV database. Since a new column is being added to the table, all applications that may be impacted by this change should be tested carefully.


Migrate the changes from the test database (GSDBDEV) back to the production database (GSDB)

For the purposes of this article, let's assume Erik has done the necessary testing and feels the changes can be incorporated back into the production GSDB database.

To help manage the changes from the GSDBDEV database into the production GSDB database, Erik creates a new change management script to hold his changes.

To create a new change management script on the GSDB database, perform the following steps:

  1. Right-click on the Change Management Scripts folder under GSDB, and select New Database Change.
    Figure 14. Create new change management script
    Create new change management script

A new change management script editor is displayed, as shown in Figure 15. You'll use this editor in the next section.

Figure 15. Change management script editor on GSDB database
Change management script editor on GSDB database

Migrate CUST changes to production using the "Compare and Migrate" wizard

To propagate the specific changes from the test GSDBDEV database into the production GSDB database, Erik uses the compare and migrate capability in Optim Database Administrator. The compare and migrate capability allows Erik to visually compare his test database against his production database. He is able to selectively choose which changes/differences between the two and reconcile them as he sees fit. In this case, he will locate the changes made to the CUST table and merge them into the production database.

  1. Select Change Management > Compare and Migrate Objects from the top menu bar.
    Figure 16. Compare and Migrate Objects menu
    Compare and Migrate Objects menu
  2. Select the GSDBDEV test database connection, which has the changes you want to migrate, then click on Next.
    Figure 17. GSDBDEV database connection is selected
    GSDBDEV database connection is selected
  3. Select the Schemas folder, and then select the CHANGES schema as the source schema.
  4. Click on the Add button to add the schema CHANGES to the list of objects to compare and migrate, and then click on Next.
    Figure 18. Choose the CHANGES schema as the source
    Choose Objects to Target Model
    1. In the Masks tab, select SCHEMA as the Database Object, CHANGES as In Mask, and GOSALESCT as Out Mask, and then click on Add Mask.
      Figure 19. Masking CHANGES schema to GOSALESCT
      Masking CHANGES schema to GOSALESCTCHANGES
  5. Add objects to the Ignores tab to reduce the number of changes displayed in the compare wizard:
    1. Click on the Ignores tab from the Ignore Database Objects drop-down menu.
    2. Select BPNAME, then click on Add Ignore.
    3. Repeat the same steps for TABLESPACES, CONTAINER, and AUTHORIZATION.
    4. After you have selected BPNAME, TABLESPACES, CONTAINER, and AUTHORIZATIONS as database objects to ignore, click on Next.
    Figure 20. Add Ignores
    Add Ignores
  6. Locate the MOBILEPHONE column under the CUST table in the comparison editor, click on the Copy from Left to Right icon, then click on Finish. This migrates the MOBILEPHONE column to the CUST table in the production database (GSDB).
    Figure 21. Locate MOBILEPHONE under CUST table
    Locate MOBILEPHONE under CUST table
    Figure 21b. MOBILEPHONE column migrated to GSDB database
    MOBILEPHONE column migrated to GSDB database
  7. The MOBILEPHONE column is now displayed in the object list view of the change management script editor.
    Figure 22. MOBILEPHONE column is shown in the CUST table
    MOBILEPHONE column is shown in the CUST table
  8. Click on the Preview Commands link to verify changes in the Commands Section.
    Figure 23. Generated commands for migrated table and column
    Generated commands for migrated table and column
  9. Click on Run to deploy the changes to the GSDB production database.

Once the changes are deployed, you can view your newly deployed changes in the Administration Explorer Explorer by looking at the columm properties of the CUST table.

Figure 24. MOBILEPHONE column in the GSDB production database
GSDB production database with MOBILEPHONE column circled

Conclusion

Eric has now addressed the request provided by his company. By using Optim Database Administrator, he was easily able to accomplish this task with little manual effort. Optim Database Administrator handles complex situations and contingencies by providing easy-to-use wizards that walk you through every step of the process.


Download

DescriptionNameSize
GSDB sample database for this articleGSDB_database.zip33KB

Resources

Learn

Get products and technologies

  • Data Studio Administrator: Download the free trial version of Data Studio Administrator for DB2 for Linux, UNIX, and Windows.
  • DB2 Express-C: Now you can use DB2 for free. Download DB2 Express-C, a free version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy application.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

  • Optim Team blog: Find advice, hints and tips, and lessons learned from a team of Optim experts and their experiences working with customers around the world.
  • Data Studio Community space: Contribute and learn from the central hub of information and conversation about Data Studio and related products and technologies.
  • Participate in developerWorks blogs and get involved in the developerWorks community.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=382944
ArticleTitle=Data migration and change management using Optim Database Administrator V2.2.3
publish-date=10282010