topic Re: Copy multiple tables from one database to several other databases in Oracle in Talend Studio

Copy multiple tables from one database to several other databases in Oracle

Anonymous — Sat, 16 Nov 2024 02:44:38 GMT

I need to create a job using Talend Studio (7.2) to copy multiple tables from one database to several other databases in Oracle.

For example:

Here are the databases in Oracle:
databaseA, databaseB, databaseC, databaseD,,,,, databaseQ

Here are my sources tables in databaseA:

HR.Employee, HR.Department, HR.Location, HR.Salary, HR.Customer

The requirements are:

1) Copy these HR.Employee/Department/Location/Salary/Customer tables from databaseA to

databaseB, databaseC, databaseD,,,,, databaseQ.
2) Run the Talend job daily to remove rows that no longer exist in these source tables in databaseA &
Insert new rows and update changed rows into corresponding tables in the target databases (B, C, D,,,Q) --- so we can have the same data across different databases.

I created a job which will copy HR.Employee/Department/Location/Salary/Customer tables from databaseA to databaseB.

I am wondering if I should modify this to be a child job and then create a parent job to call this child job several times to copy tables to the rest of databases?

But how should I pass the information to establish DB connection for different databases ( my only concern would be the Database passwords).

Looking forward to your suggestions.

Thanks!

Re: Copy multiple tables from one database to several other databases in Oracle

manodwhb — Thu, 09 Apr 2020 06:48:56 GMT

How are you planning to this there are different approach based on your data and which you need to choose. Are you planning to design multiple jobs or single job?

Re: Copy multiple tables from one database to several other databases in Oracle

Anonymous — Thu, 09 Apr 2020 16:34:18 GMT

A Parent job and one single Child job to move multiple tables from databaseA to a targetDB.
Here is my current design:

I listed the target databases (B, C,D until Q) and their host info and a sequence field (1,2,3, ...) within tFixedFlowInput ("Target Databases"). The passwords are kept on a txt file with 2 fields (sequence, password). Using a tMap to get all database info and generate global variables and then load them to contexts for database in the Child job.

Child job has several subjobs to do Delete/Insert/Update in the targetDB tables.
The prejob will establish database connection for databaseA and a targetDB.

Please give comments on my approach and let me know if you have better ideas. Thanks!

Re: Copy multiple tables from one database to several other databases in Oracle

kotvir — Wed, 06 Oct 2021 00:47:03 GMT

Is there any other way rather than creating input and out for every table in Child job in case we have thousands of tables to deal with ?

Note: Don't wan to use Dynamic Schema