Saving MIPS by DB2 Data to Oracle for BI



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.














Comments:

Hello, Greetings! Good bye.

Posted by zkubfg on May 23, 2007 at 11:24 AM BST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

bocadmin_ww

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today