topic Re: shared db connection in Talend Studio

shared db connection

Anonymous — Wed, 12 Oct 2016 21:36:20 GMT

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

Re: shared db connection

Anonymous — Thu, 13 Oct 2016 03:42:41 GMT

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

Re: shared db connection

Anonymous — Thu, 13 Oct 2016 08:38:46 GMT

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.

Re: shared db connection

Anonymous — Thu, 13 Oct 2016 14:05:52 GMT

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

Re: shared db connection

Anonymous — Thu, 13 Oct 2016 16:10:02 GMT

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