Top Ten Oracle Database to IBM DB2 Enablement Tips for DBAs


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

Rebecca Bond
Rebecca Bond
Rebecca Bond, an IBM Information Champion, industry recognized independent consultant and author of the only published book specific to DB2 LUW security, "Understanding DB2 9 Security", enjoys sharing technical lessons learned from her experiences in government, healthcare and financial consulting roles. Rebecca holds numerous advanced IBM certifications covering all aspects of DB2 and is an expert at balancing the twin needs of robust security and accelerated performance. Her unique background provides a wealth of pertinent database and security puzzlers, which she delights in helping us understand and solve via articles, blog posts and presentations.

Latest Articles