Sharing database connection between parent job and child job

 Overview

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

  • Using the same single DB connection between parent Job and child Job, instead of creating multiple DB connections
  • Implementing transaction management between parent Job and 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, we create two Jobs to show how to use the 'Use or register a shared DB connection' feature, let's call them ParentJob and ChildJob, take Mysql database as an example, the detailed Job settings are described as below:

ParentJob

 on 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 in a connection name in the Shared DB connection Name field, for example "Conn".

 on tFixedFlowInput_1: generate a set of test data.

on 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 item from the Action on table list, this means the Job will create the table if it doesn't exist, drop the table if it already exists and create it again. 

 on tRunJob_1: call the 'ChildJob' Job.

 on tMysqlCommit: commit the changes if the Job works fine and close the DB connection.

 on tMysqlRollback: rollback the changes if the Job fails and close the DB connection.

ChildJob

 on 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 ParentJob Job, in the Shared DB connection Name field.

Icon

We don't need to set the DB connection parameters like Host on this component, allow them to be empty, as the component will use a shared DB connection.

 on tMysqlInput_1: read data from the "person" table which is created and filled in ParentJob job.

 on tLogRow_1: print the data on the console.

Icon

This example Job are also available in Related Files section.

 Executing example Job

Now, open the ParentJob Job and execute the Job, we get the following result on the console; These data are inserted into the 'person' table from the parent job, are 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]

 Related Files

  File Modified
  ZIP Archive sharingdbconndemo.zip Nov 19, 2012 byShicong Hong
Version History
Revision #:
1 of 2
Last update:
‎04-17-2017 11:03 PM
Updated by:
 
Labels (1)
Contributors
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