Commit every... with existing connection

One Star

Commit every... with existing connection

I am trying to load data into oracle using TOS 2.3.3.
1) I have main connection that goes from my file to tOracleOutput.
2) I use the existing connection.
3) I want to commit every 10000 rows.
Is there a way to implement this kind of load in Talend?
I understand that I can do it if I don't use existing connection (there is a Commit Every... field on the Advanced Settings tab, but it disappears when I switch to "use existing connection"). Why can't I do it with the existing connection? Is there a reason or am I missing something?
Employee

Re: Commit every... with existing connection

Why can't I do it with the existing connection? Is there a reason or am I missing something?

It's a design choice from us the developers. tOracleConnection/tOracleCommit/tOracleRollback where designed so that user can control the transactions.
Workaround:
tFileInputDelimited --row--> tOracleOutput --row--> tFilterRow --row--> tOracleCommit
in the tFilterRow, advanced mode:
sequence('abcd', 1, 1) % 10_000 == 0

Don't forget to add a tOracleCommit after the subjob, so that last lines are commited.
May I ask you to add a feature request "add a commit every in t*Commit"? I think it would be much more user friendly than the tip I just gave you.
One Star

Re: Commit every... with existing connection

Why can't I do it with the existing connection? Is there a reason or am I missing something?

It's a design choice from us the developers. tOracleConnection/tOracleCommit/tOracleRollback where designed so that user can control the transactions.
Workaround:
tFileInputDelimited --row--> tOracleOutput --row--> tFilterRow --row--> tOracleCommit
in the tFilterRow, advanced mode:
sequence('abcd', 1, 1) % 10_000 == 0

Don't forget to add a tOracleCommit after the subjob, so that last lines are commited.
May I ask you to add a feature request "add a commit every in t*Commit"? I think it would be much more user friendly than the tip I just gave you.
Excellent! I will use this trick for now and wait for the new feature.
Submitted 3797
One Star

Re: Commit every... with existing connection

How to perform such trick with java tFilterRow?
One Star

Re: Commit every... with existing connection

Hi,
In tFilterRow, Advanced:
Numeric.sequence("abcd", 1, 1) % 500 == 0
Kind regards,
Yves
One Star

Re: Commit every... with existing connection

Numeric.sequence("abcd", 1, 1) % 500 == 0

It works.
Thank you very much!
One Star

Re: Commit every... with existing connection

what is "abcd"?
One Star

Re: Commit every... with existing connection

The first 4 letters of the alphabet.
Actually it's the sequence reference name.
Five Stars

Re: Commit every... with existing connection

Hi guys,

 

Could you please help me with this??

 

I have 495 records in input when I try to run the job it inserts only 400 records if i keep the expression

Numeric.Sequence("S1",1,1)%100==0 in advanced mode of tFilterRow.

 

SELECT COUNT(1) FROM TEMP ---> 400 

 

May I know why and how to insert the other 95 records?

PFA screenshot for reference

 

Thanks in advance

 

 

Four Stars

Re: Commit every... with existing connection

Hi Kiran,

 

Did you find the solution for 

"I have 495 records in input when I try to run the job it inserts only 400 records if i keep the expression

Numeric.Sequence("S1",1,1)%100==0 in advanced mode of tFilterRow."

Even I am facing the same issue, If you find the solution please share.

 

Thank You

 

 

2019 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

APIs for Dummies

View this on-demand webinar about APIs....

Watch Now