Possible to bring in another table from a different Oracle source into the same target DB?

[desmondchew]

I have setup replication from an Oracle DB source database eg: ABC, target DB is a MySQL database. The source schema let's call it schema: HR.

So application team require a table(ADMIN) from another source Oracle database XYZ. We have created a dblink that allows schema HR to access to this table - ADMIN from Oracle XYZ DB, in database ABC using a synonym.

When I try to replicate the table using Qlik Attunity I was not able to do so as the replication GUI couldn't find the table.

Please let me know if there is a workaround to bring in different tables from another source?

[KellyHobson · Accepted solution]

Hey @desmondchew 

Replicate supports one source endpoint and one target endpoint connection per task.

For your original task, this is OracleABC -> MySQL_Target. Where OracleABC has connection string, user, password, configured for ABC.

Thus if you would like to replicate from another Oracle source DB (XYZ), you need to create a new source endpoint for XYZ and a new task which uses XYZ as source.

OracleXYZ -> MySQLTarget

When you set up the second endpoint connection and task, then you will be able to search for the schema and tables you are interested in.

Let us know if you have any additional questions.

Best,

Kelly

[SwathiPulagam]

Hi @desmondchew,

From the Replicate task, we can only load the tables from one database and there is no option to get the tables from other databases. The only option is to create a separate task.

To be more specific Replicate can only see tables and views and not the synonyms. 

Thanks,

Swathi

[Michael_Litz]

Hi,

Just wanted to let you know "if" the table was in the same database you would be able to add a field from it to the original table by using a field level transformation (date enrichment) function called source lookup.

Here is a link to a community article that will help explain how this would work.

Transformation: Source Lookup - Oracle ROWID
https://community.qlik.com/t5/Knowledge/Transformation-Source-Lookup-Oracle-ROWID/ta-p/1805170

[SwathiPulagam]

@desmondchew  As I said before, Replicate can't see synonyms so you can create a view from an existing synonym and create a replicate FULL LOAD only task to replicate the view.

NOTE: Views can be replicated only in the FULL LOAD task.

[desmondchew]

Hi,

I have tried creating a view on the synonym that points to another database via db link. Qlik GUI doesn't retrieve the view from "Table Selection"

[SwathiPulagam · Accepted solution]

@desmondchew  You can't see the views in the Full load + CDC task. Please create a Fullload-only task and try to view the objects again.