topic Re: Is it necessary to use tDBCommit and tDBRollback after tDBOutput? in Talend Studio

Is it necessary to use tDBCommit and tDBRollback after tDBOutput?

Anonymous — Sat, 16 Nov 2024 02:39:42 GMT

I'm wondering here if I should add tDBCommit and tDBRollback after tDBOutput?

For example, in tDBRollback case,

if the job fails, it will not be executed and there will be error message, regardless of whether there is a tDBRollback component or not.

So I was wondering does it help to use tDBRollback?

In tDBCommit case, what's the different between setting 'auto commit' in DBConnection and use tDBCommit?

Re: Is it necessary to use tDBCommit and tDBRollback after tDBOutput?

Anonymous — Thu, 27 Apr 2023 06:18:01 GMT

Hello,

Rollback components are used to make sure that entire data is rolled back in case the input data volume is high and your DB load failed in between.

For example, you have 1 million records and data will be written to table at regular intervals since we will not be able to pump 1 million in a single batch. When the data load reached 50,000 records, you got some error data and job failed. Now, the initial 50k records are still in DB but ideally you need to roll these data back. tDBRollback helps in this context where it will rollback from all the associated tables (this table and any previous tables) which got loaded under same tDBConnection unless you have given an explicit commit.

Could you please refer the link below for a quick sample scenario too?

https://help.talend.com/reader/jomWd_GKqAmTZviwG_oxHQ/7THiSA~u~mPb7PskcQONMQ

Best regards

Sabrina

Re: Is it necessary to use tDBCommit and tDBRollback after tDBOutput?

jlolling — Fri, 28 Apr 2023 07:55:57 GMT

It depends. If the tDBOutput components use an external connection than if this connection has no auto-commit option activated you have to commit or rollback after the whole flow has been finished.

You can trigger the tDBCommit with OnSubjobOk and the tDBRollback with OnSubjobError.

If your tDBOutput creates its own connection than both are done automatically.

Re: Is it necessary to use tDBCommit and tDBRollback after tDBOutput?

jlolling — Fri, 28 Apr 2023 08:04:27 GMT

The difference between using the auto-commit option in tDBConnection and do not use this option, instead of use tDBCommit/tDBRollback is how you cut your transactions.

Everything in a transaction is either completed successfully or no changes are in the database appears at all.