CODEWITHSUNDEEP
javasqljdbcsybase
Zuko
Zuko

Reputation: 2914

Data transfer from one Database server to another in Java

I don't really ask a lot of questions but This time, it's too much. Here's the problem. I have these two database (Sybase) servers and there's this database with over 90 tables but i need to archive only 20 tables.

These tables are however quite large and can contain up to 90million records. So here's the deal. Currently, what I do is that

Now, I've tried other methods like for instance.

  1. Up to now, the multi threading is just okay but the speed of archival is not good enough. for instance, it can archive up to 1.6M records within one hour. That is not good enough for my Boss.

Kindly advice on any other solution, approach or thought you'd think can help. Please not that all solutions are welcome.

Thanks in advance.

Upvotes: 1

Views: 3307

Answers (2)

Michael Kazarian
Michael Kazarian

Reputation: 4462

  1. Generate SQL executive script and pass it to database. It mean fetch all records from select statement and create insert/update statements:

String query = "UPDATE OR INSERT INTO TABLE (ID, VALUE) VALUES (9, 2) MATCHING (IDPRODUCT, COUNT); "+ "UPDATE OR INSERT INTO TABLE (ID, VALUE) VALUES (10, 1) MATCHING (IDPRODUCT, COUNT); "+ "COMMIT WORK;";

  1. If Sybase can connect to other Sybase instance create procedure for execute previous clause. For FierebirdSQL it possible through ON EXTERNAL and EXECUTE PROCEDURE with procedure name as parameter instructions.

The users need to monitor and know how the tool is running. For each commited table update user interface.

Upvotes: 1

Charles Shi
Charles Shi

Reputation: 9

Do not copy such amount of data by yourself. Create database jobs to copy/archive tables. And monitor the output/logs of those jobs in your application. It will be much faster.

Upvotes: 1

Related Questions