Top Ten Oracle Database to IBM DB2 Enablement Tips for DBAs

Wednesday Apr 14th 2010 by Rebecca Bond

With IBM DB2 9.7's new compatibility and enablement features, DBAs tasked with moving Oracle databases to DB2 will find out-of-the-box "enablement" functionality that streamlines tasks and fast tracks time to completion.

With IBM DB2 9.7's new compatibility and enablement features, DBAs tasked with moving Oracle databases to DB2 will find out-of-the-box "enablement" functionality that streamlines tasks and fast tracks time to completion.

Running the Oracle to DB2 Enablement Trail

When I saw the press release about Oracle to DB2 enablement features that were included in the DB2 9.7 LUW product, I was pleased. I’ve converted a few databases myself and I don’t look back on those as pleasant experiences, either for me or for my Oracle DBA counterparts, so this was welcome news. I knew what the word “conversion” meant; I had done that, but what did the word “enablement” really mean from my DBA perspective? I brushed off my old “conversion” blinders, picked up my snazzy new “enabled, no-line bifocals”, put on my trail running sneakers and began to run the Oracle to DB2 Enablement Trail from mile Marker #10 all the way to the finish line.

At each mile marker, I stopped to enjoy the view and even took a couple of pictures. Here’s my travelogue.

Trail Marker #10

ORACLE FORMS: The trail took a bit of a twist here when I noticed some folks were using Oracle forms. Fortunately, I was able to jump over that issue since there is an automated conversion translation tool that converts these to Java while maintaining the original form’s “look and feel”.

Trail Marker #9

OCI: Another minor twist in this trail just appeared. It seems that some shops still use the Oracle Client Interface to connect to their databases. With IBM DB2 9.7 at Fixpack 1, the road ahead straightens out if you use the OCI compatible client, DB2CI. This trail is going to have to get a lot more challenging than this to stop us, but based on what I see, we will reach the finish line in record time!

Trail Marker #8

NEW DATA TYPES: One of the old “conversion” boulders that invariably wound up in the path when moving from Oracle to DB2 involved Data Type Mapping. Previously, the process of converting from one data type to another took some serious SQL skills, an understanding of the data and how it was used in the application, and often, a significant amount of DBA time. The great news is that with the new, “extended” data type options that are available in DB2 9.7 there is little, if any, data type mapping work necessary. These new, extended data types will provide native support for the majority of the Oracle applications running today.

Trail Marker #7

NEW FUNCTIONS: No need to say goodbye to DECODE or adieu to opportunities to perform interesting DATE arithmetic using the Oracle flavored functions. In fact, there are a lot of new DB2 functions to make Oracle applications feel right at home on their new DB2 databases.

Trail Marker #6

CLPPLUS: I know that Oracle DBAs love writing and editing complex PL/SQL scripts in SQL*Plus and who could blame them? The path ahead may seem like a steep climb, but relax and enjoy the view. Transferring all those awesome scripts to DB2 is not going to present any challenging vertical ascents. The CLPPLUS command line processor, with its SQL*Plus-compatible command options, will become your new best friend. If you are a DB2 DBA who hasn’t tried out CLPPLUS yet, you should. With CLPPLUS, you have a robust command line interface that offers variable substitution, column formatting, reporting functions, control variables, easy connection options, great scripting/tracing opportunities and more.

While we’re here, let’s stop to take a quick look at the scenery. Did you notice the support for PL/SQL packages via built-in package libraries?

Hold on a second while I take a picture. Smile (oh, you are already smiling. Never mind).

Trail Marker #5

MULTI-PLATFORM and SCALE OUT: DB2 LUW is an athlete. You’re not locked into a single operating system platform. Want to run your database on Linux, no problem. Prefer Windows? No worries. Is AIX your platform of choice? Of course, DB2 LUW can handle that. Moreover, if your company becomes successful beyond your wildest dreams, DB2 is highly scalable.

Trail Marker #4

MEET DB2: We’re more than half way through the run and it’s time for us to get a score. MEET DB2 can do just that. It can analyze all of the objects in your Oracle databases and score them. The resulting report will let you know exactly where you stand in regard to DB2 enablement. MEET DB2 will provide an HTML report that will list the number of objects, if any, that will need a tweak or two to make them happy runners. Fortunately, there are typically few that need our help. No need for us to linger here. We need to get back on the trail.

Trail Marker #3

IBM DATA MOVEMENT TOOL (IDMT): At this point in the run, you may start to get weary. Don’t despair. This is the easy part and, at most, you will only encounter a pebble or two on the trail. Using the free IBM Data Movement Tool GUI, the DBA can connect to both the Oracle and DB2 databases. Then it is a simple task to use the GUI to drag and drop tables, packages, or entire schemas from Oracle onto DB2. The exceptions are few and often only require minimal time to address. If you’re not a fan of GUIs, the command line option is available and enables scripting capability.

“Enabling” your DDL (e.g. tables, procedures, functions, triggers) is a sprint, not an endurance run. More great news, the IDMT also extracts the table data from the source Oracle database (using a speedy multi-threaded technique) and generates DB2 LOAD scripts to allow you to populate your tables quickly. We are unstoppable; let’s run on.

Trail Marker #2

CONCURRENY CONTROL: This is a treacherous part of the trail. There have been rockslides here from time to time, but I think they’ve been mitigated now. In the past, the locking semantics for Oracle and DB2 databases were quite a bit different. With Version 9.7, DB2 supports locking mechanisms that will be familiar to Oracle DBAs who expect that writers won’t block readers and readers won’t block writers. Now, if an uncommitted row-change is found, DB2 can use the currently committed version of the row to eliminate a potential wait situation. An added benefit we get from this new concurrency control functionality is that there is no need for a rollback segment or an “undo” tablespace (or any other new objects) to support this functionality.

Trail Marker #1

TRAINING: As we approach the final bend and pass Trail Maker #1 heading to our goal at the end of the trail, it is important to realize that while today’s journey will soon be over, there will be another run tomorrow. Fortunately, DB2 has a robust, helpful user community and there are numerous opportunities for self-education. This is in addition to formal classes that are offered by vendors and by IBM training. In fact, there are so many DB2 training resources that we could spend days just exploring them. Here are a few of my favorite ones:

Serge Rielau’s Developer Works Article (Excellent overview and links to more information)

Redbook on DB2 to Oracle Enablement

Susan Visser’s Skills Blog (Learn about training options)

Learn while being entertained with Scott Hayes' DB2 Night Show

In addition, you’re always welcome to enjoy my personal travelogue.

Now I understand the definition of Oracle to DB2 “Enablement”. It simply means that changes are the EXCEPTION not the rule!

I see the finish line tape. Let’s sprint! YAY! Another successful trail run completed.

» See All Articles by Columnist Rebecca Bond

Mobile Site | Full Site