Jason's Blog on Oracle Modernization Solutions

First
I wanted to make a quick reference to a good article
written by Mark Wilcox
on how we can be more of a "blogging company", which is my
inspiration for this particular post. So, this keystroke saving
effort is my effort to help break
down those walls.

Recently
there have been some exchanges around our shop and from some
customers on ways/options to get at mainframe db2 data.  Of
course a lot depends upon what you are running but the general
scenario was this:


I'm running DB2 on the
mainframe...version X. I need to get this data off the
mainframe....not migration, but access to this information. My batch
window is getting filled up with other jobs, and I don't have time to
run reports, plus, I don't want to spend MIPS running reports when I
can do this on Linux for free. Oh yeah, the data (in this case) was 2
days stale because of batch transfers/windows and they needed
something more real or near time than this.


So we are
looking at doing, in the cases of my recent email exchange and a
current project with a customer, doing a Change Data Capture (CDC)
strategy using minimal 3^rd
party products. That was the key driver here.

Ideally,
the customers (in my case) didn't want to use anything other than
what they already had...namely, DB2 on the mainframe and Oracle on
Sun.  They did not have Information Integrator (Which is an
option...you can run a small cobol program to capture changes and
push that through)

So,
there are a couple of options here.
First, we need to create a
good way to identify changes, capture them and them push (or pull)
them off the mainframe. 

In
this case, there were some fields indicating updates/date time. So
that is good. Made the job pretty simple. Grab the changes (after an
initial dump of course)

So,
once that is done, what are my options to getting to the data and
putting it into Oracle.

1. Connect
   to DB2 directly via the IBM DB2 Universal JDBC driver and treat it
   like any other database.
   (IBM provides Java support on the z/OS
   and iSeries)
   
   
   

   
   

2. Extract
   data as flat files and use ODI's comprehensive flat file handling
   capabilities to handle the data.
   Out-of-the-box EBCDIC, ASCII
   support, delimited, fixed width, XML, etc.
   This is popular with
   customers who handle large amounts of data.

   
   

3. Use
   a 3rd party bridge from Attunity or iWay (aka Oracle Adapters) in
   conjuction with OWB or Sunopsis,but in this case, that wasn't an
   option.

   
   

4. Use
   Triggers on DB2 for Change Data Capture. Then build custom routines
   to generate Streams LCR records.

   
   

The
key in our situation was that there needs to be JDBC connectivity on
the Z Series. That would allow up to get to the changed data, once
you solved the notification of change problem.

The
project mentioned above is just starting out, so we'll continue to
let you know what was optimal in this situation. The prospects look
much more promising to be able to reduce MIPS on running reports, and
getting better BI at the end of the day.