Saving MIPS by DB2 Data to Oracle for BI
By jasonw on May 10, 2007
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.
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 3rd
party products. That was the key driver here.
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)
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.
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)
once that is done, what are my options to getting to the data and
putting it into Oracle.
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
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.
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
Triggers on DB2 for Change Data Capture. Then build custom routines
to generate Streams LCR records.
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.
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.