Using Data Studio Administrator and DB2 High Performance Unload for fast and efficient data and schema migration

As a database administrator (DBA), you might need to upgrade your database server when faster and more efficient hardware is made available to you. And you probably need to do this with as little impact to production performance as possible. In this article, you will follow along step-by-step in two scenarios to see how Erik, a DBA at a fictional company, uses Data Studio Administrator 2.1 and DB2® High Performance Unload 4.1 together to accomplish fast and efficient data migration. These scenarios describe how to efficiently combine the ease of use of Data Studio Administrator with the high-speed unload capabilities of DB2® High Performance Unload.

Share:

Don Langworthy (dbl@us.ibm.com), Software Engineer, IBM

Don Langworthy photoDon 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 (chirayat@us.ibm.com), Software Engineer, IBM

Photo of Vinod ChirayathVinod 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.



Kathryn Zeidenstein (krzeide@us.ibm.com), InfoSphere Guardium Evangelist, IBM

Photo of Kathryn ZeidensteinKathy Zeidenstein has worked at IBM for a bazillion years. Currently, she is working as a technology evangelist for InfoSphere Guardium data activity monitoring, based out of the Silicon Valley Lab. Previously, she was an Information Development Manager for InfoSphere Optim data lifecycle tools. She has had roles in technical enablement, product management and product marketing within the Information Management and ECM organizations at IBM.



14 May 2009

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.

Figure 1. DB2 High Performance Unload provides flexible output options
Tech art: LIsts various source and output options. Source options include database_name, table_name, all user (non-system) tables, individual tables, multiple tables, summary and alias tables, full backups, and all partitions. Output options include tape devices, named pipes, PC/IXF, delimited, undelimited, DSNTIAUL, and XML formats.

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.
Figure 2. Data Source explorer
Screen cap: Data Source explorer with Database Connections and New selected
  1. 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.
Figure 3. New Connection wizard
Screen cap: New Connection wizard with DB2 for Linux, UNIX, and Windows selected. Also shows Database as NEWDB, Host as Localhost, and Port Number as 50000
  1. 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.
Figure 4. Selecting High Performance Unload
Screen cap: Data Source Explorer screen showing CUST and High Performance Unload selected

The DB2 High Performance Unload task assistant opens.

  1. Specify C:\HPU_ControlFiles for the Data server data file directory, as shown in Figure 5.
Figure 5. High Performance Unload task assistant
Screen cap: High Performance Unload CUST window showing C:\HPU_ControlFiles for the Data server data file directory
  1. Click the Customize Query tab, and select all the columns available in the table, as shown in Figure 6.
Figure 6. Specifying columns for DB2 High Performance Unload
Screen cap: High Performance Unload CUST window showing all the columns as Selected
  1. Click Preview Command, and review the generated DDL, as shown in Figure 7.
Figure 7. Preview commands
Screen cap: Shows the list of commands to be run
  1. 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.
Figure 8. Load table
Screen cap: Data Source Explorer screen showing CUST and Load selected

The Load task assistant window opens.

  1. Click the Files tab in the Import table task assistant, and use the data file you created.
  2. Click Preview command, and review the generated DDL, as shown in Figure 9.
Figure 9. Preview Commands
Screen cap: Shows the list of commands to be run
  1. When you are satisfied with the commands, click Run to execute them.

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.
Figure 10. Data Source explorer
Screen cap: Data Source explorer with Database Connections and New selected
  1. Select DB2 for Linux, UNIX, and Windows, and provide the instance details and authentication credentials based on your environment, as shown in Figure 11.
Figure 11. New Connection wizard
Screen cap: New Connection wizard with DB2 for Linux, UNIX, and Windows selected. Also shows Database as NEWDB, Host as Localhost, and Port Number as 50000
  1. 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.

Under the covers

When a change management script is created, two physical models of the database are created. One of the models is the base model, which represents the current state of the database. The other model is the target model, which is the model that is edited to specify what the database should look like after the changes.

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.
Figure 12. Copy from GSDB database
Screen cap: Data Source explorer showing all the tables and Copy selected
  1. From the Data Source explorer, navigate to the GSDBTEST database, and drill down to the GOSALES schema.
  2. Right-click the GOSALES schema folder, and click Paste, as shown in Figure 13.
Figure 13. Paste to GSDBTEST database
Screen cap: Data Source explorer with GOSALES and Paste selected

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

  1. Select Create a New Change Management Script, and click Next. The GOSALES schema is selected by default.
  2. Click Next.
  3. Select Copy database objects only, as shown in Figure 14.
Figure 14. Copy database objects in Change Management Script editor
Screen cap: Paste Database Objects window with Copy Database Objects Only selected
  1. Click Finish. The results are similar to Figure 15.
Figure 15. Change Management Script editor
Screen cap: Change Management Script Editor window showing several tables in the Objects To Be Changed column
  1. Click Preview Command, and review the generated DDL, as shown in Figure 16.
Figure 16. Preview commands
Screen cap: Shows the list of commands to be run
  1. When you are satisfied with the commands, click Run to execute them.

Remember to check the generated DDL

Check the generated DDL to make sure table space and container names are accurate. You can change the DDL by clicking Open in SQLX Editor.

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

Figure 17. Data Source Explorer view showing the newly created tables in the test database (GSDBTEST)
Screen cap: Data Source Explorer window showing the newly created tables in the GSDBTEST database

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.
Listing 1. Control file template
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;
  1. Save the control file on the computer that is running Data Studio Administrator, such as C:\HPU_ControlFiles\MigrateSchema.ctr.
  2. From the Data Source Explorer, drill down to any table in the GOSALES schema in GSDB, such as the PRODUCT table.
  3. Right-click the PRODUCT table, and click High Performance Unload, as shown in Figure 18.
Figure 18. High Performance Unload option in Data Source explorer
Screen cap: Data Source Explorer screen showing PRODUCT and High Performance Unload selected

The DB2 High Performance Unload task assistant opens.

  1. Click the Customize Query tab on the DB2 High Performance Unload task assistant.
  2. 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.
Figure 19. High Performance Unload task assistant
Screen cap: High Performance Unload PRODUCT window showing C:\HPU_ControlFiles\MigrateSchema.ctr for the Data server data file directory and Upload Control File selected
  1. Click Run to migrate the data to the test database.

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


Downloads

DescriptionNameSize
Sample database GSDBGSDB_database.zip132KB
Sample database GSDBNEWGSDBNEW.zip515KB
Sample database GSDBTESTGSDBTEST.zip186KB

Resources

Learn

Get products and technologies

Discuss

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=389047
ArticleTitle=Using Data Studio Administrator and DB2 High Performance Unload for fast and efficient data and schema migration
publish-date=05142009