Re: DB2 to MySQL

On 8/27/2024 1:11 PM, DEnglander--- via RPG400-L wrote:

The existing Java program runs a LOAD DATA LOCAL INFILE, which appears to
be running on the remote server and getting one row at a time from our IBM
i server.

Can you post the actual LOAD DATA statement?

'The LOAD DATA statement reads rows from a text file into a table at a very high speed.'

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

> This particular file has approximately 3.4 Million rows that it

slogs through each time it runs [1x a day]. I am hoping that running the
LOAD DATA INFILE on the remote server, with the CSV file on the remote
server will save run time and prevent the timeouts we are getting more
frequently now as the file gets larger.

'When LOCAL is used, the client program reads the file and sends its contents to the server. The server creates a copy of the file in the directory where it stores temporary files.' It was always my understanding that the main different between LOCAL and non-LOCAL is the security of the server. If I have access to the server, I can put the import file right there and issue LOAD DATA INFILE myfile.txt. If I don't have that kind of access, I put the file on my local PC (the client) and LOAD DATA LOCAL infile myfile.txt. Again, for security reasons, the server needs to be configured to allow clients to do that. I myself do not see any performance differences between LOCAL and non-LOCAL for a 1.5 million row import, but this is between a Linux machine and a Windows machine. The IBM IFS can have some performance issues serving up stream files...

If you're looking to optimise the performance of LOAD DATA, see 10.2.5.1 Optimizing INSERT Statements https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html The bottom of the page has tips for both InnoDB and MyISAM tables.