JDBC To DB2 for i is Slow when host server is Windows 2019 or 2022

This is a strange issue. I am trying to get a server upgraded from Windows Server 2016 Data Center to anything newer. We have tried 2019, and 2022. This server is an ETL server for running Talend Open Studio, it is a connection between MS Sql Server and IBM DB2 for i. The problem is that the connection to DB2 is 100 times slower when the host is Windows 2019 or 2022, and this is just not workable for us.

We noticed this when we got new faster hardware for the server. We installed Windows Server 2022 Datacenter on it and migrated a Talend job over to test with, and the new server, while quite fast when running app that stayed on the server, nearly ground to a halt when connecting to DB2. The architecture looks like this:

A single IBM i instance running IBM i v7.2. A number of MS SQL Server machines running SQL Server 2014, 2017, and 2019. A VM on older hardware running Windows Server 2016 and Talend Open Studio (TOS) v7.3. And, a VM on new hardware running Windows Server 2022 and Talend Open Studio v7.3. Source data is on SQL Server, this data is processed by TOS and written to DB2 using JDBC.

In our initial testing we were getting throughput of around 1000-1200 rows per second on the old 2016 Talend server. The new hardware, inexplicably, only manager 17-20 rows per second. On IBM i, the same pool of jobs are handling all ODBC/JDBC requests. The initial reads from the SQL Servers were much faster on the newer Talend Server than they were on the older Talend Server, and both servers were using the same JDBC drivers for DB2, that is jtOpen 9.8. The only difference was Windows 2016 (fast) vs. Windows 2022 (slow) and the JVM loaded on each, One was using Java 8, while the other was using Java 11.

Our infrastructure guys couldn't find any material configuration differences between the Windows servers, and on the network, there is only a single hop from the Talend Servers to the IBM i (they are all on the same subnet).

Infrastructure spun up two identical VMs to test Windows. On one they put an empty Windows 2016 server, and on the other they put an empty Windows 2022 server. I installed Java 11, Talend 7.3, and the same test Talend model on both servers. Both Talend servers are connected to the same SQL Server for input, and the same IBM i for output. The Windows 2016 server managed around 2000 rows per second on the new hardware, the Windows 2022 server managed only 19 rows per second. We spun up a new VM same as the other two, and put Windows 2019 on it. I installed the same Java, and Talend software on it, and it only managed 20 rows per second. I upgraded Java to Java 17 on the Windows 2022 server and installed a copy of Talend v8.0. That only managed 17 rows per second.

So where do I look next? We don't really want to be stuck with an old version of Windows, but some of our data migration jobs take 13 to 18 hours on the old hardware. That would take months on the newer Windows servers.

What could be causing the slowdown? The only difference is Windows 2016 which is, relatively, fast, and windows 2019/2022 which is 100x slower.

Note: There are no errors, it just runs slowly.

Really sounds like to me that there something different in the JDBC connection properties or in configuration of the Talend process. Not sure how the configs are moved between original and new.

Assuming straight INSERTs into Db2 for IBM i, the two biggest throughput limiters I've seen are

• single row inserts instead of batching multiple rows
• writing to a journaled table on the i without using commitment control.

You might want to take a look at the tracing properties

"server trace"
Specifies the level of tracing of the JDBC server job. When tracing is enabled, tracing starts when the client connects to the server and ends when the connection is disconnected. You must start tracing before connecting to the server, because the client enables server tracing only at connect time.

• "0" (trace is not active)
• "2" (start the database monitor on the JDBC server job)
• "4" (start debug on the JDBC server job) < - "8" (save the job log when the JDBC server job ends)
• "16" (start job trace on the JDBC server job)
• "32" (save SQL information)
• "64" (supports the activation of database host server tracing) Multiple types of trace can be started by adding these values together. For example, "6" starts the database monitor and starts debug.

"toolbox trace"
Specifies what category of an IBM Toolbox for Java trace to log. Trace messages are useful for debugging programs that call JDBC. However, there is a performance penalty associated with logging trace messages, so this property is only set for debugging. Trace messages are logged to System.out.

• ""
• "none"
• "datastream" (log data flow between the local host and the remote system)
• "diagnostic" (log object state information)
• "error" (log errors that cause an exception)
• "information" (used to track the flow of control through the code)
• "warning" (log errors that are recoverable)
• "conversion" (log character set conversions between Unicode and native code pages)
• "proxy" (log data flow between the client and the proxy server)
• "pcml" (used to determine how PCML interprets the data that is sent to and from the server)
• "jdbc" (log jdbc information)
• "all" (log all categories)
• "thread" (log thread information)

Lastly, JTOpen 9.8 is very old released...in April 2019.

I'd probably try a newer version and see if that made a difference.