topic Re: Shared DB Connection in JobLets in Talend Studio

Shared DB Connection in JobLets

Anonymous — Thu, 19 Oct 2017 15:29:47 GMT

Hi Talend Community,

I've got one simple question. Our job definitions are based on joblets in order to make some logic reusable. Some of the joblets are doing extractions from a database. Since the parent job may initiate a database connection on its own all the included jobs should use the connection instead to create a new one --> We want to overcome some performance issues.

That's the theory!

Here is one of these db extraction joblets:

Its very straight! You get a parameter (a "WHERE" condition) use the shared connection in the sql_e_skp_firma and execute it in order to provide the fetched output. Easy!

One of the main unclear things is, where I can place properly tOracleConnection_1 (db_src_avsc_repo)? Right now I am not sure how to connect it within the joblet correctly. In a normal job (the parent Job) I'm using the tPreJob to establish the connection I need as shared connection.

Is there something analogous to the tPreJob in context of joblets?

Thanks and best regards from Germany

Adam L.

Re: Shared DB Connection in JobLets

Anonymous — Thu, 19 Oct 2017 21:24:58 GMT

Include a connection component in the joblet, just as you have done.

In both the connection component in the parent, and in the joblet, check the "register a shared db connection" box

In the parent job, and assign a value , like "CONN1" , "CONN2", etc, for each shared name.

In the parent job, create a context variable context.SharedConnectionName.

In the joblet, use context.SharedConnectionName for the shared name. You do not have to setup a context in the joblet, as it inherits contexts automatically from the job that calls it.

Before the joblet is called, set context.SharedConnectionName to the appropriate value in the parent job.

When the joblet runs, ir will be "synched up" with the parent to use the context.SharedConnectionName shared connection.

Re: Shared DB Connection in JobLets

Anonymous — Mon, 23 Oct 2017 15:57:54 GMT

Hi talend_guru,

I tried out your suggested recipe. Before I start some screenshots:

The tOracleConnection_1 in the joblet uses the "context.shared_connection" value. The tOracleInput1 references the tOracleConnection_1.

The logs bring some interessting entries:

To see the whole post, download it here
OriginalPost.pdf

Re: Shared DB Connection in JobLets

Anonymous — Mon, 23 Oct 2017 16:50:29 GMT

You don't need tOracleConnection again. You can directly get the shared connection in tOracleInput.

Re: Shared DB Connection in JobLets

Anonymous — Tue, 24 Oct 2017 07:08:31 GMT

Hi talend_guru,

don't think that I can do it. The tOracleInput (with Use an existing connection checked) demands a selected Connection component in the Component List. I highlighted the mentioned property in the screenshot.

Any ohter suggestions ?

Re: Shared DB Connection in JobLets

Anonymous — Tue, 24 Oct 2017 15:09:40 GMT

Use OnSubjobok on tOracleConnection component and connect to tFlowtoIterate component. That should take care of it.

Re: Shared DB Connection in JobLets

Anonymous — Fri, 27 Oct 2017 13:10:50 GMT

You are neither able to connect the tOracleConnection to the tFlowToIterate nor any other components in the joblet. Sorry for the lag.

Any other suggestions?

Re: Shared DB Connection in JobLets

Anonymous — Mon, 30 Oct 2017 06:59:12 GMT

For those who else use such an design I've got an solution (thanks to talend support):

Delete in dhe Joblet the "tOracleConnection" (the tOracleInput will start to show erros).
In the tOracleInput jump in the "Dynamic settings" section
Add a new entry: Name: Component List and Value tOracleConnection_1 (Equals the name of your connection created in the parent job).

That's it. Even the tOracleInput is still decorated with an error you the Job does it as it should. I could confirm it with the trace.

Thanks