Go Back   Team-BHP > Around the Corner > Shifting gears > Gadgets, Computers & Software


Reply
 
Thread Tools Search this Thread
Old 11th November 2009, 10:04   #1
BHPian
 
Spinnerr's Avatar
 
Join Date: Feb 2007
Location: Bangalore
Posts: 339
Thanked: 120 Times
Default Tech Q: Syncing 2 DBs (DB2 and Oracle)

Hello people, DBAs,
Need some expert advice.

Situation:
We are migerating a mainframe system (using DB2) to Java (using Oracle 10g). Now the client wants to run the mainframe system and the Java system parallely for about a year, during this time we want both the databases to be in sync.

What is the best way to do this?

As we understand, the client will let its users access the system in both ways, so, some people would be using the mainframe version, some the Java version. Since it is accessed across the web, across continents, we want both the databases to have the latest data at any given point of time.

Ps: it is a shipping company, they manage, track their cargo/containers using this system.

* Running a batch job at some time of the day, isnt really a solution, i feel this way we would end up with lot of inaccurate data.
* Having update, insert trigger on Oracle is a way, but wont that increase a lot of n/w traffic? every action, wud invoke a trigger.

Is there some other way we can do this?

We really dont want to meddle with the DB2 database, one, getting access is not eazy, no test DBs to play with. We are open to all kind of solutions, be it Java, Messge queues, some other sync tool, anything else that you can think of.
Spinnerr is offline   Reply With Quote
Old 11th November 2009, 10:24   #2
BHPian
 
Join Date: Sep 2005
Location: Chennai
Posts: 399
Thanked: 3 Times
Default

Dear Sir,

Our advanced ENT-Demand(R) iCloud(TM) Enterprise Integration Platform v 11.3.23 delivers on-demand synergistic integration solutions through proprietary JSON formats and high-performance DSLs, leveraging our path-breaking innovations in BBC spectrum assembly and the latest Intel(R) 8085 processors.

Write to us at ENT-Demand@Bingo.com for a free brochure, or call us at 1-800-GO-BINGO to have our consultants respond to you!

Yours Truly,
Dr. Ballkey Bingo.
ballkey is offline   Reply With Quote
Old 11th November 2009, 10:35   #3
BHPian
 
Spinnerr's Avatar
 
Join Date: Feb 2007
Location: Bangalore
Posts: 339
Thanked: 120 Times
Default

Thanks for that.

Adding another note, ours is a development project, a fixed bid one, we are in the design phase, so was more looking at in house implementable solutions.

But yeah, if we cant think of an effective solution, we have to take professional support.
Spinnerr is offline   Reply With Quote
Old 11th November 2009, 10:53   #4
BHPian
 
Rotorhead's Avatar
 
Join Date: Sep 2006
Location: Bangalore
Posts: 925
Thanked: 17 Times
Default

Just thinking aloud here. as i understand the front end to the client is through the web, so how about writing a small adapter in your current server side implementation which will ensure that the data is written both onto your DB2 database as well as to your oracle database simultaneously using tcp sockets (if both the DB2 and oracle databases are colocated) and have a offline transaction log report generated at the EOD to verify if the data is in sync. Also you will need to consider the number of transactions/load etc to arrive upon a defenite solution. Also i am not sure how the oracle triggers will increase the load on the network which i assume you are talking about your internet nw. Going by your statement I understand that the db2 and oracle database servers will not be colocated in the same premises.
Rotorhead is offline   Reply With Quote
Old 11th November 2009, 11:21   #5
BHPian
 
Spinnerr's Avatar
 
Join Date: Feb 2007
Location: Bangalore
Posts: 339
Thanked: 120 Times
Default

The database servers will not be colocated in the same premises, you got that correct.

The solution that you had provided is a start, I shall think on those lines.

Just clarifying a few things. With the Adapter, I ensure that whatever is entered / modified from my Web Client goes into both the DBs. But how do I sync the Oracle DB with the data entered from the Mainframe client to the DB2 database?

N/W traffic and Trigger, I am not very sure about that. I was thinking on the performance issue lines, felt that, since the 2 databases are in different locations, the triggers would be an additional load for every transaction.

Its not just Oracle Trigger, my plan was to have have triggers in both the Databases, where every update/insert in one database would update/insert the same data in the other Database. Concurrency is another big thing.

Last edited by Spinnerr : 11th November 2009 at 11:25.
Spinnerr is offline   Reply With Quote
Old 11th November 2009, 11:55   #6
BHPian
 
Spinnerr's Avatar
 
Join Date: Feb 2007
Location: Bangalore
Posts: 339
Thanked: 120 Times
Default

An update: just found a solution, rather read abt it, using 'Oracle Stream' to sync heterogeneous Databases.

Have to do some reading to understand what is what. If you guys have any links / info, plz do share.
Spinnerr is offline   Reply With Quote
Old 11th November 2009, 13:00   #7
Senior - BHPian
 
NetfreakBombay's Avatar
 
Join Date: Jan 2008
Location: Bombay
Posts: 1,375
Thanked: 315 Times
Default

Keep in Mind that this is an Implementation of "Master Master Replication".

That would mean that DB would not be able to Guaranty ACID for transactions. This would significantly increase support costs. This cost would be due to manually handling replication conflicts.

And since both systems can modify ANY data, you can not setup automatic rules.

Like :

1.DB instance A Wins for rows related to London Office, refuse concurrent edits by any other office
1.DB instance B Wins for rows related to Chicago Office, refuse concurrent edits by any other office
NetfreakBombay is offline   Reply With Quote
Old 12th November 2009, 18:57   #8
BHPian
 
Rotorhead's Avatar
 
Join Date: Sep 2006
Location: Bangalore
Posts: 925
Thanked: 17 Times
Default

Quote:
Originally Posted by Spinnerr View Post
An update: just found a solution, rather read abt it, using 'Oracle Stream' to sync heterogeneous Databases.

Have to do some reading to understand what is what. If you guys have any links / info, plz do share.
Just read up on the oracle streams, and yes it defenitely seems to work for sync of heterogeneous databases, but i am not sure if this would work over remotely located databases. I am not sure if this sycn up/exchange of data happens over the http protocol. Again the concern in cases it uses the internet backbone then again the question arises on the security of data unless you have some kind of private network setup. When the databases are colocated then there are a whole lot ways of implementing the same but considering that the two databases can be located in two different locations altogether poses an entire set of new issues. I have not worked on mainframe systems, so can you kindly let me know if that too follows a client server approach as in java based GUI apps, where the data is entered in the client side but the actual data updation to the database at the server end ?

as far as i can imagine the proximity of the databases is a big factor in determining what you want to go for. If you have a VPN that supports good bandwidth then you have multiple options such as the oracle streams, or a simple socket based data transfer. But one more question is that the sync up needs to happen from DB2 to oracle or does it have to be vice versa too, since you mentioned that the customers will be using both the interfaces so are there no restriction policies or both interfaces can update delete or modify records inserted from the other database? in that case you have quite an issue to ensure the data sanity which again will have to go through some reconcilliation process periodically.
Rotorhead is offline   Reply With Quote
Old 13th November 2009, 10:55   #9
BHPian
 
Spinnerr's Avatar
 
Join Date: Feb 2007
Location: Bangalore
Posts: 339
Thanked: 120 Times
Default

Quote:
Originally Posted by Rotorhead View Post
I...can you kindly let me know if that too follows a client server approach as in java based GUI apps, where the data is entered in the client side but the actual data updation to the database at the server end ?

...proximity of the databases is a big factor in determining what you want to go for.
Yes, you got that part right, mainframe has a client-server architecture.

And yeah, with every reading, and your comments, I realized having both the databases in the same location can address a lot of our concerns.

I guess for now, we would have to specify different possible ways, with their pros & cons, complexity & risk.

Anyway this is not high priority right now, but yeah before the actual Service layer development, we have to have this sorted out.
Spinnerr is offline   Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads
Thread Thread Starter Forum Replies Last Post
Special 'Carbon Black' Editions of AM's V12 Vantage and DBS! suhaas307 The International Automotive Scene 8 19th December 2009 17:17
Need help removing Oracle sukrit7 Gadgets, Computers & Software 2 16th May 2009 02:06
Cheating by PeopleSoft Trainer ERPGuru: Oracle takes action! Samurai Gadgets, Computers & Software 31 10th April 2009 02:51
The GURU of Oracle comes to India bblost Shifting gears 9 12th November 2008 09:42


All times are GMT +5.5. The time now is 02:57.

Copyright 2000 - 2017, Team-BHP.com
Proudly powered by E2E Networks