Good afternoon maybe someone can shed some light or at least point me in the direction to figure out why a PowerBuilder 9 application would cause 100's of database connections to be opened in our SQL server database.
Background
Our organization recently acquired another company whose main application is built using PowerBuilder 9 sitting on top of a SQL Server 2008 R2 database. Users of this application are logging into the database using their domain credentials and their are roughly 15-20 active users. We have seen that at any given moment in time these 15-20 users can have 100's of connections/processes running in the SQL Server database.
Example
Yesterday for instance there were around 530+/- connections to the database from these users. When running a query against the sys.sysprocesses table I can see that all of the connections have a status of "sleeping" and a cmd value of "AWAITING COMMAND".
We are not sure if this is caused by the developer not opening and closing connections correctly or if this is normal for a PowerBuilder 9 application.
Any information would be helpful and appreciated. Thanks in advance.
You seem to be wanting to get an answer that will suggest PowerBuilder will natively do something very stupid. Sorry, you're not going to get it in this case, I don't think. Like any tool, you can make a PowerBuilder application do a lot of things. If there is an architecture that PowerBuilder's nature and infrastructure drive you to, it's a single-threaded, single connection application.
I could make the application multi-threaded, and connect from each thread (threads can't share a connection), but the documentation on this isn't strong, and the method is obtuse, so it's not likely.
I could make the application connect to the database 1000 times, but that would mean going beyond the built-in single global connection object and instantiating 1000 new connections. This is a little more obvious on how to do than multi-threading, but I'd have to ask why someone would program this?
Here's another thought: If this app has been around since about 2003 (the release of PB9), why is this just coming up now? One suggestion is that maybe it's the matching with a DBMS engine that came out years later? I've known people that have successfully used a version of PowerBuilder with a later DBMS engine, but then it's pretty conceivable that you've stumbled on something that's incompatible, that wasn't predicable at the time of the development of PB9.
I'd be surprised if your next step(s) weren't a lot of tracing, trying to figure out what is going on. SQL Server has great tracing tools. PowerBuilder has it's own ability to trace database connections, and while they aren't as elegant, they are pretty precise in describing what PowerBuilder is "throwing over the wall" to the DBMS client software, and is getting back.
Good luck,
Terry.