Sharing a database connection between a parent Job and a child Job

Overview

This article demonstrates how to use the Use and register a shared DB Connection feature to share DB connection between a parent Job and a child Job. With this feature, you can benefit from the following advantages:

  • Using the same single DB connection between a parent Job and a child Job, instead of creating multiple DB connections
  • Implementing transaction management between a parent Job and a child Job
  • Improving project development efficiency

 

Environment

This procedure was written with:

  • Talend Open Studio for Data Integration 5.4.1-r111943
  • JDK version: Sun JDK build 1.6.0_26-b03
  • Operating system: Windows XP SP3
  • Mysql 5.0.67-community-nt

Talend verified this procedure to be compatible with Data Integration releases starting from v4.2.3.

 

Procedure

Create example Jobs

In this tutorial, you create two Jobs to learn how to use the 'Use or register a shared DB connection' feature. Call them ParentJob and ChildJob. Using a Mysql database as an example, the detailed Job settings are described below.

 

Note: This example Job is also available in the attached zip file.

 

ParentJob

 

parent_job.png

 

For tMysqlConnection_1:

  • Create a Mysql DB connection, select the Use or register a shared DB connection box to register a shared DB connection, and type a connection name in the Shared DB connection Name field, for example "Conn".

    tMysqlConnection_1.png

 

For tFixedFlowInput_1:

  • Generate a set of test data.

    tFixedFlowInput_1.png

 

For tMysqlOutput_1:

  • Insert the data into a table called "person". Select the Use an existing connection box to use an existing connection created on tMysqlConnection_1. Select Drop table if exists and create from the Action on table list. This means the Job will create the table if it doesn't exist, or drop the table if it already exists and create it again.

    tmysqlOutput_1.png

 

For tRunJob_1:

  • Call the ChildJob Job.

    tRunJob_1.png

 

For tMysqlCommit:

  • If the Job works properly, commit the changes and close the DB connection.

 

For tMysqlRollback:

  • If the Job fails, rollback the changes and close the DB connection.

 

ChildJob

 

childJob.png

 

For tMysqlConnection_1:

  • Select the Use or register a shared DB connection box to use a shared DB connection and type in the connection name "Conn", which is defined in the ParentJob Job, in the Shared DB connection Name field.

     

    Note: You don't need to set the DB connection parameters such as Host on this component. Allow them to be empty, as the component will use a shared DB connection.

     

    ChildJob_tMysqlConnection_1.png

 

For tMysqlInput_1:

  • Read data from the "person" table, which is created and filled in by the ParentJob job.

 

For tLogRow_1:

  • Print the data on the console.

 

Executing the example Job

Executing the ParentJob Job inserts data into the "person" table from the parent job that are then read from the child job, and are displayed on the console.

 

Starting job ParentJob at 11:59 11/01/2014.
[statistics] connecting to socket on port 3699
[statistics] connected
.--+-------.
|tLogRow_1 |
|=-+------=|
|id|name   |
|=-+------=|
|1 |Shong  |
|2 |Elise  |
|3 |Pedro  |
|4 |Patrick|
|5 |Dave   |
'--+-------'
[statistics] disconnected
Job ParentJob ended at 11:59 11/01/2014. [exit code=0]

 

Version history
Revision #:
7 of 7
Last update:
‎06-06-2017 05:10 PM
Updated by:
 
Labels (1)
Comments
harinadh4

Dear Sparks,

 

i implemented shared eared connection with subjob. But connection in the main job is getting closed before subjob complete. I am getting closed connection error.

 

could you please suggest me to resolve the problem.

 

Regards,

Harinadh

agershenson

Hi Harinadh,

 

Sorry for the delay in replying. For assistance if this is still a problem, please log on to https://www.talend.com/services/technical-support/. If you would like help from the community, please post your question to the correct forum.

 

Hope that helps,

Alyce