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.

Back to top

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.

Back to top

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.

Back to top

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
   
   
   
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
   
   
   
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
   
   
   

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

Back to top

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
   
   
   

   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
   
   
   
6. Select Paste by using Change Management in the pop-up dialog, and then click on Next.
   
   Figure 8. Paste Options dialog
   
   
   
7. Select Copy database objects and data, then click on Finish.
   
   Figure 9. 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.

Back to top

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.

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

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:
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
   
   
   
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
   
   
   

   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
   
   
   

6. 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.

Back to top

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
   
   
   

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

Back to top

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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   
   
   Figure 21b. 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
   
   
   
8. Click on the Preview Commands link to verify changes in the Commands Section.
   
   Figure 23. 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.

Back to top

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.

Back to top

Download

Information about download methods

Resources

Learn

• "IBM Data Studio software: The big picture" (developerWorks, December 2008): Understand how Data Studio Administrator fits in with the rest of the Data Studio portfolio.
  
  
• "Understand IBM Data Studio, Version 2 software packaging" (developerWorks, February 2009): Understand Data Studio packaging, including the free administrative capabilities available in the Data Studio Administrator download.
  
  
• "What's new and exciting in IBM Optim Database Administrator 2.1" (developerWorks, December 2008): Find out all the new capabilities in Optim Database Administrator.
  
  
• "Oops! Restoring your database with Optim Database Administrator" (developerWorks, April 2009): Follow the story of a typical DBA on a not so typical day as he puts the point-in-time recovery capability of Optim Database Administrator 2.1 to the test.
  
  
• developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• Technology bookstore: Browse for books on these and other technical topics.
  
  

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.
  
  

About the authors

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

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.

Arlan 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 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.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Prerequisite
• Erik's database change request (scenario overview)
• Create a schema in the test database
• Create a copy of CUST table in the test database
• Add MOBILEPHONE to CUST table using Change Management Script Editor
• Migrate the changes from the test database (GSDBDEV) back to the production database (GSDB)
• Migrate CUST changes to production using the "Compare and Migrate" wizard
• Conclusion
• Download
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

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