Introduction

This article illustrates how Erik, a DBA at a fictitious company, JK Enterprises, uses IBM Data Studio Administrator to apply changes to the production database in response to requests he receives from his team members. Without Data Studio 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. Data Studio Administrator can help Erik perform these complex changes in little time with little effort.

Back to top

Prerequisite

This article uses Data Studio Administrator Version 2, Release 1, with Fix pack 1. If you want to follow along with Erik, you need to have Data Studio Administrator (Administrator) installed on your machine. (Download Data Studio Administrator V2.1.)

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 Data Studio Administrator, connections to the production and test databases appear automatically in the Data Source 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.

(Click here to see a larger image of Figure 1.)

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 Data Studio 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 Schema folder under the GSDBDEV database in the Data Source Explorer.
2. Right-click on the Schemas folder, and choose the option Create > Schema.
   
   Figure 2. Create schema in test database
   
   
   
3. Select Data Object Editor as the editor to use for the change.
   
   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
   
   
   

   (Click here to see a larger image of Figure 4.)

The CHANGES schema now shows up under Schemas folder in the GSDBDEV database, 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 Data Studio 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 GOSALESCT schema and extend the node to open the Tables folder.
2. Find the CUST table under the Tables folder.
3. Right-click on the CUST table, and choose the Copy action.
   
   Figure 6. Copy action on CUST table
   
   
   
4. Navigate back to the GSDBDEV database, and select the CHANGES schema.
5. Right-click on the CHANGES schema, and select the Paste option.
   
   Figure 7. Paste option on CHANGES schema
   
   
   
6. Select Create a New Change Management Script in the pop-up dialog, then click on Next.
   
   Figure 8. Change Management Script Selection dialog
   
   
   
7. By default, the schema CHANGES is selected to be added in the change management script editor. Click on Next.
   
   Figure 9. CHANGES schema is selected by default
   
   
   

   Note: Although Erik works with just one schema in this scenario, you can work with more than one schema at a time if needed.

8. Select Copy database objects and data, then click on Finish.
   
   Figure 10. 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. "Copy dependent database objects" is checked so dependent objects, if any, are also copied.

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 Data Studio Administrator (see Figure 11). This editor contains the CUST table that was pasted into the CHANGES schema.

(Click here to see a larger image of Figure 11.)

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 12. MOBILEPHONE column added to CUST table
   
   
   

   (Click here to see a larger image of Figure 12.)

5. Click on the Preview Command link to see the generated commands for the table paste operation and for adding the new column.
   
   Figure 13. Generated DDL in commands section
   
   
   

   (Click here to see a larger image of Figure 13.)

   Optional: A dialog appears asking you if you would like to see a formatted report outlining the proposed changes is displayed. Click Yes or No, depending on your preference. Viewing the report does not affect the generation of the DDL.

   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 Customize 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 customize button and change the import option to LOAD.

   
   
   Figure 14. Change table space
   
   
   

   (Click here to see a larger image of Figure 14.)

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 anywhere in the Data Project Explorer, and select New > Change Management Script.
   
   Figure 15. Create new change management script
   
   
   
2. Name the script CHANGE TO PRODUCTION, then click on Next.
   
   Figure 16. Specify script name
   
   
   
3. Select the connection to the GSDB database, and click on Next.
   
   Figure 17. Select GSDB database connection
   
   
   
4. Select the GOSALESCT schema, and click onFinish.
   
   Figure 18. Select schema for change management script
   
   
   

   Note: Select the same schema that was used to create the objects in the GSDB database.

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

(Click here to see a larger image of Figure 19.)

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 Data Studio Administrator. The compare and migrate capability allows Erik to visually compare his test database against his product 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 20. 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 21. GSDBDEV database connection is selected
   
   
   
3. Add a mask to the CHANGES schema:
   1. In the Masks tab, select SCHEMA as the Database Object, CHANGES as In Mask, and GOSALESCT as Out Mask, then click on Add Mask.
   
   
   Figure 22. Masking GOSALESCT schema to CHANGES
   
   
   
4. 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 23. Add Ignores
   
   
   
5. 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 24. Locate MOBILEPHONE under CUST table
   
   
   
   
   Figure 25. MOBILEPHONE column migrated to GSDB database
   
   
   
6. The MOBILEPHONE column is now displayed in the object list view of the change management script editor.
   
   Figure 26. MOBILEPHONE column is shown in the object list view
   
   
   
7. Click on the Preview Commands link to verify changes in the Commands Section.
   
   Figure 27. Generated commands for migrated table and column
   
   
   

   (Click here to see a larger image of Figure 27.)

8. 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 Data Project Explorer by exploring the CUST table and looking in the columns folder.

Back to top

Conclusion

Eric has now addressed the request provided by his company. By using Data Studio Administrator, he was easily able to accomplish this task with little manual effort. Data Studio 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 Data Studio Administrator 2.1" (developerWorks, December 2008): Find out all the new capabilities in Data Studio Administrator.
  
  
• "Oops! Restoring your database with Data Studio 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 Data Studio 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

• Data Studio Team blog: Find advice, hints and tips, and lessons learned from a team of Data Studio 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

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.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

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