This discussion is archived
3 Replies Latest reply: Jan 30, 2013 8:47 PM by r035198x RSS

Help me

882106 Newbie
Currently Being Moderated
Help me to solve this

I am involved in a project which is a data base inegration of db2 & mysql.
Here i want to imgrate data from db2 database to mysql database. I am not possible to use any other 3rd party tool like 'Pentaho'
So i have to write my own java project with jdbc queries.In my case db2 database is a legacy database it doesn't implemented fundamentals of the db designing. (ex:- keys, relaion ships)
Any how i have a mapping doc with descrbing table mapping of db2 and mysql

ex:-
"Bank Table" of db2 database mapped with mysql database "Bank Table" as follows

db2 side                                        mysql side
Bank Code      | Bank Name                    Bank Code      | Bank Name     | Date
------------------------               ----------------------------------
000               |     BOC                         000               |     BOC          |     SYSDATE
001               |     COM                     001               |     COM      |     SYSDATE

What i want is to write java appo read db2 database table and write to mysql db.

Tools i am using
Jboss 7.1 app server with hornet queue
Spring 2.5
Ejb 3.0

I herd MDB(Message Driven Bean) is a good soluion for implementing loosely coupled heteregenous systems together.

So what is the best method from follwing ways

1. Read the data from db2 database and put the result set the message queue & insert data o mysql database inside "onMesagge" method of the MDB

2. Pass a paramter and read it in the MDB and select data and insert

Note
Data rows may be 100000+ in some tables
Also have a requirements to use spring schdulers

What is the best mehod for implemening that?

Thank you
  • 1. Re: Help me
    EJP Guru
    Currently Being Moderated
    Please clarify. Is this a one-time data migration project? If so I can't imagine why you are looking at any solution beyond Java plus JDBC. You don't need any of that other stuff.
  • 2. Re: Help me
    882106 Newbie
    Currently Being Moderated
    No dear. This is not a one time data migration. it should configured with spring schedulers to continusly
  • 3. Re: Help me
    r035198x Pro
    Currently Being Moderated
    Now clarify as to whether this is copying from db2 to mysql only or if you have to synchronize the two, i.e if you have to copy from mysql to db2 as well.
    you have other functional questions to ask as well like
    Can there be duplicates and are you supposed to detect them?
    Are you allowed to extend the original tables with extra columns to mark them as processed?

    Personally I don't see the point of JMS here (it's best for integration between two Java systems and all you're doing here is DB work). I would also not use an application server for batch processing involving huge data, especially since you are already using spring which offers spring batch. Maybe with the container you'll gain with some transactional aspects if you can XA and only commit if you have both written to target and updated source as processed.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points