tDBCommit every 1000 rows - how to?

[mr_burns]

Hi,

since the talend mysql components have no option "Commit every" (see https://community.talend.com/s/question/0D55b00008C8K8dCAF/question-on-mysql-database-and-commit) to commit data e.g. every 1000 rows I tried to find a workaround but could not succeed.

Here is my flow:

 

As you can see in the component view and also in the log the tJavaFlex_1 together with tRESTClient_1 get data in chunks of 1000.

Every 1000 rows are move forward to tDBOutput_1 to write into the mysql database.

Using tBCommit_1 I wanted to commit all 1000 rows but this is not working, monitoring the mysql database shows that the data does not come in chunks of 1000.

At the end of the job (using tPost_1) I have a final commit and only then all 20000 records are committed as once.

tDBOutput_1 and tDBCommit_1 are set to "Use an existing connection" and point to same tDBConnection_1 component.

If I use the output 'Main' from tDBOutput_1 into tDBCommit_1 then every record is committed - but this is also not what I want.

Connecting tDBCommit_1 to tJavaFlex_1 (OnSubjobOk) is also not working:

 

Why tDBCommit_1 does not commit every 1000 rows? What I am missing please?

[Anonymous]

Hi

Try to iterate each input row and commit for each iteration. eg:

tJavaFlex--row1--tFlowToIterate--iterate-->tFixedFlowInput--main--tRestClient......oncomponentok--tDBCommit

tFixed​FlowInput: generate the current row.

Regards

Shong

[mr_burns]

@Shong, thanks for reply.

I tried your suggestion:

 

and it is working now 😀.

tFixedFlowInput_1 I configured like:

 

Since it is working the problem is solved, but I do not really understand why I need to use tFlowToIterate_1 and tFixedFlowInput_1 to solve my problem.

Could you please explain it a bit more detailed?

Thanks a lot...

[borke02]

I wrote a component as a copy of tPostgresqlCommit years ago with a begin, main and end part where I can configure a number when a commit should be made. The component still works in Talend 8.0.1.

I don't know if I'm allowed to post the code here. Also, the component needs to be changed to MySQL.

Output:

Starting job CommitEvery at 11:20 10/01/2023.

[statistics] connecting to socket on port 3868

[statistics] connected

 commited 1000

 commited 2000

 commited 3000

 commited 4000

 commited 5000

 commited 6000

 final commited 6666

[statistics] disconnected

Job CommitEvery ended at 11:20 10/01/2023. [Exit code = 0]

Regards,

Frank

[mr_burns]

@Frank Borkenhagen​ :

Thanks but in my case the webservice itself needs to be chunked to keep the responding data packages small.

To chunk only the insertion into the mysql database is not what I needed.

But may it is interesting for someone else.

[Anonymous]

tDBCommit commit all the changes cached in​ the DB connection object. In your job design, tDBCommit is only executed one time, if you iterate the input data, tDBCommit is executed for each iteration.