Migrating from Oracle to DB2 – A High-level Guide
Alpine has had the opportunity to migrate moderate sized transactional Oracle databases to DB2 v10.5. One client was moving to a new hosting site and was using WebSphere Commerce (WCS) with an Oracle database. Since DB2 is bundled with WCS, it made sense for them to migrate to DB2 to get rid of the Oracle licensing fees.
We used the IBM Database Conversion Workbench (DCW) which is a plugin for the IBM Data Studio product. As anyone who has dealt with both Oracle and DB2 databases knows, there are some significant differences in certain data definitions, including some different data types, as well as SQL and stored procedures differences. The DCW tool offers a way of analyzing your Oracle database including data types, stored procedures, functions, etc. The resulting reports show potential compatibility problems allowing you to be pro-active on changes where you can and making post migration changes where necessary.
These are the high-level steps to run to get your database migrated:
- The first step is to run the task to analyze the Oracle database. This is very important as this step analyzes the Oracle objects and produces 2 reports – one of which shows the invalid objects like functions in the Oracle database that will not migrate over to DB2. The other report is an SQL script that is analyzed to determine where any other issues may occur like with data type conversions. Run the evaluate compatibility job to analyze the script to see what data types, etc. are not compatible between the two databases.
- Run the convert code task to create a “converted” SQL script that will be used to create the objects in DB2. You can modify this script to correct anything that was not automatically corrected such as certain data types. We found the Oracle Number data type does not equate to a specific data type in DB2 so you will need to modify the script to specify the data type you need in DB2 based on the current size and number of decimals defined in Oracle.
- Once your scripts are ready, you can run these scripts on your new DB2 database to create the tables, indices, etc. Note you must create the actual database prior to running the scripts.
- Next, run the Extract task to extract the data from Oracle. Depending on how large your database is, this can take a significant amount of time. One delimited flat file is created for each table and a separate file(s) is created to extract any LOB type columns for that table. We found using the | (pipe) character as the delimiter worked the best for our data content, but you can select the delimiter that works best for your data.The Extract task also generates a script that you will use to load the data. It consists of a DB2 load statement for each table in the extract.
- Load the data into DB2 by first uploading the data extracts to the DB2 server along with the load script(s) mentioned in step 4. Running the load SQL scripts will load the data as well as generate logs that should be reviewed later.
- Review the logs for any errors and correct.
- Run any custom scripts or SQL to complete the migration (create incompatible functions, stored procedures, etc.)
- Confirm the database is as expected.
There is a lot of uncertainty and stress associated with database migrations, and if you’re feeling overwhelmed, we’re here to help. Drop us a line!
Debbie Hamning – Sr. Consultant, Alpine Consulting, Inc.