RE: Sever pulling a bazillion rows from IBMi via JDBC (I think)

One should sort the data into the correct order ahead of time. This saves the remote the effort of maintaining the index database.



The most extreme example I ever saw was a while ago, when a certain hamburger company in the Chicago area was exporting data from an iSeries machine to a pSeries machine, and that client engaged an IBM consultant for the project. He was able to get his code working to his satisfaction after a few weeks, fired up his routine to get a half a billion records, and went home to Rochester for the weekend. When he returned on Monday, his extract was still running, but was only half done. He couldn't figure out why.



I asked him if he had sorted the data to be extracted before he began his routine. I had to explain that the remote machine was working overtime to keep the index database in the proper order. He slapped his forehead, and I helped him write a sort utility on the iSeries box. When that completed, he cranked up his routine again. The data transfer completed in time for him to buy my lunch. 😊



-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Richard Schoen
Sent: Tuesday, September 2, 2025 12:52 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: RE: Sever pulling a bazillion rows from IBMi via JDBC (I think)



One nice option might be exporting to CSV and bulk loading to the remote.



Lack of order by should make things faster I would think.



But a large record inserted and committed individually will also be slow.



Would still need to know more info about the code being run to know how efficient the inserts are.



Maybe put sometimings in the query and the remote inserts.



Is it a local or remote server ? That will also have a great performance impact.



So many questions.........



Regards,

Richard Schoen

Web: <http://www.richardschoen.net> http://www.richardschoen.net

Email: <mailto:richard@xxxxxxxxxxxxxxxxx> richard@xxxxxxxxxxxxxxxxx

----------------------------------------------------------------------



message: 1

date: Tue, 2 Sep 2025 12:06:54 -0400

from: <mailto:smith5646midrange@xxxxxxxxx> smith5646midrange@xxxxxxxxx

subject: RE: Sever pulling a bazillion rows from IBMi via JDBC (I

think)



The bottleneck is that it is transferring 22,064,447,488 bytes of data and inserting it into a windows server database (not sure which one).



Their SQL currently does not have an order by clause. I was actually wondering if having "order by RRN" would give better blocking for the SQL read portion.



It looks like they are going to have to suffer through it until they change the process to build a delta version of the file to pull.





--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: <mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx> MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,

visit: <https://lists.midrange.com/mailman/listinfo/midrange-l> https://lists.midrange.com/mailman/listinfo/midrange-l

or email: <mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx> MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx

Before posting, please take a moment to review the archives at <https://archive.midrange.com/midrange-l> https://archive.midrange.com/midrange-l.



Please contact <mailto:support@xxxxxxxxxxxxxxxxxxxx> support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.