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.
DCW is provided as a plug-in to IBM Data Studio, a no-charge graphical tool based on an Eclipse® platform.
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.
- 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:
- 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.
- 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.
- 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
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
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
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
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 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
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.
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
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.
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
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
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
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.
- A connection to the source database.
- DCW must be installed on the target server, if executing the load from within DCW.
- 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
- A connection to the source database.
- DCW must be installed on the target server.
- 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.
- A connection to both the source and target databases.
- The source database must have federation capabilities.
- The target database must have the federation license.
- 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
- A connection to both the source and target databases.
- CDC access server and replication server running on the target database.
- Appropriate CDC license.
- When the downtime windows is small.
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.
|Oracle to DB2 Conversion Guide||IBM DCW Oracle to DB2 Conversion Guide||2.8 MB|
|DB2 to DB2 Migration Guide||IBM DCW DB2 to DB2 Migration Guide||2.2 MB|
- "Enable C++ applications for Web services using XML-RPC" (developerWorks, Jun 2006) is a step-by-step guide to exposing C++ methods as services.
- In the XML area on developerWorks, get the resources you need to advance your XML skills, including DTDs, schemas, and XSLT.
- Stay current with developerWorks technical events and webcasts focused on a variety of IBM products and IT industry topics.
- Attend a free developerWorks Live! briefing to get up-to-speed quickly on IBM products and tools as well as IT industry trends.
- Follow developerWorks on Twitter.
- Watch developerWorks on-demand demos ranging from product installation and setup demos for beginners, to advanced functionality for experienced developers.
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.
- Participate in the discussion forum.
- Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.