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

i can understand not posting the java code - lots of rules company's
have... but the iACS Explain and index advisor should have the executing
sql (which would be most helpful to see). A solution may be found without
waiting for the rewrite.
If you have any view of the timing of stages of this to see where the time
spent (request rcvd, temp indexes built, actual selection, any packaging or
altering result set, transmit results to Win, etc.

Jim Franz

On Tue, Sep 2, 2025 at 12:31 PM Daniel Gross <daniel@xxxxxxxx> wrote:

I would go with no ORDER BY should be faster - because ORDER BY is applied
to the result set (think only in ORDER BY you can use column number and
"AS" names) - if the result set has no ordering, it can be sent to the
client as-is.

JDBC connection parameters can make a difference.

FOR FETCH ONLY might help - and if you don't want to go the WITH NC route,
try USE CURRENTLY COMMITTED instead - this suppresses record lock times, as
it simply take the image of the row before the pending transaction.

Of course - this process should be fixed - maybe by a running ID or a
timestamp that is stored somewhere, and where the SELECT is fetching only
new or updated records.

Even better would be, not to transfer at all, but have the database
running on IBM i and access the data directly.

HTH
Daniel

Am 02.09.2025 um 16:44 schrieb Charles Wilt <charles.wilt@xxxxxxxxx>:

Would be helpful to see the Java code being used.
Possible that the way it's written has a lot to do with the slowness.

Not including an ORDER BY is always going to be faster...
Also make sure that FOR FETCH ONLY WITH NC is specified.

Some JDBC properties to consider

https://javadoc.io/static/net.sf.jt400/jt400/21.0.0/com/ibm/as400/access/doc-files/JDBCProperties.html

"block criteria"
"block size"
"data compression"
"receive buffer size"
"tcp no delay"

An alternative to using block size, is to use setFetchSize

https://javadoc.io/static/net.sf.jt400/jt400/21.0.0/com/ibm/as400/access/AS400JDBCStatement.html#setFetchSize-int-

Not sure off the top of my head if that would give you a larger set of
records than a 512 block size.
Note that to use it, block size must be 0.

HTH,
Charles

---------- Forwarded message ---------
From: <smith5646midrange@xxxxxxxxx>
Date: Tue, Sep 2, 2025 at 7:52 AM
Subject: Sever pulling a bazillion rows from IBMi via JDBC (I think)
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx



We have a server that is pulling a bazillion rows via JDBC (I think)

daily.

Unfortunately, we are stuck with this process as is for a while until we

can

get a project on the plan to pull only net changes. It currently runs

for

2-3 hours each day.



What is the fastest way to pull this data? Will it be faster to omit the
order by clause and just let SQL hand back data in whatever order that it
wants or would it be quicker to add an order by clause. It seems like
having an order by clause would slow it down because the file is not in

any

particular sequence but I also didn't know if an order by RRN would help.



Any thoughts (other than rewriting the process which is in the plans but
probably not for this year)?

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
--
This is the Java Programming on and around the IBM i (JAVA400-L) mailing

list

To post a message email: JAVA400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/java400-l.

--
This is the Java Programming on and around the IBM i (JAVA400-L) mailing
list
To post a message email: JAVA400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/java400-l.