Move data using the IBM DB2 Migration Toolkit

Was your application recently ported to IBM DB2® Universal Database™ (UDB) on Linux™, UNIX® or Windows®? Do you want to move data from an existing deployment of Oracle or SQL Server to DB2 UDB? Learn how to do this using the IBM DB2 Migration Toolkit.

Paul Yip (ypaul@ca.ibm.com), DB2 Consultant, IBM Toronto Software Lab

Author photoPaul Yip is a consultant from the IBM Toronto Lab. He specializes in assisting business partners port their database applications from competitor's platforms to DB2. He has authored over a dozen technical articles and has co-authored three books.



18 November 2004

Also available in Russian

Introduction and background

This article is intended for developers, administrators, or Independent Software Vendors (ISVs) who:

  • Have database applications that support both IBM DB2 Universal Database (UDB) and non-IBM databases (such as Oracle and SQL Server)
  • Have customers who wish to migrate their data from Oracle or SQL Server to DB2

I frequently receive the following question:

I have completed a port of my database application to DB2, but I have existing customers running on Oracle/SQL Server who wish to transition their production systems to DB2. How can I migrate the data?

The task of moving your data can be achieved using the IBM Migration Toolkit (MTK) if the database schema used for the non-IBM platform is identical (or very similar) to the schema used for DB2. Therefore, this article assumes the port of database schema has been completed and will focus on how to move data from an Oracle or SQL Server database to DB2 only (using the data movement feature in MTK).

In this article, we will refer to the Oracle or SQL Server database as the source database, and the DB2 database as the target database. We assume you have already created an empty target database with schema in place.

Before you begin, ensure you have:

  • Downloaded and installed the free IBM DB2 Migration Toolkit
  • Sufficient disk space to unload the source database to flat files on the system where MTK is installed
  • A script used for creating the schema for the source database (to be used as input to MTK)

Task 1: Import schema script for source database

  1. Start up MTK and click Launch the Migration Toolkit product button.
    Figure 1. Launching the MTK
    Figure 1
  2. Create a new MTK project. The project can be called anything you want. In the pull-down lists, select the appropriate source database type and your target DB2 version (DB2 for Linux, UNIX, and Windows in this case).
    Figure 2. Creating a project
    Figure 2
  3. Import the source SQL script into MTK. This script will later be parsed by the MTK to generate an internal XML representation of the source database's schema. This is how MTK becomes aware of the structure of the source database.
    Figure 3. Importing source database DDL script
    Figure 3

    Tip: The performance of MTK can be improved if you use an input script which only contains DDL for tables. That is, remove DDL for functions, triggers, stored procedures, etc). The MTK only needs to know about the source table structures to generate export/import scripts.

  4. Along the top, you will see that MTK has 5 tabs. In the Covert tab, click the Convert button. This is the step where MTK parses the input script you provided in the previous step. If your schema has many objects, this may take several minutes. Once the conversion is complete, you will be taken to the Refine tab of the MTK.
    Figure 4. Using CONVERT to make MTK aware of the source database schema
    Figure 4
  5. In the refine tab, review the Translator Information and confirm that all the syntax was accepted without any serious errors.
    Figure 5. Viewing the REFINE tab of the MTK
    Figure 5

Note that some errors or warnings can safely be ignored. For example:

  • 16 Duplicate definition of object-name
  • 20 Object name has been changed to new-name.

Task 2: Extract Oracle/SQL Server data to flat files

  1. Now that the MTK is aware of the source database and the tables which need to have data extracted, you can generate data extraction scripts and data load scripts for DB2.

    In the Generate Data Transfer Scripts tab, specify options to be used for the data extraction and loading process.

    Figure 6. Data extraction and loading options
    Figure 6

    We recommend selecting the following options:

    Option Value Reason
    DB2 Data Loading Options Use LOAD We want to have an exact duplication of the source database's data in DB2. Therefore, this is the correct option because you do not want any triggers to be activated. LOAD is also faster than IMPORT.
    DB2 LOAD/IMPORT mode Use REPLACE In case loading of a table fails for some tables, you do not want the data to be appended to any target tables should you restart the loading process.
    File Format Use DEL Conserves disk space, preserves blank spaces in strings, and works in most cases.

    Important: Ensure that the directory specified in Directory for data extraction has plenty of space for unloading the source database into flat files.

  2. Click Create Scripts button. Note that no data is actually extracted or loaded at this point. Only the scripts to perform this action are generated based on the information MTK has gathered from the input scripts and the options you selected in the prior step.
  3. In the Deploy to DB2 tab, provide the name of the DB2 database.
    Provide information for:
    • target database name
    • target database (local or remote)
    • user name and password for target database. The user name must match the schema name where tables are created in target database.
    De-select
    • Launch script-name in the database
    Select:
    • extract (from source database) and store data on this system
    • Load data to target database using generated scripts
    Figure 7. Extraction and loading of data to target database
    Figure 7

    Click the Change source database button to confirm that source database is correct.

    Tip: If you see Driver Not Available in the Change source database dialog, you need to correct your Windows System classpath to include Oracle's classes111.zip (or whichever class file provides the Oracle JDBC driver). Save your project and exit MTK. Fix the classpath and restart MTK to continue with this project.

    Figure 8. Driver Not Available error
    Figure 8

    To begin data movement, click the Deploy button. Review the resulting HTML reports and determine if data was successfully moved.


Additional tips

If MTK has problems with your input script, modify it and re-convert it until it is accepted graciously (by reviewing messages in the Refine tab). Your goal is to get MTK to parse the input script with as few errors as possible about the basic table structures of the source database. Errors or warnings relating to anything but the basic table structure can be ignored. For example, errors or warnings related to indexes and views can be ignored.

Keep in mind that this technique works best when the source schema is identical or nearly identical to the target schema. If this method is not suitable in your situation, you may want to try it anyway and use the scripts generated by MTK as a starting point.

The performance of DB2 LOAD utility can be improved by increasing the target database's utility heap.

db2 update db cfg for dbname using UTIL_HEAP_SZ 20000        (recommended minimum for a 2 CPU box)
db2 force application all                                    (to ensure that the change takes effect)

A good rule of thumb is to set util_heap_sz to at least 10000 x #CPUs on the DB2 server. If your system is constrained on memory, you can reduce the size of buffer pools in the target database because the DB2 LOAD utility does not use buffer pools.

If you are using system managed storage (SMS) table spaces, you can reduce the overhead of allocating disk space by enabling multi-page allocation.

db2 force application all    (The utility db2empfa can only work when the database is inactive)
db2empfa dbname	             (This is an operating system level command)

Disk contention can be further reduced if data is extracted to disk(s) which are not already used by the source or target databases (specified in Task 2, step 1)

Resources

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=31894
ArticleTitle=Move data using the IBM DB2 Migration Toolkit
publish-date=11182004