Creating migration jobs for each schema, not for each table

[StreetTalender]

Hello,

I am new to Talend, and need help to design a migration job.

Is there any way to create a job which lets me migrate a whole schema, not only a table?

I have about 5000 tables within 10 schemas to migrate, and I want to avoid creating 5000 jobs one by one.

The immigration requirement is simple, let’s say I have a schema named “A” in the source DB, and and I want exactly the same schema “A” (including all tables and their data) in the target DB.

Environment:

• Talend Studio 8.0.1
• source and target schema are both Oracle

I would really appreciate if anyone can help me!

[Anonymous]

Hi

You need Dynamic schema functionality which is only available in Talend subscription products, you can read a scenario in this page.

The job design looks like:

main job:

tForEach--iterate--tRunJob

iterate each schema from a list and pass the current schema name to child job.

child job:

tOracleConnection1-oncomponentok-tOracleConnection2

|onsubjobok

tOracleTableList--iterate--tOracleInput--main--tOracleOutput--oncomponentok--tOracleCommit

|onsubjobok

tOracleClose1--oncomponentok--tOracleClose2

Regards

Shong

[StreetTalender]

Dear Shong,

@Shicong Hong​  

Thanks a lot for the help!

If you don't mind, I would like to know about settings of this part: tOracleTableList--iterate--tOracleInput--main--tOracleOutput

1. Regarding tOracleTableList, I am not sure what I should choose in “fetch from” field. Can you point it out for me? I couldn’t find documentation for this field.
2. Regarding tOracleInput, I’d like to know what to write in “Table name” field and “Query“ field.
3. Regarding tOracleOutput, I assume I should put the same table name I use in tOracleInput. Is that right?

Sorry for asking many questions, but I would really appreciate if you could lent me your hand.

Best Regards

[Anonymous]

@Miyo Takahashi​ ,

1. 'Fetch From' field is used to filter the tables, user_tables: only select the tables created by users, all_tables: system tables+user tables, custom query: customize the query by yourself to filer the tables.
2. About the basic usage of tOracleInput, please refers to the component documentation.
3. Yes, you are using tOracleTableList to iterate each table, there is a global variable eg: ((String)globalMap.get("tDBTableList_1_CURRENT_TABLE")) that stores the current table name, you should use this variable in the table name field on tOracleOutput.

Regards

Shong

[ryota0000]

Dear Shong,

@Shicong Hong​ 

Thanks a lot for the help!

I was able to migrate the table. thank you.

I want to migrate SYNONYM as well, but I can't do it this way.

Is there a way to migrate SYNONYM in a similar fashion as tables?