How do I share a database connection with sub jobs?

One Star

How do I share a database connection with sub jobs?

Hi
I want to use a connection in a master job that will be used by all of the sub jobs. In the connection component in the main job I have checked the "Use or register a shared DB connection" and entered a name "Master".
I have added a connection component to a sub job and done the same thing. The database output component uses existing connection and refers to the connection component on the sub job. (See screen shots)
The job runs OK but no data is present in the database. I don?t think I have set up the shared connection correctly.
Any help gratefully received.
Seven Stars

Re: How do I share a database connection with sub jobs?

You have a tOracleRollback component but I don't see a tOracleCommit component. You'll need one if you didn't set the tOracleConnection to Auto Commit.
One Star

Re: How do I share a database connection with sub jobs?

Hi Alevy
The commit is in the master job.
Is the aproach I have used the correct one? i.e. ticking the check box and using the same name in the connections?
Many thanks Dave
Community Manager

Re: How do I share a database connection with sub jobs?

Hi
You share and use the db connection correctly, if the connection name does not exist, it will throws exception when you execute the job. Can you upload a screenshot of master job? I want to have a look at the job design of master job.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: How do I share a database connection with sub jobs?

Hi Shong
I have attached the master job.
Many thanks for your help.
Dave
Community Manager

Re: How do I share a database connection with sub jobs?

Hi
Are there records extracted from tOracleInput? If no, the tRubjob will not be fired. Don't know why you link a main from tJavaRow to tRunJob.
For me, the job design should be:
master job:
...
|
onsubjobok
|
tOracleInput--main-->tJavaRow--tFlowToItere--iterate--tRunjob
|
onsubjobok
|
tOracleCommit
or
...
|
onsubjobok
|
tOracleInput--main-->tJavaRow
|
onsubjobok
|
tRunJob
|
onsubjobok
|
tOracleCommit
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: How do I share a database connection with sub jobs?

Hi Shong
What I am trying to do is have potentially large number of jobs in the project with each one being responsible for extracting data from a source table and then landing it in a staging area. We will decide how many of the jobs need to run by working out if it is worth visiting a table (is there any data to extract?) and passing this into the tRunJob component to only run the jobs we require. This is what the tOracleInput does it retrieves the job names of the jobs to be run. I have added the tFlowToItere to the job but it still does not load any data. If I change the sub jobs such that the tOracleOutput does not use an existing connection then it works fine.
I dragged the tOracleConnection from the Oracle database entry in the projects MetaData so it has all of the context vairables set. I did try changing this to a tOracleConnection from the pallet but it then could not connect. I have added a different view of the master job with the note showing what is in the tJavaRow.
I think the problem is the way I have set up the shared connection.
Any help gratefully received.
Dave
One Star

Re: How do I share a database connection with sub jobs?

Hi Shong
Based on what you said "if the connection name does not exist, it will throws exception when you execute the job" I changed the name in one of the subjobs from "Master" to "Masteruuu" and the job ran without errors?
I don't know if this helps.
Dave
Seven Stars

Re: How do I share a database connection with sub jobs?

For me, sharing the connection works fine for a non-dynamic tRunJob (see screenprints) and fails if the Shared DB Connection Name is not the same in the parent and child jobs.
However, you obviously can't share a connection with a child job run as an independent process and, unfortunately, that is how Talend handles dynamic tRunJobs (see note 77780 in bug 19312 and bug 21108). I get a very similar error when I try this; I'm not sure why you're not.
One Star

Re: How do I share a database connection with sub jobs?

Thanks alevy.
That makes sense. I had not used the shared connection before so I assumed it was the way I had used it and not the dynamic aspects of the tRunJob.
Cheers Dave

Re: How do I share a database connection with sub jobs?

Thanks Guys,
DB connection with child sub jobs is not possible when child sub job is marked to run as independent sub job.
For people looking for How to share Db connection, I have written a small article with example:
http://vikramtakkar.blogspot.in/2013/03/sharing-database-connection-with-child.html
Vikram Takkar
http://vikramtakkar.blogspot.in