RE: Blocking excessive JDBC connections

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