Blog Details

Approach for Data Migration (From DB2-Oracle and IDMS-Oracle)

Approach for Data Migration (From One Database to another Database) with Database specific references.

This is an attempt to mention an approach which has been followed for the following cases

1.Conversion Approach ( DB2 to Oracle ) : DB2 under MVS5.22 5.0 to Oracle 8i or 9i under UNIX

2.Conversion Approach ( IDMS to Oracle ): IDMS under MVS5.22 Version ND 14.0 to Oracle 8i or 9i under UNIX

Approach 1. Conversion Approach ( DB2 to Oracle )

Schema conversion involves moving Data Definition Language (DDL) scripts from DB2 to Oracle8i and running Oracle8i DDLs to generate the Oracle8i database.

Data conversion involves moving the existing data from the DB2 database to the system on which the Oracle8i database has been created, then loading the data into the Oracle8i database, Application conversion converting the application programs so they can use the Oracle8i database. The new application retains the look and feel of the original application

Data Conversion

(A) SQL*Loader utility :

Oracle8i understands control files created by DB2’s unload utility. The control file generated by DB2 can be loaded to the UNIX box in ASCII format. The control file needs to be examined and the appropriate datatype mappings have to be made. The DB2 data can then be loaded as a binary file. The control file must be modified to include SQL*Loader’s CHARSET command. In DB2, the INTEGER, FLOAT, and DECIMAL datatypes are stored as compressed data. Oracle7’s SQL*Loader translates the binary data before storing the data in the Oracle7 database. DB2 double precision FLOAT must be converted to Oracle7’s datatype DOUBLE in the SQL*Loader control file.

Steps for Data Migration:
· Convert DB2 data to flat files
· Transfer the flat files to UNIX box
· Write SQL * Loader control files to load the data in intermediate oracle database
· Write Oracle Packages, Procedures and functions to load data from intermidiate database to Final oracle database (Where all tables and indexes should be created before migration)


· Oracle’s own utility product.
· Faster than any third party tool for migration
· Has a powerful data-parsing engine that puts little limitation on the format of the data in the datafile.
· Can load data from multiple datafiles during the same load session.
· Can load data from disk, tape, or named pipe.
· Does sophisticated error reporting, which greatly aids troubleshooting.

(B) Data Junction (Third Party tool) :

Data Junction 7.0 offers a superb range of data conversion capabilities via its new and improved graphical design interfaces and multiplatform conversion engine. No other data conversion tool delivers so much for so little, making this a good choice for most companies implementing data warehouses.


· Graphical Project Designer; event-driven processing; multidatabase dynamic SQL lookups; processes multiple record layouts in a single pass; ActiveX integration into expression evaluator; design-once, run-everywhere capability; low cost

· DJ Engine for UNIX limited to AIX, HP-UX, SCO UnixWare and Solaris; lacks server-based repository for sharing data transformation definitions

· Data Junction now supports multiple record layout scenarios, including parent-child and hierarchical records found in complex data structures

· The Conversion Designer is the heart of the Data Junction tool set. This is where the main work of getting data from one format, layout or application to another is done

(C) DecisionBase (Third Party Tool) :

DecisionBase is a data transformation and movement tool that helps you build data marts and warehouses right — without compromising scalability, ease of use, complex transformations, or robust metadata management. With a graphical mapping tool, sophisticated transformation and movement capabilities, and the industry-leading repository technology, DecisionBase defines, creates, and maintains data warehouses in a client/server environment. DecisionBase is the only end-to-end data warehouse solution that offers a mature metadata technology and a robust data movement language, all managed from a graphical interface


· DecisionBase moves and transforms data from a variety of sources, including ERP systems, allowing you to leverage all your enterprise-wide data in a single warehouse environment

· The DecisionBase Mapper lets you describe how source data is extracted, transformed, and passed to target databases, with simple point-and-click gestures through a graphical user interface (GUI).

· Metadata Capture from All Major Databases : The metadata captured by the DecisionBase Metadata Manager can be published to the web, allowing corporate metadata to be viewed from a standard Internet browser

· Leverages Data Models Created in PLATINUM Erwin : Physical database models created in PLATINUM ERwin for common RDBMSs can be scanned into the Metadata Manager of DecisionBase and used in the Mapper, just as if they had been scanned from the underlying RDBMS

· Simple Point and Click Development : The DecisionBase Mapper is an easy, fast, and efficient graphical user interface for creating data warehouse movement applications for the data warehouse

· Complex Transformations Made Easy : DecisionBase also provides the means for you to create your own transformations using a combination of Mapper wizards and the underlying movement language

Approach 2. Conversion Approach ( IDMS to Oracle )

· Map and Gap document preparation for IDMS and Oracle

· Write Ezytrieve program to generate desired flat file

· Move flat files to UNIX box through FTP

· Create Oracle Tables as per requirement

· Oracle Stored Procs. will read the flat files and will update into Oracle tables

· Oracle Stored Procs will update the data into different tables as per business logic.

Testing :

In any bespoke software project, testing can account for up to 40% of the cost of the system. In its simplest form, where the database has been replaced, the testing will ensure the data has been migrated in a one-to-one state and that the application functions in the same way as before, producing the same results in Oracle as the source database. There should be three levels of testing at this phase, Integration, System testing and User acceptance testing ensuring that the migration is valid. 

Compiled by Keshav Tripathy, Nirmalya Labs.

    13-06-2017         2 : 1 PM

Social Links