What specific tests need to be performed under Database migration from DB2 to Oracle?

Question

Currently I am going to be testing (I am a Data Analyst) of a larger migration project where they are planning to migrate from DB2 to Oracle. My question may be little broader; however any information which relates to the following questions would certainly be helpful.

1) How to validate both under Source and Target tables for:

• Matching of Total Number of tables

• Matching of Data fields under each table

2) How to validate from Front end (UI), so that right data is getting populated under right table: Challenge

• There are 500+ UIs, What is the best way to test in an Optimized way (to avoid any duplicate testing)
• Should I also focus on table mapping to UI screen

3) Any specific challenges I need to see under migration

• Datalock, Spacing issue, Noisy words

Answer 1

Normally for a straight forward data migration it is enough to know that all data is complete, valid and accessible for the application. Typically subtle differences may pop-up with some data-types like those with date, time, binary fields and long text fields.

In a straight forward data migration, data from table a field a goes to the new system in the same table, the same field. A good check to validate this could be done by computing a MD5 check-sum on all fields of all tables, both in source and target tables. Any difference here shows there is a problem in the migration procedure or software. Make sure to have the same formats in place for the MD5 computation; for example, make sure that the date formats are the same on both sides.

The application has to be tested, that's for sure. What the strategy should be is hard to tell, every application has it's own challenges. If all logic is known and standardized, it could work by making a simple test application that accesses all different field types and see how the interaction goes. In the end a regression test will be needed.

Answer 2

Usually when migrating to database we applying 2 different tests:

1. Check sum, as written above to make sure all the fields that are measurable are the same in both databases. This is relatively simple since you use a group by statement, so your return set will be small.
2. Comparing all the fields - this is a more heavy test where we compare all the field in both sources. This is used especially to find the gap on field that cannot be sum or count like date, address, email, etc. By using this test we are also capable of saying which column and line are the problematic. Which is a huge time saver.

There are several challenges when you do it so keep that in mind, but there are of course solution:

1. How do you compare 2 DB2 and Oracle? Manually is not the right answer...
2. How do you do this validation in a rapid way? especially when you have dozens, hundreds and even thousand of table that were migrated? I got an example for this in this post: http://quality-gates.com/?p=1268
3. How to constantly make this data migration validation. Maybe there are some gaps that were discover on the first round. You would not like to rebuild all those tests again.

I hope I gave you some tips to assist you, good luck!