Six Stars

shared db connection

Hello,
I am working on a talend job (parent job) which orchestrate four subjobs 
each subjob (subjob1, subjob2, subjob3, subjob4) need a db connections (differente db) to insert/update data
the parent job  need this db connections to do some insert/ update 
As these db connections are used both in the parent job and subjobs (child jobs), I woud like to declare them one 
in the parent job  using db connection component (like a pool connection) an share them with the child jobs 
(every child job use a db connection component too but using the same connection name used in the parent job)
but every child job has to commit his own work (independently from the other child jobs and the parent job too)
so i check commit automatic in each child job db connection component. 
the question is : shared db connection doesn't mean shared transaction ? 
because in my case as explained the goal is not to share the same transaction that is opened in the  parent job
and used throw the child jobs (no commit ) and finally one commit is done in parent job.
My goal is to have one db connection created in the parent job (like a connection pool) and each child job
use this connection to commit his own data () and give the connection back to the "pool" and finally the parent 
job can close all the connections in postjob. 
how can I do it if the shared db connection is not meant for this use  ?
thanks a lot 
I am using teradata connection component 
 
 
4 REPLIES
Moderator

Re: shared db connection

Hi,
Could you please post your job design screenshots into forum?
Did you create a shared connection in parent job and you want to reuse it in child job? If so, you can use joblet which will let you share the database connection.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: shared db connection

Hi xdshi,
I am using TOS (so no joblets) 
Yes I created a shared connection in the parent job (that use this connection) and reuse it in the child jobs too 
(but each child job has to do his own commit separately from the other child jobs and return the connection back to
 the "kind of pool of connection" shared by the parent job and so on for the other child jobs and finally the parent job 
can close the connections) 
In my case :  I used a tTeradataConnection in the parent job (I did select the checkbox
 “Use or register a shared DB connection” and provided a unique name to this shared connection)

and in each child job i used tTeradataConnection too  but I didn't provide the connection parameters  
because I want to use the same connection created in parent Job so I provided the same name  
to this shared connection in the “Shared DB Connection Name” text box of each child job
and giving that each child job has to commit his data I did select the checkbox "commit automatically" advanced
setting in each child job tTeradataConnection component.
Is it the right way to do ?
thanks a lot.
One Star

Re: shared db connection

I guess the moment you will choose Commit in individual Sub job it will commit all the uncommitted transactions for that shared connection
Six Stars

Re: shared db connection

that's ok, in my case it is not a problem, giving that all the uncommitted transactions are always the 
child job (doing the commit) ones. In the same way, the second child job, when he ask for the shared connection, will commit 
his own transactions and so on for the third and the fourth child jobs.
It's sort of connection pool with one shared connection, that's why it is incompatible with multi thread 
tRunJob (child jobs)  execution.
It will be great if the tTeradataConnection component could be configurable  to set the connection pool to more than
one connection and then some child jobs can be run in parallel 
I found one component (talend exchange) connectionPool that's not compatible with teradata (just mysql and Oracle)
thanks a lot  sankalp.verma for the answer