topic Re: Lost persistent connection to database (MSSQL) in Talend Studio

Lost persistent connection to database (MSSQL)

Anonymous — Sat, 16 Nov 2024 05:44:33 GMT

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-p/23063
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_discussion=true&q=lost%20connection&search_type=thread
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.

Re: Lost persistent connection to database (MSSQL)

Anonymous — Mon, 27 May 2019 14:38:33 GMT

@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 🙂

Re: Lost persistent connection to database (MSSQL)

Anonymous — Mon, 27 May 2019 14:59:06 GMT

current 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?

Re: Lost persistent connection to database (MSSQL)

Anonymous — Mon, 27 May 2019 17:41:02 GMT

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 🙂

Re: Lost persistent connection to database (MSSQL)

Anonymous — Mon, 27 May 2019 20:19:59 GMT

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.

Re: Lost persistent connection to database (MSSQL)

Anonymous — Mon, 27 May 2019 20:58:40 GMT

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 🙂

Re: Lost persistent connection to database (MSSQL)

Anonymous — Tue, 28 May 2019 07:13:12 GMT

Ок. How me do it?

Re: Lost persistent connection to database (MSSQL)

Anonymous — Tue, 28 May 2019 15:02:17 GMT

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 🙂

Re: Lost persistent connection to database (MSSQL)

Anonymous — Tue, 28 May 2019 15:23:14 GMT

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.