Lost persistent connection to database (MSSQL)

Six Stars

Lost persistent connection to database (MSSQL)

I have a Talend Job with typical path tRESTRequest -> tMsSQLRow->tRESTResponse
The tMsSQLRow use a persistent connection by tMsSQLConnection
How restore lost connection for Talend Open Studio for this typical job scenario?

possible сommunity solutions:
1) external tLoop "supervisor" job for subjob with tRESTRequest -> tMsSQLRow->tRESTResponse 
But how to cancel this subjob? tDie and other community solutions not working with tRESTRequest -> ...->tRESTResponse services. Not working solution.

2)  a tJava reconnect code as https://community.talend.com/t5/Design-and-Development/Is-there-a-way-to-reconnect-to-a-database/td-...
But this code make reconnect for each request after first reconnect ( or generate exceptions). If this code located between tRESTRequest -> ...->tRESTResponse in same job with it.
The print debugging says, what internal data of Talend code contain a two (or more) connection: global losted (for tMsSQLConnection) and normal for curent request context. But normal connection is lost immediately after request. Half working solution.

3) Possible using runtime connection pool https://community.talend.com/t5/Design-and-Development/Connection-pooling/m-p/114417?collapse_discus...
Ok. But this solution for deployment in Karaf etc.

I needed solution in how to implement repair losted db connection in Talend Open Studio for REST-like services.
Thank you.

Employee

Re: Lost persistent connection to database (MSSQL)

@spot62 

 

Could you please share your current job flow? Are your trying to have a single DB connection for multiple calls of same job? Or are you trying to retain the DB connection throughout single execution of a job flow?

 

If the case is second, you can very well use the Connection component to share the DB connection throughout various other DB components. 

 

But if you are trying to use a single connection for multiple calls of same job, it is not a recommended way. You will have to call DB every time you are doing a job call.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Six Stars

Re: Lost persistent connection to database (MSSQL)

REST_mssqlsp_subjob_0.3.pngcurrent job

This is my experimental job. tMSSqlConnection is a persistent connection, its must restored after down/up link to my MSSQL server. But not do it. How I can use Talend OS to restore tMSSqlConnection connection after down and up network link to my db host (this is simulate network problems), for example?

Employee

Re: Lost persistent connection to database (MSSQL)

Hi,

 

   If my understanding about your query is right, you are trying to automatically reconnect the DB connection even if there is a DB outage in between due to any network issue.

 

   But if the DB connection is cut off while doing the data processing, you will not be able to reconnect automatically and process the job. In this case, you should provide a Fault response back to the request with necessary error message. 

 

    Once the connection is established between Talend and DB, it is the duty of network and DB to give sufficient support to make sure that connection is retained unless it is closed by Talend process. So if there is not enough network or DB bandwidth, Talend job will (and should) error out and error response needs to be transmitted back.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Six Stars

Re: Lost persistent connection to database (MSSQL)

Yes. Where the connection is lost, the service will return an error.
But at connection between Talend host and DB host is established, Talend is not reestablish connection to DB, and the service will continue to return an error.

Those this job will work until the first network failure.

> connection is retained unless it is closed by Talend process...

No. tMSSqLConnection component is not saved current connection and will not restore it (although it should) after any infrastructure problems.

This is a very serious problem, because for reliability the service must establish a connection with the database every time. This is reduces service performance.


My primary question - how restore tMSSqLConnection connection after DB link established? Or typical Job template for this.

Employee

Re: Lost persistent connection to database (MSSQL)

Hi,

 

     The Talend DB connection component will not poll for the re-connectivity to DB in case of failure. If needed, the polling has to be created based on the error value and then using a parent job, you will have to send a new connection request to DB.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Six Stars

Re: Lost persistent connection to database (MSSQL)

Ок. How me do it?

Employee

Re: Lost persistent connection to database (MSSQL)

Hi,

 

     In case your job is failing, you will have to raise a tDie with a specific error code. You need to create a parent job where it will poll the return code and if it is database specific code, you will have to make the call to child job again.

 

     But my preference will be to strengthen the network and DB layer for avoiding these type of issues rather than creating overhead on application code.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

Six Stars

Re: Lost persistent connection to database (MSSQL)

tDie component is not terminate job, if this job contain tRestRequest/tRestResponse components. See start topic message 1)
Parent job will be in infinite waiting

> But my preference will be to strengthen the network and DB layer for avoiding these type of issues rather than creating overhead on application code.

No things work absolutely reliably.

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

An API-First Approach to Modernizing Applications

Learn how to use an API-First Approach to Modernize your Applications

Watch Now

Talend API Designer – Technical Overview

Take a look at this technical overview video of Talend API Designer

Watch Now

Getting Started with APIs

Find out how to get started with APIs

Read