Migration of a DW solution from Oracle to DB2

Recently we migrated an entire DW module from Oracle to DB2. And our ETL was done using Business Objects Data Integrator.

Below is a sequence of steps that we followed to migrate the entire solution (including data structures, ETL code and universe)

Assumption:
–>All estimates done for 4 DB2 databases, 3 federated systems, 62 tables, 33 indexes, 6 DI jobs, 110 data flows, 151 transformations, 16 SQL transforms, 34 SQL() scripts, 220 objects in 23 classes and 2 reports.
–>Add 20% time as buffer for unexpected issues.
–>Some tasks can be executed in parallel, while some have strong dependency on completion of previous tasks.

Work breakdown of migration and rough estimation:

1. Initiation & planning : 40 Hrs
Establish objectives and goals
Requirements gathering
Establish scope
Plan resources
Identify development environment

2. Assessment : 40 Hrs
Assess tool and technology needs
Assess and understand technical requirements
Establish technical guidelines
Finalize tools and development environment

3. Inventory of objects to migrate : 40 Hrs
Identify DI jobs
Identify data structures used in DI jobs
Identify data structures used in SQL() and SQL transforms in DI jobs
Identify source and target databases
Identify data structures needed for universe
Gap analysis

4. Configuring the development environment : 48 Hrs
Install and configure DB2 server
Install and configure DB2 client in local machine, DI Designer and job servers, BOXI R3 server
Install and configure IBM MTK
Create and configure databases and federate them if needed.
Configure database storage
Setup ODBC connectivity and create DI data stores
Test all connections

5. Migrating data structures : 40 Hrs
Read data structures from Oracle using IBM MTK
Convert to DB2
Deploy in DB2 database
Fix issues and validate

6. One time loading of all tables (Oracle to DB2) : 60 Hrs
Create one time ETL to load all tables
Fix issues
Execute the ETL

7. Migrate ETL code : 120 Hrs
Review ETL code complexity
Review SQL() and SQL transforms used in ETL
Review usage of Oracle specific features
Migrate ETL to DB2 by a ATL file datastore change. (Risky but easy. Take backup of repository)
Manually change all tables used in table comparisons and key gen transforms (or functions)
Manually modify SQL used in SQL() and SQL transforms. (This is one of the most time consuming tasks)
Execute each ETL job
Execute one complete ETL cycle

8. Migrating Universe : 40 Hrs
Change database connection to point to DB2 and test
Identify all objects, measures, classes, filters involved in Universe and parse them successfully
Check loops / contexts
Check integrity

9. Migrating / creating reports :40 Hrs
Create 2 Webi report (with charts, tabs, filters, prompts, multiple data providers, etc)
Test report (Scheduling, data validation, etc)

10. Unit & integration testing : 40 Hrs

11. Reconciliation of reports between ORA & DB2 : 8 Hrs

12. Documentation (minimal): 40 Hrs