IBM Database Conversion Workbench, Part 1: Overview

Make database conversion easy with DCW

The IBM® Database Conversion Workbench (DCW) is a no-charge plug-in that adds database migration capabilities to IBM Data Studio. DCW integrates many of the tools used for database conversion into a single integrated environment, following an easy to use framework that is based on best practices from IBM migration consultants. This first article in the series provides an overview of conversion methodology and the various functions in DCW 2.0.

Antonio Maranhao (amaranha@ca.ibm.com), Development Manager, IBM

Antonio MaranhaoAntonio Maranhao is the development manager of IBM Database Conversion Workbench (DCW). Working in the IBM Toronto Lab, he manages a team of software engineers dedicated to developing DCW and supporting its community of users. He is also a passionate DB2 for Linux, UNIX, and Windows advocate and has delivered DB2 training to audiences worldwide. He has participated and coordinated the development of several DB2 training courses, articles, and certifications.



Joshua Kim (joshkim@ca.ibm.com), Project Manager, IBM

Joshua KimJoshua Kim is the project manager for the IBM Database Conversion Workbench, working out of the IBM Toronto Software lab. With over four years of project management experience, he provides project management and development support for the IBM PureData Ecosystem team. He holds a bachelor of applied science degree from the University of Toronto, and he is certified on DB2.



05 September 2013

Introduction

Converting your database from one vendor to another is often a difficult and time-consuming task. DCW provides an integrated workbench of tools to help facilitate your conversion project to DB2®. Whether converting to DB2 from another relational database management system (RDBMS), or migrating from one version of DB2 to another, DCW provides a simple framework to take you through the conversion process.

DCW provides the following benefits:

  • Common graphical interface with uniform look and feel for all phases of the conversion
  • A consolidated process with well-defined steps based on best practices
  • Faster enablement for client and partner teams in conversion methodology
  • Wizards that guide you through all steps of the conversion

This article provides an overview of the standard conversion process, as well as details of the various features and functions of DCW. Subsequent articles will cover the in-depth processes concerning Oracle to DB2 LUW conversion, DB2 to DB2 migration, Oracle to PureData System for Transactions conversion, and Oracle to DB2 for z/OS conversion.

DCW developerWorks community

In addition to this series of articles, there exists the DCW developerWorks Community, which contains download links and user guides, as well as various forums and blogs to help you connect with the development team.

IBM Data Studio

Visit IBM Data Studio for downloads and additional information.

DCW is provided as a plug-in to IBM Data Studio, a no-charge graphical tool based on an Eclipse® platform.

Offerings

There are two different offerings for DCW:

  • Plug-in for Data Studio: The recommended version of DCW for production use, to be installed into Data Studio. IBM Data Studio helps database developers and administrators manage, administer, and develop heterogeneous database environments for increased productivity and collaboration.
  • VMware Image: VMware Image based on SUSE Linux, comes pre-installed with IBM Data Studio, DCW, and DB2 Express-C edition. Use for demonstration purposes only.

Supported conversions

  • DB2 LUW to DB2 LUW
  • Oracle to DB2 LUW
  • Oracle to DB2 z/OS
  • Oracle to IBM PureData System for Transactions

The database conversion process

Database conversion is the process of transferring data between different storage systems, formats, or types. Very simply, data from the source system is mapped to the target system through a process of data extraction and data loading. However, this is not always as easy as it seems. Often, manual conversion of the DDL syntax is required to ensure compatibility between source and target database servers.

The various steps of the conversion can be grouped into three broad database conversion phases:

  1. DDL Extraction: Data Definition Language (DDL) is a syntax for defining data structures. DDL statements are used to create, modify, and drop database objects such as tables, indexes, and users. DCW utilizes DDL files to analyze objects in your source database and to reproduce the database objects on the source database. DDL extraction is the process of obtaining the required DDL statements from the source database.
  2. Assessment and conversion: During this phase, you examine the DDL of the source database to appropriately plan your conversion project. This traditionally involved manually reviewing and converting thousands of DDL statements for syntax compatibility issues between the source and target databases.
  3. Data movement: Once all compatibility issues have been resolved, the appropriate source database objects need to be reproduced on the target database. After the required database objects have been created, the data can be mapped and migrated from the source database to the target database.

To help illustrate this conversion process, Figure 1 shows the DCW Oracle to DB2 Conversion Process. This process is examined in more detail in the next article of this series.

Figure 1. DCW Oracle to DB2 conversion process
DCW Oracle to DB2 conversion process

Getting started with DCW

One of the advantages of DCW is that it provides wizards and help topics for each of the steps in a conversion project.

With the exception of a few advanced functions, everything you need is accessible from within the DCW graphical interface.

Creating a conversion project

The integrated environment of DCW allows you to manage your conversion easily by creating a new DCW Project. From within the DCW Project, all the features and functions relevant to your conversion are readily available. All relevant files are stored in a single location and actions can be fired with a click of the mouse.

Figure 2. Creating a new DCW project
Creating a new DCW project

When you select the source database and target database, DCW automatically configures the appropriate features and functions to the specified vendors.

The DCW Task Launcher

Figure 3 shows the centerpiece of DCW: the DCW Task Launcher. It provides an easy-to-use interface to launch the wizard for the various steps of the conversion process.

Figure 3. The DCW Oracle to DB2 Task Launcher
The DCW Oracle to DB2 Task Launcher

DDL extraction

IBM Database Conversion Workbench offers two different methods to easily extract the DDL of your source database: through a connection or through a custom extraction. Figure 4 shows an example of a DDL extracted from a database:

Figure 4. Example of DDL extracted from a database
Example of DDL extracted from a database

Extract DDL through a connection

DCW can connect to a source database and extract DDL. This requires a connection profile to the source database, and it can require additional JDBC drivers. The wizard allows you to filter what objects and schemas to generate the DDL for, shown in Figure 5:

Figure 5. Extract DDL through a connection wizard
Extract DDL through a connection wizard

Extract DDL using a custom extraction script

If a connection to the source database is not possible, DCW can produce a custom DDL extraction script. This script can be run using Oracle's SQL*Plus to extract the DDL of the source database. Figure 6 shows a custom DDL extraction script wizard:

Figure 6. The custom DDL extraction script wizard
The custom DDL extraction script wizard

Assessment and conversion features

The most challenging part of the conversion process is often determining whether or not your source syntax is compatible with the target database and making the appropriate changes. To help facilitate this, DCW offers a variety of assessment and conversion features.

Compatibility evaluation

The Compatibility Evaluation wizard generates a report that rates the compatibility of the source DDL with the target DB2 database. It includes a detailed DDL and PL/SQL issues list, as well as known workarounds to fix incompatible code. Figure 7 is a sample of the Technical Summary section of the Compatibility Evaluation Report, which provides statistics on the number of statements that was detected, and identifies the number of statements that require attention. You can use these statistics to help plan your conversion project. In addition to the technical summary, the report contains a detailed technical summary, which identifies specific statements that require attention and a recommended solution. These statements must be altered or removed because DB2 does not recognize these statements.

Figure 7. Sample of an evaluation report
Sample of an evaluation report

Note: DCW generates an encrypted report that must be emailed to askDCW@ca.ibm.com, which responds with the decrypted HTML report. No source code is included in the encrypted report. For users with an IBM Intranet ID, DCW can authenticate your credentials and automatically generate the decrypted HTML report. Instructions on how to authenticate can be found in the user guides.

Code conversion

In addition to the Compatibility Evaluation Report, DCW also has the ability to auto-convert well known syntax to DB2 compatible syntax, automating a time-consuming process. Figure 8 shows a sample of converted code:

Figure 8. Sample of converted code
Sample of converted code

DCW does one of the following four things:

  • Remove code fragment: DCW comments out fragments of code that are not required on DB2. These are tagged in the converted code with Code fragment was removed.
  • Auto-convert code: DCW automatically makes updates to code to make it compatible with DB2 while maintaining the semantics of the original code. These statements are not tagged.
  • Requires Attention: Code is marked as requires attention and is converted to DB2 compatible syntax so that the statement can compile and execute successfully. Although due to differences in behavior between DB2 and the source vendor, it might raise issues during runtime in very specific scenarios.
  • Evaluation Issue: DCW highlights statements that were identified in the Compatibility Evaluation Report as being a potential issue. These are tagged in the converted code as DCW Evaluation Issue.

You can use the SQL Editor to view and edit the converted DDL.

Split a DDL file

DCW offers an optional, yet useful function to split a single DDL file into multiple files, organized by object type. This function helps to streamline your conversion process by providing a much more intuitive code organization. Furthermore, it breaks down a large DDL file into smaller and more manageable components, which allows multiple team members to work on various files. Figure 9 shows an example output of the Split DDL function:

Figure 9. Example output of the Split DDL function
Example output of the Split DDL function

Creating objects on the target database

Once the source DDL has been correctly converted and all issues resolved, you can directly execute the SQL statements from DCW to create the required objects on the target database, shown in Figure 10:

Figure 10. Using the SQL Editor to execute SQL statements
Using the SQL Editor to execute SQL statements

Data movement options

DCW contains various data movement methods to suit your requirements. These options are available depending on whether a direct connection to the database is possible, as well as the combination of source and vendor databases. Further, DCW offers some advanced data movement features, which may require additional licenses from IBM.

Data Movement using Flat Files

DCW can extract and load data to and from delimited text files. Extract and load scripts are automatically generated by DCW and can be executed either from within DCW or from the command line. This process requires additional disk space to store the temporary data files.

Requirements:

  • A connection to the source database.
  • DCW must be installed on the target server, if executing the load from within DCW.

Recommended use:

  • When a simultaneous connection to both the source and target databases is not possible.

Data movement through pipes

You can move data from one database to another without the need for temporary files, by using pipes. This process is executed from within DCW and the extract and load are done in parallel.

Figure 11. Selecting tables to move
Selecting tables to move

Requirements:

  • A connection to the source database.
  • DCW must be installed on the target server.

Recommended use:

  • When a simultaneous connection to both the source and target databases is possible.
  • No additional licenses required.

Data movement using InfoSphere Federation Server

If federation is available for the Oracle/DB2 source databases, DCW can leverage the data movement capabilities of the InfoSphere Federation Server.

Requirements:

  • A connection to both the source and target databases.
  • The source database must have federation capabilities.
  • The target database must have the federation license.

Recommended use:

  • When a federation license is available on the target database.
  • When installation of DCW on the target server is not possible.

Data replication using InfoSphere Change Data Capture

For databases that are progressive and when the downtime window is small, DCW can configure InfoSphere Change Data Capture replication by creating a unique subscription between source and target tables, as shown in Figure 12:

Figure 12. Configuring CDC subscription
Configuring CDC subscription

Requirements:

  • A connection to both the source and target databases.
  • CDC access server and replication server running on the target database.
  • Appropriate CDC license.

Recommended use:

  • When the downtime windows is small.

Conclusion

The task of converting a database to a different vendor can be time consuming without the appropriate tools. This article presented DCW, a no-charge plug-in that enhances IBM Data Studio with database conversion capabilities. You can leverage DCW to assess effort of a database migration, perform automatic code conversion, and move data from the source to the target database. All these functions are available as wizards in DCW, which can guide beginner and advanced users alike to perform the database conversion tasks in easy and structured steps.


Downloads

DescriptionNameSize
Oracle to DB2 Conversion GuideIBM DCW Oracle to DB2 Conversion Guide2.8 MB
DB2 to DB2 Migration GuideIBM DCW DB2 to DB2 Migration Guide2.2 MB

Resources

Learn

Get products and technologies

  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online or use a product in a cloud environment.

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=943143
ArticleTitle=IBM Database Conversion Workbench, Part 1: Overview
publish-date=09052013