Problem solve Get help with specific problems with your technologies, process and projects.

Database migration from DB2 to Oracle

What are the steps of a database migration from DB2 to Oracle?

What are the steps of a database migration from DB2 to Oracle?
In migrating from any database platform to another, there are two basic steps one needs to take. But each of these basic steps lends itself to very complex substeps that can take a long time to complete.

Step 1: The data needs to be moved from one database system to the other platform. One common method is to dump...

the data to a vendor-independent entity. Comma-delimited files are typically used. Going from DB2 to Oracle, you can also use Oracle's Heterogeneous Services to pull data across your network from DB2 into Oracle. Creating tables in Oracle might mean that you have to map DB2's datatypes to Oracle's datatypes. For instance, a table in SQL Server that has columns defined as BIGINT and SMALLINT map to Oracle's NUMBER datatype. You might also have to look at re-creating your indexes in Oracle's defintions.

Step 2: Application logic needs to be migrated. Views need to be re-created in Oracle. Stored procedures and functions need to be ported over to Oracle's PL/SQL language. SQL statements that worked well in DB2 might not work as well in Oracle, so tuning might be required.

Above is the view of migrating from DB2 to Oracle from the 30,000-foot level. There are lots of details that I just do not have the space to describe in this forum. The best migrations from one database platform to another occur when the person(s) performing the migration knows both platforms really well. For instance, I know both Oracle and SQL Server pretty well. I have successully migrated applications between the two because I know that when I do this in Oracle, I have to do that in SQL Server to get the same job done. As an example, in SQL Server autoincrementing of a column's value is done with the IDENTITY datatype, whereas in Oracle, it is done with a sequence and a trigger.

Dig Deeper on Oracle database design and architecture