Talend Job is taking more time to complete execution

[Rajkumar_J]

Hi,

I am working on TOS. In this i have created a certain number of jobs and done schedule through 'windows task scheduler'.

My problem is, sometimes the job is getting completed within 2 mins and sometimes the same job is taking more time (approx 2 hrs) to complete the execution.

What is the reason for this and how could it be rectified?

[Anonymous]

make sure the tables you use are not being locked by other objects. if other user or other job has locked your table. writing to that table will be kept on hold till the lock is released. 

Also see how much throughput you are getting for the mapping. 

Use bullk load component to load to target

[Rajkumar_J]

--Do the tables got locked while fetching the data through talend?

--Bulk load is better option, but i am facing some error. 'Access denied'

"Exception in component tDBOutputBulkExec_1_tMBE (New_component)
java.sql.SQLException: Cannot bulk load because the file "C:/Users/*****/Desktop/talend/TOS_DI-20180411_1414-V7.0.1/workspace/mssql_data.txt" could not be opened. Operating system error code 5(Access is denied.).
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:572)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:809)
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1282)
at talend_local.new_component_0_1.New_component.tDBInput_3Process(New_component.java:1180)
at talend_local.new_component_0_1.New_component.runJobInTOS(New_component.java:1522)
at talend_local.new_component_0_1.New_component.main(New_component.java:1360)"

and also I have one more doubt.

--Do we enable bulk load permission in sql database?

[Anonymous]

Hi,

    Did you check the DB statistics from your DBA to verify that DB is having enough resources?

    I am having doubt about the performance of DB, number of simultaneous connections allowed, usage of indexes etc. (to say a few).

    Also please check the memory and CPU utilization of the server where Talend jobs are running (you have already checked the network part).

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 🙂

[Rajkumar_J]

--In db statistics, what i need to check?
--There is no limit for simultaneous connections. Even-though I am executing the table alone which is taking too much time, at that time also same problem occurs.
--CPU and memory usage is around 70%

[Anonymous]

Hi,

   If its Oracle, I would have asked DBAs to provide AWR and ADDM reports.

https://oracle-base.com/articles/10g/automatic-database-diagnostic-monitor-10g

   I am not sure the equivalent of ADDM report in SQL Server but your DBA can help you in it. 

   Are you having enough CPU and memory in the machine where you are running Talend jobs? Please refer the recommended Resource size for enterprise products as a bench marks.

https://talendpnp.github.io/architecture-center/reference-architecture/summer-2018-7.0/products/comm...

   You need to have comparable infrastructure in your server like a job server.

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 🙂

[Rajkumar_J]

Do we need to enable any options to make bulk load in sql through talend?

[manodwhb]

@uganesh ,to load data using Bulk method ,you should use combination of tOracleOutputBulk and tOracleOutputBulk

[Rajkumar_J]

@manodwhb, i am using sql database not oracle.....so i have to use tmssqloutputbulk component only right..

[manodwhb]

@uganesh ,if you are using MSSql server then yes.

[Rajkumar_J]

I have used tmssqloutputbulk component, but it is showing error at the end....

Error : You do not have permission to use bulk load statement