How to....

[vincent5]

Hello,

I'm using TIS 6.4.1 and my need is: Retrieve data from multiple DBs by running a sql file to populate a common database. The flow would be like this (I use a BDD informix but it is possible to take as exmple another BDD more used):

1 - To know the connection information of other BDDs, I read a table (included in the target DB) to value the connection variables of each BDD.

2 - On each database, I execute a sql file (the same for all), either one by one or see if I can do parallel executions.

3 - the result of each request is inserted in the same table of the target DB.

Thank you for your help because I have a little trouble building my job.

Regards,

Vincent

[Anonymous]

Hi Vincent,

The Subject of this question makes it hard for people with relevant experience to know to answer. As such, it is likely to be ignored. Can you update the title to something more meaningful? If you cannot do this, send me a title you would like and I can do it. Regarding the question, you need to break it down a bit I think. What exactly are you stuck with? You appear to have given the whole requirement. It is usually better to ask many small questions rather than one big question

Regards

Richard

[vincent5]

Hi Richard,

actually, I gave all the indications of what I wanted to do, because I do not know how to start .... The principle is to be able to do a generic Talend processing, ie a processing that would take into account a file containing a sql query (never the same, as needed) and that will have to execute it on several databases (different clients). All these data will have to be inserted in another and same database. This database or will be inserted all the data will also contain a parameter table containing the information of all the databases on which the data will be extracted. What title can I give to this description?

regards,

Vincent

[Anonymous]

That is actually multiple questions. As I said, it is best to break them down. For example, here are a couple of questions I gathered from your description. These are very different questions.

1. How to dynamically supply and use different DB connection details?

2. How to use dynamic queries for multiple tables (with different schemas) in a single job?

I was also not sure whether you know how to run jobs in parallel, or whether that is another question.

Essentially, this looks like a complete requirement. People do not generally answer those questions

[vincent5]

Spoiler

Hello,
Thank you for these indications. The heart of the problem is how to loop a sql query on different database.
Regards,
Vincent

[Anonymous · Accepted solution]

The way to do this is to create a list of DBs you want to query and their connection credentials. You can store this in another DB maybe. Then create a job which will return this data. This job will need to call a further job using a tRunJob component. Inside this job you will create a connection or connections using context variables and create your source and target components using those connections. This job will be run for each row supplied by the outer job's data. When the outer job runs, it will return a row for each DB you want to run the inner job for. You will pass the details to context variables inside the inner job. You can do this with the tRunJob quite easily.