Introduction

Periodically migrating a database to newer and faster hardware is a known requirement. As a database administrator (DBA), you might wonder whether the process you currently follow is the most efficient in terms of managing the change. Are data migration tasks cumbersome and taking too long? This article describes how to use Data Studio Administrator 2.1 and DB2 High Performance Unload for Linux®, UNIX®, and Windows® 4.1 to show simple yet powerful solutions to carry out these tasks.

Data Studio Administrator

Data Studio Administrator is a key tool that includes basic administration capabilities, such as commands and utilities, but that specializes in complex change management. Data Studio Administrator helps you to:

• Keep track of your changes
  
  
• Work together with other DBAs who contribute different changes
  
  
• Audit and manage the history of contributed changes
  
  
• Reverse or undo any changes no longer needed

With Data Studio Administrator 2.1, you can:

• Use DB2 High Performance Unload from within the Data Studio Administrator user interface to unload tables in your database
  
  
• Run your custom DB2 High Performance Unload control files from Data Studio Administrator

DB2 High Performance Unload

DB2 High Performance Unload helps you meet service level agreements with high-speed unload capability. Because DB2 High Performance Unload performs the loading and the unloading steps in parallel by using named pipes, the migration process is fast. System resources are used efficiently. In addition, in many scenarios, DB2 High Performance Unload can work directly against the containers, avoiding the overhead of going through DB2.

DB2 High Performance Unload can also repartition data and help speed backup and recovery operations by enabling unload and recovery of single tables from a full database backup. These capabilities make it easier to respond to a variety of outages.

This article uses two scenarios to illustrate the power of both products in performing systematic change management and fast data preservation.

Scenario A: Unloading a table using DB2 High Performance Unload from Data Studio Administrator

This example explains the steps involved to unload data using DB2 High Performance Unload from Data Studio Administrator that you can load using the DB2 LOAD utility. Without Data Studio Administrator, you would have to issue the unload command from the operating system command line. For more complex unloads, you might be required to construct a control file and then pass it to the DB2 High Performance Unload command line with the –f option.

Scenario overview

JK Enterprises, the fictional company in this example, needs to transfer its customer accounts to a new database. Erik, the DBA at JK Enterprises, is assigned the task. He needs to unload the CUST table in GOSALESCT schema in the production database GSDB, and he needs to load it into the new database GSDBNEW. Erik needs to perform this task as quickly as possible to minimize impact on the production schedule.

This scenario is broken down into the following high level steps:

1. Create the desired database, schema, and table on the new server
   
   
2. Unload the data using High Performance Unload task assistant
   
   
3. Use DB2 LOAD task assistant to load the data to the test database

Prerequisites

Follow these steps to install and configure an environment you can use to follow along with Erik's example scenario:

1. Install Data Studio Administrator Version 2 Release 1 with Fix Pack 1 (see Resources if you need to download the trial version). If you have not installed Fix Pack 1 onto your Data Studio Administrator Version 2 Release 1 system, see Resources.
   
   
2. Install a supported version of DB2. If you do not have DB2, see Resources to download DB2 Express-C, which is a no-charge version of DB2 Express Edition.
   
   
3. Follow these steps to create a sample database named GSDB:
   1. Download the GSDB_database.zip file from the Download section of this article, and extract the GSDB_Database.sql file from it. See "Oops! Restoring your database with Data Studio Administrator" (developerWorks, 2009 Apr) if you need help installing the download.
      
      
   2. Open a DB2 command window.
      
      
   3. Navigate to the location where you saved the GSDB_Database.sql file.
      
      
   4. Enter the following command: db2 -td~ -f GSDB_Database.sql
   
   
4. Install DB2 High Performance Unload for Linux, UNIX, and Windows 4.1 on both the test and development servers.

Create the desired database, schema, and table on the new server

Follow these steps to create a sample database named GSDBNEW:

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

Once you have created the database, use Data Studio Administrator to create a connection to the GSDBNEW database following these steps:

1. Right-click the Database connections folder in the Data Source explorer, and click New, as shown in Figure 2.

2. Select DB2 for Linux, UNIX, and Windows for the Database Manager, and provide the instance details and authentication credentials based on your environment, as shown in Figure 3.

3. Click Finish. A new connection profile GSDBNEW is added to the Data Source explorer.

Unload the data using High Performance Unload task assistant

1. From the Data Source explorer, drill down to the tables in the GOSALESCT schema in the GSDB database.
   
   
2. Right-click the CUST table, and click High Performance Unload, as shown in Figure 4.

The DB2 High Performance Unload task assistant opens.

3. Specify C:\HPU_ControlFiles for the Data server data file directory, as shown in Figure 5.

4. Click the Customize Query tab, and select all the columns available in the table, as shown in Figure 6.

5. Click Preview Command, and review the generated DDL, as shown in Figure 7.

6. When you are satisfied with the commands, click Run to execute them.

Use DB2 LOAD task assistant to load the data to the test database

1. From the Data Source explorer, drill down to the CUST table in the GOSALESCT schema in the GSDBNEW database.
   
   
2. Right-click the CUST table, and click Load, as shown in Figure 8.

The Load task assistant window opens.

3. Click the Files tab in the Import table task assistant, and use the data file you created.
   
   
4. Click Preview command, and review the generated DDL, as shown in Figure 9.

5. When you are satisfied with the commands, click Run to execute them.

Back to top

Scenario B: Migrating a schema

Scenario overview

JK Enterprises has purchased new hardware to set up a test server, which will be called GSDBTEST. Erik has been assigned the task to migrate the GOSALES database schema (tables and data) from its existing development database (GSDBDEV) to GSDBTEST.

This scenario is broken down into the following high level steps:

1. Create the database and schema on the new server
   
   
2. Use the Data Studio Administrator change management feature to copy the tables to the new server
   
   
3. Use the DB2 High Performance Unload task assistant to migrate the data

Create the database and schema on the new server

Follow these steps to create a sample database named GSDBTEST:

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

Once you have created the database, use Data Studio Administrator to create a connection to the GSDBTEST database following these steps:

1. Right-click the Database connections folder in the Data Source explorer, and click New, as shown in Figure 10.

2. Select DB2 for Linux, UNIX, and Windows, and provide the instance details and authentication credentials based on your environment, as shown in Figure 11.

3. Click Finish. A new connection profile GSDBTEST is added to the Data Source explorer.

Use the Data Studio Administrator change management feature to copy the tables to the new server

In the first part of the scenario, Erik copies the tables in the GOSALES schema using Data Studio Administrator’s copy-and-paste function. In order to keep track of the changes, a change management script is used, which is a Data Studio Administrator resource that keeps track of the change management process.

Complete the following steps to copy the tables in the GOSALES schema and paste them in the GOSALESTEST database:

1. From the Data Source explorer, drill down to the tables in the GOSALES schema in the GSDB database.
   
   
2. Select all the tables in the schema.
   
   
3. Right-click the selected tables, and click Copy, as shown in Figure 12.

4. From the Data Source explorer, navigate to the GSDBTEST database, and drill down to the GOSALES schema.
   
   
5. Right-click the GOSALES schema folder, and click Paste, as shown in Figure 13.

The Change Management Script task assistant starts. Complete the following steps in the Change Management Script task assistant:

6. Select Create a New Change Management Script, and click Next. The GOSALES schema is selected by default.
   
   
7. Click Next.
   
   
8. Select Copy database objects only, as shown in Figure 14.

9. Click Finish. The results are similar to Figure 15.

10. Click Preview Command, and review the generated DDL, as shown in Figure 16.

11. When you are satisfied with the commands, click Run to execute them.

On successful execution, all the tables you copied will be created in the GOSALES schema in the GSDBTEST database, as shown in Figure 17.

Use the DB2 High Performance Unload task assistant to migrate the data

With DB2 High Performance Unload 4.1, you can use a single DB2 High Performance Unload control file to unload the data, transfer it, and load it into the new database. As described in Scenario A, Data Studio Administrator provides a DB2 High Performance Unload task assistant that helps you create a simple control file to unload data from a single table. However, in order to migrate the data for all the tables in the schema, you need to create a control file that you can invoke from Data Studio Administrator. Complete the following steps to create a control file:

1. Create the control file in a text editor by modifying the template in Listing 1. Be sure to modify the file to ensure that the TARGET ENVIRONMENT and WORKING IN lines are modified to reflect your environment.

GLOBAL CONNECT TO GSDB
UMASK "022";

MIGRATE TABLESPACE 
SELECT * FROM GOSALES.INVENTORY_LEVELS;
TARGET ENVIRONMENT (INSTANCE "NEWINST" ON "NEWSERVER" IN GSDBTEST)
WORKING IN ("user_dir_on_new_system")
FORMAT MIGRATION;

MIGRATE TABLESPACE 
SELECT * FROM GOSALES.ORDER_METHOD;
TARGET ENVIRONMENT (INSTANCE "NEWINST" ON "NEWSERVER" IN GSDBTEST)
WORKING IN ("user_dir_on_new_system")
FORMAT MIGRATION;

MIGRATE TABLESPACE 
SELECT * FROM GOSALES.PRODUCT;
TARGET ENVIRONMENT (INSTANCE "NEWINST" ON "NEWSERVER" IN GSDBTEST)
WORKING IN ("user_dir_on_new_system")
FORMAT MIGRATION;

MIGRATE TABLESPACE 
SELECT * FROM GOSALES.PRODUCT_BRAND;
TARGET ENVIRONMENT (INSTANCE "NEWINST" ON "NEWSERVER" IN GSDBTEST)
WORKING IN ("user_dir_on_new_system")
FORMAT MIGRATION;

MIGRATE TABLESPACE 
SELECT * FROM GOSALES.PRODUCT_COLOR_LOOKUP;
TARGET ENVIRONMENT (INSTANCE "NEWINST" ON "NEWSERVER" IN GSDBTEST)
WORKING IN ("user_dir_on_new_system")
FORMAT MIGRATION;

MIGRATE TABLESPACE 
SELECT * FROM GOSALES.PRODUCT_FORECAST;
TARGET ENVIRONMENT (INSTANCE "NEWINST" ON "NEWSERVER" IN GSDBTEST)
WORKING IN ("user_dir_on_new_system")
FORMAT MIGRATION;

MIGRATE TABLESPACE 
SELECT * FROM GOSALES.PRODUCT_LINE;
TARGET ENVIRONMENT (INSTANCE "NEWINST" ON "NEWSERVER" IN GSDBTEST)
WORKING IN ("user_dir_on_new_system")
FORMAT MIGRATION;

MIGRATE TABLESPACE 
SELECT * FROM GOSALES.PRODUCT_NAME_LOOKUP;
TARGET ENVIRONMENT (INSTANCE "NEWINST" ON "NEWSERVER" IN GSDBTEST)
WORKING IN ("user_dir_on_new_system")
FORMAT MIGRATION;

MIGRATE TABLESPACE 
SELECT * FROM GOSALES.PRODUCT_SIZE_LOOKUP;
TARGET ENVIRONMENT (INSTANCE "NEWINST" ON "NEWSERVER" IN GSDBTEST)
WORKING IN ("user_dir_on_new_system")
FORMAT MIGRATION;

MIGRATE TABLESPACE 
SELECT * FROM GOSALES.PRODUCT_TYPE;
TARGET ENVIRONMENT (INSTANCE "NEWINST" ON "NEWSERVER" IN GSDBTEST)
WORKING IN ("user_dir_on_new_system")
FORMAT MIGRATION;

2. Save the control file on the computer that is running Data Studio Administrator, such as C:\HPU_ControlFiles\MigrateSchema.ctr.
   
   
3. From the Data Source Explorer, drill down to any table in the GOSALES schema in GSDB, such as the PRODUCT table.
   
   
4. Right-click the PRODUCT table, and click High Performance Unload, as shown in Figure 18.

The DB2 High Performance Unload task assistant opens.

5. Click the Customize Query tab on the DB2 High Performance Unload task assistant.
   
   
6. To use the control file, select the Upload control file option, and browse to select the file you created. This opens up the control file in the editor, as shown in Figure 19.

7. Click Run to migrate the data to the test database.

Back to top

Conclusion

This article demonstrates one way that you can use Data Studio Administrator’s easy user interface and DB2 High Performance Unload’s powerful capabilities together to speed data migration. The step-by-step instructions in the article can help database administrators use these tools together most effectively.

Acknowledgment

The authors would like to thank Sweta Patel and Tina Chen for their valuable review and feedback for this article

Back to top

Downloads

Information about download methods

Resources

Learn

• See "IBM Data Studio software: The big picture" (developerWorks, 2008) to understand how Data Studio Administrator fits in with the rest of the Data Studio portfolio.
  
  
• Understand Data Studio software packaging, including the no-charge administrative capabilities available in the Data Studio Administrator download.
  
  
• Find out "What’s new in Data Studio Administrator 2.1" (developerWorks, 2009 Feb) to explore all the capabilities in that product.
  
  
• Learn how to recover from unexpected outages using Data Studio Administrator.
  
  
• Read "Data migration and change management using Data Studio Administrator V2.1" (developerWorks, 2009 Apr) to learn more about using the copy and paste capabilities.
  
  
• Look at the IBM Data Studio Web page.
  
  
• Find the IBM Data Studio community space for more Data Studio information.
  
  
• Read the Data Studio/Integrated Data Management blog for more Data Studio scoop.
  
  
• Watch the demo A Day in the Life of a DBA to observe a DBA use Data Studio Administrator, DB2 High Performance Unload, and other integrated data management offerings to get through a typical day.
  
  
• Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Go to "Trial: IBM Data Studio Administrator for DB2 for Linux, UNIX, and Windows" to download the trial version Data Studio Administrator.
  
  
• Go to "IBM Data Studio Administrator, Version 2.1.0.1" to get the Fix Pack 1 installation files and instructions for Data Studio Administrator.
  
  
• Download DB2 Express-C, which is a no-charge version of DB2 Express Edition.
  
  

Discuss

• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the authors

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.

Vinod Chirayath is a Software Engineer in the Information Management Tools group at the IBM lab in Lenexa, Kansas. He has worked on Data Studio Administrator since 2008, and he holds a Master’s degree in Computer Science.

Kathy Zeidenstein has been with IBM's Silicon Valley Lab since 1987. She started in the DB2 for OS/390 organization, which is where she got her first exposure to object-relational. After she began working on the SQL standards team, she became involved with the more advanced object-relational features in DB2, because much of the language design for that work was taken to the SQL standards committees.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Scenario A: Unloading a table using DB2 High Performance Unload from Data Studio Administrator
• Scenario B: Migrating a schema
• Conclusion
• Downloads
• 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