2

Premise

I have a number of database servers (different DBMS instances, installations in different physical locations), each of them contains data and uses the same schema. Most of the data is completely the same across all databases, but unfortunately not in every case.

I need to write an application that will issue queries (only selects, not inserts, updates or deletes) against a table across all databases, retrieve all results so that the tool can compare them to be able to report back possible discrepancies to users.

Use case

For example imagine that I have a web based form, where users enter company details. Submitting the form will issue an insert against the same table in all databases (via different database connection, each of them separate). While users typing the company's name the application needs to be able to verify that the value entered doesn't exist across all databases. If it exists or doesn't exist on all databases all is fine. Either the company name is taken or can be used by the user. However if the value exists on some databases, but not on others I have to raise a validation error.

My plan

The tool I'm writing is a Java application, using JDBC (connecting to multiple Oracle databases), so right now my thinking is that I create a Connection towards each of my database servers. Issue the same query in parallel, wait for the results and compare them. My use case involves a handful of users and 5-10 different database servers (database migration or slave/master replication is completely out of question).

In the past I've answered someone's question which required a solution similar to what I'm thinking I should do right now.

Question

What are the possible ways to handle a scenario like this?

Improve this question
8
  • 2
    Are you trying to implement replication, a feature that Oracle already supports?
    – Pieter B
    Commented Jan 4, 2016 at 16:16
  • Do you need threads? Can't you sequentially query one database at a time?
    – Tulains Córdova
    Commented Jan 4, 2016 at 19:25
  • 3
    Seems like you have it all worked out. What are you asking?
    – John Wu
    Commented Nov 30, 2016 at 1:11
  • 2
    Why, write an application that uses multiple database connections and executes queries in parallel, of course. But you said that. Not sure what else there is to work out. Are you asking for alternatives? Or are you asking what code to write?
    – John Wu
    Commented Nov 30, 2016 at 16:07
  • 2
    This question can't have a single correct answer, hence it is unsuitable for QA format.
    – Basilevs
    Commented Dec 30, 2016 at 3:39

4 Answers 4

Reset to default
2

I think what you are looking for is are Oracle Data Base Links.

CREATE DATABASE LINK

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system.

Improve this answer
1

All the RDBMS products that come to my mind have a feature to link databases and query across multiple databases. If this is the only requirement you have and can't foresee any other requirements for cross-database querying, I personally wouldn't rush to linking databases to get this done. I'd consider all the potential downsides and think carefully. I'd be concerned about introducing a performance impact since the databases aren't in the same location, and also potentially making the product harder to support. The latter will depend on how your IT organization is structured and any standards/practices laid down by DBA's.

If not going down the linked database path for this requirement, I'd kick off background threads in parallel each of which connects to and queries each remote DB for similar data.

Establishing the connection to each DB is a fairly costly operation. If your app already connects to each periodically, then database pooling would help as the DB pool would keep connections open and available.

Another thing to consider is that it sounds like your app doesn't own the databases and the data. Obviously, whatever route you take to hook into the back-end database of this other owning app, you'd be depending on the other app to not transform its data model in a way that breaks yours.

Improve this answer
0

Is your application the single point of updates into all the databases? If not, then you will have a lot of issues anyways with dups.

On the other hand, if your app is the single point of updates, then you could use a centralized cache using NoSQL, and use it as a write-through cache, before you save updates to the various databases so that you only need to query that one cache to check for dups.

Improve this answer
1
  • Is your application the single point of update? Not at all. My app won't modify data on the databases. It needs only to determine if all databases are consistent in regards to each other. Not on the whole database level. So for example the app would need to check if a given record in a table is same across all databases or not.
    – Kohányi Róbert
    Commented Dec 5, 2015 at 15:01
0

There's an alternative to threads.

The use case you explain doesn't seem to need be synchronous.

So you could make a script that reads a database and writes to a temporary table. Such a script would recieves the connection info via parameters passed to it. That way you can schedule cron jobs with the same scripts but different databases that run in parallel at a given time.

A second job would poll to see if those jobs finished and then do the comparison and store the results in a single-point table.

The data-input form could check with that pre-processed tables instead and, if the data is too old (configurable), tell the user to wait for a notification in a notification area to give them a sense of workflow.

It's just a possible alternative.

Also if the input form shows the user a drop-down list of existing names you can save the user confirmation time.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.