Re: Blocking excessive JDBC connections

There is indeed, “subsystem routing by IP/Range”. Harness the power of
IBMi work management through your own subsystem(s) definitions.


----------------
Joe Wood
----------------

E: wood3875@xxxxxxxxx
M: 801-891-5223
L: http://www.linkedin.com/in/josephgwood




On Tue, Dec 30, 2025 at 11:27 AM Michael Quigley <MichaelQuigley@xxxxxxxxxx>
wrote:

-----Original Message-----
message: 1
date: Sun, 28 Dec 2025 16:59:31 -0500
from: smith5646midrange@xxxxxxxxx
subject: Blocking excessive JDBC connections

My client has a problem where non-IBMi applications (Windows servers) are
creating MANY JDBC connections to pull data.one file is 98G and is

pulled in

full every day. Their app needs to finish faster so they create

additional

threads to bust up the file but they never talk to us about it first.

Our system

currently sits at over 100% CPU for hours at a time while these JDBC
connections are active. Last night it was 4+ hours at 150+%. This is

impacting

other IBMi programs.



There is a whole lot wrong with that previous paragraph and we are

actively

working on the root of the problem but it is a long process getting it

changed.

I don't need help fixing that part of the problem so please don't offer
solutions for it.



The problem that I am trying to solve is how to limit the number of JDBC
connections from a single IP address. They are connecting through a job
named QRWTSVR (or something close to that). When I look at the joblog, I
can see what IP address did the connection. I have gotten permission to

limit

an IP address to 5 threads and if they try to open a sixth, it will

fail. This sixth

connection's failure assumes all of the first five jobs are still

active. If one has

ended / disconnected, the sixth connection should be allowed. My problem
is how can I do it? Is there a way through an exit point? I've been

looking but

not finding anything. The exit points that I have found do not show me

the

job information.



Does anybody have any thoughts that I can research to find a way do to

this?

[Michael Quigley] Most of the suggestions are for limiting the connections
based on user. If I understand correctly, you're trying to limit the
connections per IP address. There are APIs to help you determine the IP of
the connection. But you can use the JOBLOG_INFO table SQL function to
retrieve the joblog entries and find the IP address. Here's a sample SQL
statement

Select substr(Message_tokens,5,15)
from table(QSYS2.JOBLOG_INFO('*'))
where Message_ID = 'CPI9162'

Select the result into a VARCHAR field and you will have the IP. (it's
null-terminated/filled to 15 characters.)

The challenge will be to decide how to track how many requests the IP
address has--set up a table or something. I like setting up a user space
which I can set a pointer to and track things there. The bigger challenge
will be to only track active connections. That's where using the ability to
route connections to their own subsystem could help when used in
conjunction with the ACTIVE_JOBS_INFO SQL service. You can filter that to a
particular subsystem. I think the routing is by user only, not by IP
address.

If you can route by the IP address, that simplifies your whole scenario as
you could simply set the maximum jobs for the subsystem. You may be able to
do something with the routing entries--I'm not sure and don't have time to
research it right now.

Hope this helps some,
Michael Quigley
Computer Services
www.TheWay.org


--
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.