Four Stars

Limitation on rows in TOS

Is there a limitation on the no of rows in trial version of TOS ?   my job is not loading the data after 20000rows.. looks like it is not updating more than that.

1 ACCEPTED SOLUTION

Accepted Solutions
Fifteen Stars

Re: Limitation on rows in TOS

Take a look at this: https://stackoverflow.com/questions/44188234/azure-exceeded-the-memory-limit-of-20-mb-per-session-fo...

 

It shows that this is likely an Azure configuration limitation. I do not believe this is a Talend issue.

Rilhia Solutions
23 REPLIES
Fifteen Stars

Re: Limitation on rows in TOS

No there is a no limitation on the number of rows that Talend will process. I have processed 100s of millions in the Open Source Edition. What are you trying to do? This could be a memory limitation or an issue caused by data.

Rilhia Solutions
Four Stars

Re: Limitation on rows in TOS

when you say memory limitation , could you explain more on this.?
 i am trying to load data from oracle to ms-sql..

Fifteen Stars

Re: Limitation on rows in TOS

The limit of what your system can handle and your JVM settings have been set to. Look up "Talend JVM settings" on line. Talend do not impose any limits on developers.

Rilhia Solutions
Thirteen Stars TRF
Thirteen Stars

Re: Limitation on rows in TOS

Hi,

Do you have any error message?

Are you running the job from the Studio? 

If so, how many rows are counted after tOracleInputComponent (should appear in green on the link after the component)?


TRF
Four Stars

Re: Limitation on rows in TOS

can you comment on this job ? this has only 1642 rows , but it still does not complete... Attached screenshot

Thirteen Stars TRF
Thirteen Stars

Re: Limitation on rows in TOS

 

You need to commit your transaction at the end using a tMSSqlCommit component (or use an existing connection which configured with autocommit)


TRF
Four Stars

Re: Limitation on rows in TOS

please help me with this error..  in the component list i do not see any option.

Fifteen Stars

Re: Limitation on rows in TOS

Can you show your Output component's Advanced Settings?

Rilhia Solutions
Four Stars

Re: Limitation on rows in TOS

here it is. 

Fifteen Stars

Re: Limitation on rows in TOS

As a test, can you reduce the "Commit every" to 1000 and the "Use Batch Size" to 1000. Then try running it.

Rilhia Solutions
Thirteen Stars TRF
Thirteen Stars

Re: Limitation on rows in TOS

OK, I forgot the option "Commit every"...

Can you share the basic settings too?


TRF
Four Stars

Re: Limitation on rows in TOS

After reducing the "Commit every" to 1000 and the "Use Batch Size" to 1000, the data is loaded for one job which has 1642 rows,

 

 the second job which has 52000 rows  is still not updated.. I have hard coded it to 1000 as well.. please suggest

Thirteen Stars TRF
Thirteen Stars

Re: Limitation on rows in TOS

Try to add an explicit tMSSqlCommit at the end.


TRF
Four Stars

Re: Limitation on rows in TOS

what do i fill here ? attached screenshot

Fifteen Stars

Re: Limitation on rows in TOS

This is likely caused by your Input component. Can you show us your job and component config? Do you get an error? Are ANY rows being loaded to your database out of the 52000?

Rilhia Solutions
Thirteen Stars TRF
Thirteen Stars

Re: Limitation on rows in TOS

You need a MS SQL Connection (tMSSQLConnection component to be added at the beginning then to be reused for tMSSQLOutput and tMSSQLCommit components)


TRF
Four Stars

Re: Limitation on rows in TOS

No, rows are not loaded to the database. attached screenshot

Thirteen Stars TRF
Thirteen Stars

Re: Limitation on rows in TOS

Are you able to use a tLogRow component after the tMSSQLOutput (connected with the Reject flow)?


TRF
Fifteen Stars

Re: Limitation on rows in TOS

Ah, I see you are using the Oracle componenst :-).In your Advanced settings of your Input component, tick use cursor.

Rilhia Solutions
Fifteen Stars

Re: Limitation on rows in TOS

Did this work?

Rilhia Solutions
Four Stars

Re: Limitation on rows in TOS

It copied 23000 rows and the job failed..   I executed the job thru control M .. below is the error log

 


C:

cd C:\Talend\6.5.1\studio\Solar_0.1\Solar\

java -Xms256M -Xmx1024M -cp .;../lib/routines.jar;../lib/dom4j-1.6.1.jar;../lib/log4j-1.2.16.jar;../lib/mssql-jdbc.jar;../lib/ojdbc\
6.jar;../lib/talend-oracle-timestamptz.jar;../lib/talend_file_enhanced_20070724.jar;solar_0_1.jar; local_project.solar_0_1.Solar --\
context=Default
[FATAL]: local_project.solar_0_1.Solar - tMSSqlOutput_1 112007;Exceeded the memory limit of 20 MB per session for prepared statemen\
ts. Reduce the number or size of the prepared statements.
java.sql.BatchUpdateException: 112007;Exceeded the memory limit of 20 MB per session for prepared statements. Reduce the number or \
size of the prepared statements.
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1867)
at local_project.solar_0_1.Solar$1LimitBytesHelper_tMSSqlOutput_1.limitBytePart1(Solar.java:1537)
at local_project.solar_0_1.Solar.tOracleInput_1Process(Solar.java:1579)
at local_project.solar_0_1.Solar.runJobInTOS(Solar.java:2079)
at local_project.solar_0_1.Solar.main(Solar.java:1910)
Exception in component tMSSqlOutput_1 (Solar)
java.sql.BatchUpdateException: 112007;Exceeded the memory limit of 20 MB per session for prepared statements. Reduce the number or \
size of the prepared statements.
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1867)
at local_project.solar_0_1.Solar$1LimitBytesHelper_tMSSqlOutput_1.limitBytePart1(Solar.java:1537)
at local_project.solar_0_1.Solar.tOracleInput_1Process(Solar.java:1579)
at local_project.solar_0_1.Solar.runJobInTOS(Solar.java:2079)
at local_project.solar_0_1.Solar.main(Solar.java:1910)

Job Object statistics
====================
Total User CPU Time: 13.563 second

Total Kernel CPU Time: 1.531 second

This Period Total User CPU Time: 13.563 second

This Period Total Kernel CPU Time: 1.531 second

Total Page Fault Count: 85689

Total Processes: 2

Active Processes: 0

Total Terminated Processes: 0

Total peak job memory used: 379.556 mega bytes

 

Fifteen Stars

Re: Limitation on rows in TOS

You're using Azure aren't you. This looks like an Azure limitation. Try changing the batch size or doing this in chunks.

Rilhia Solutions
Fifteen Stars

Re: Limitation on rows in TOS

Take a look at this: https://stackoverflow.com/questions/44188234/azure-exceeded-the-memory-limit-of-20-mb-per-session-fo...

 

It shows that this is likely an Azure configuration limitation. I do not believe this is a Talend issue.

Rilhia Solutions