This week's book giveaway is in the Spring forum.
We're giving away four copies of Spring in Action (5th edition) and have Craig Walls on-line!
See this thread for details.
Win a copy of Spring in Action (5th edition) this week in the Spring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

Searching for the most efficient way to transfer data from one DB to another  RSS feed

 
Ranch Hand
Posts: 158
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello everyone,

I am facing with the following issue:

I have two Databases, that have a table of the same form.

For example

Database A, Table 1


Database B, Table 2 has the same columns.
I want to run the program every 1 minute and to compare all the values of the two databases, and i something does not exist in Database B to add it.

Do you know what is the most efficient way to do this?

I though of the following:

select all values from Database A
in the  
select the value from DB B to see if the record exists.
If it exists then move on to the following record.
If it does not exist then insert the record in Database B.

My way is a little time consuming.

All ideas are welcome

Thank you,
Ioanna
 
Marshal
Posts: 61753
193
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would have thought there should b something about that in a standard databases tutorial.
But why do you want the same data twice? That means you have two copies and if one copy is altered, you now have different data in the two places ,so you have to regard your data as corrupted

Moving to our databases forum.
 
Ioanna Katsanou
Ranch Hand
Posts: 158
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is it a good idea maybe to select all the names from DB B, and store them in a SET

and then while iterating through the resultset from DB A , check if name exists in SET and if it does not exist then insert the record?
 
Rancher
Posts: 1916
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What about a database trigger?
 
Ioanna Katsanou
Ranch Hand
Posts: 158
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, I need to handle this within the Java program
 
Rancher
Posts: 3757
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Where are these two databases?
Can they talk to each other?
What database is it?
 
Ioanna Katsanou
Ranch Hand
Posts: 158
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
They are both Oracle DBS and they cannot talk to each other
 
Swastik Dey
Rancher
Posts: 1916
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Should it compare for all the columns?
 
Ioanna Katsanou
Ranch Hand
Posts: 158
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The first column is considered to be the key, so only for the first,
but it should insert all columns


 
Saloon Keeper
Posts: 5043
134
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Take a look at ETL tools (Extract, Transfer, Load) - this is one of the scenarios they're made for.
 
Swastik Dey
Rancher
Posts: 1916
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In case you are not allowed to use the tools, what if the following approach is used.

1.  Create a list of POJO class that holds all non matching records (not found in database2 table2)  from database1 table1.

2. Now user batch update to insert rows to database2 table2 using the list created above.
 
Bartender
Posts: 19988
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Ioanna Katsanou wrote:
I want to run the program every 1 minute and to compare all the values of the two databases, and i something does not exist in Database B to add it.

Do you know what is the most efficient way to do this?



Yes. Tell whatever "expert" commanded you to do this in Java that a database trigger is MUCH more efficient AND reliable for that sort of thing.

I've had a similar situation with 2 Oracle databases because our Oracle Financials database was hermetically separate from our regular production database but we did a lot of cross-database work.

I don't promote stored procedures as a rule, since they have some very definite downsides. But there are certain cases where they really are optimal solutions and this is one of them. Especially since you have such a high update frequency. I'd feel differently if you saved up all the changes and posted them at the end of the day or something like that.

Using a Java app means that you have to inject a third server into the equation above and beyond the source and destination DB servers. It means that you have to be able to depend on that server's being online and able to dispatch frequently. That both the app and the JRE are properly installed and secured. And that the code and JRE are kept maintained. It's not merely a less efficient use of machine resources, it's also consuming the human resources needed to keep all of that maintained. A trigger is much less work, and since it's running internal to the DBMS, it's already got direct access to the low-level functions needed to detect and transmit changes.

Also, you might check and make sure that the version of Oracle you're using doesn't have a built-in table mirroring facility. Modern-day DBMS's have some surprising capabilities.

Now for cases where you have more complex requirements, as Tim Moores has said, an ETL tool is more efficient in many ways than a one-off Java application. I haven't worked with Oracle's ETL resources since my employer was too cheap for that, but I have done extensive work with Pentaho DI ("Kettle") from Hitachi. Kettle can move data from just about anything to anything - DBMS's, Excel spreadsheets, Amazon-based data warehouses, CSV files, and more, converting and cleaning data along the way. It can also optimize operations by distributing and parallelizing them. I've used it for all sorts of tasks - for example, a client hourly runs a Kettle job that dumps a DB2 database to CSV files, packs them all up into a single ZIP file and FTP's the ZIP file to a third party. And if you have to "write" a Java program, it can run embedded. I've done that as well.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!