IBM DB2 to Sql Server - Load time issue

Abilaash — Fri, 15 Nov 2024 21:20:27 GMT

Hi,

I have total of 8 Talend jobs developed using open studio and each job is simple 1:1 load, but one particular job load time is very much slow.

The source table (IBM DB2) consists of 183 columns and 3.5 millions of data, the Talend job load the data into Sql server. It take very long time (say 3 hours) to complete the job or sometimes it gives following error:

Read timed out:

com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException:[jcc][t4][2030][11211][4.22.29] A communication error occurred during operations on the connection's underlying socket, socket input stream, or socket output stream. Error location: Reply.fill() - socketInputStream.read (-1). Message: Read timed out. ERRORCODE=-4499, SQLSTATE=08001.

Connection reset:

java.lang.RuntimeException:Child job running failed.

com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: [jcc][t4][2030][11211][4.22.29] A communication error occurred during operations on the connection's underlying socket, socket input stream, or socket output stream. Error location: Reply.fill() - socketInputStream.read (-1). Message: Connection reset. ERRORCODE=-4499, SQLSTATE=08001

In the IBM documents it shows following cause and suggestion:

Connection was closed by the other end. This can be any network device between JDBC driver and server. May include server itself.

Root cause outside of DB2's control. Engage network administrator to collect network traces from both client and server then to find root cause.

Troubleshoot:

I tried to extract source table (DB2) data to flat file, it only took 20 mins. All Talend jobs are deployed in windows machine and run via batch scripts.

I am not sure where the issue is: Source DB, Target DB or Job server.

Any suggestion/help is highly appreciated. Thanks!

Re: IBM DB2 to Sql Server - Load time issue

NNayal1600240775 — Tue, 14 Nov 2023 08:06:14 GMT

can you show the talend screenshot

did you try increasing the xms and xmx memory in talend.

you can also try to increase the batch size

Re: IBM DB2 to Sql Server - Load time issue

Abilaash — Tue, 14 Nov 2023 09:08:57 GMT

Hi Nayal,

I am developing jobs in my local using Talend, but running them in windows server (exporting the jobs using build job option).

I included additional JDBC paramaters loginTimeout=180 seconds;socketTimeout=300 seconds;autoReconnect=true and I am able to avoid read timeouts, but performance wise not good.

Attached old screenshot.

Re: IBM DB2 to Sql Server - Load time issue

Abilaash — Fri, 08 Dec 2023 11:52:10 GMT

Hi @Neha Nayal ,

I am setting commit and batch in the tDBOutput as 1000. Will increasing to higher value will improve my performance?

topic IBM DB2 to Sql Server - Load time issue in Talend Studio

IBM DB2 to Sql Server - Load time issue

Re: IBM DB2 to Sql Server - Load time issue

Re: IBM DB2 to Sql Server - Load time issue

Re: IBM DB2 to Sql Server - Load time issue