0

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.

mreyeros
  • 4,359
  • 20
  • 24
  • Highly recommend breaking up that brick o' text some to make it more readable... – JNK May 01 '12 at 19:41
  • Would it be possible for you to migrate your application to a more recent version of PowerBuilder? The issue you are experiencing may have already been addressed in a more recent version. – Bernard May 01 '12 at 19:49
  • At the moment that is not an option. The long term plan is to completely do away with this app and convert it to a web based solution using .NET – mreyeros May 01 '12 at 19:56
  • How does the number of connections change over time? How many does a user get when he first starts? Does it increase as the user does things with the application? – Hugh Brackett May 02 '12 at 17:19

1 Answers1

0

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.

Terry
  • 6,160
  • 17
  • 16
  • "You seem to be wanting to get an answer that will suggest PowerBuilder will natively do something very stupid" In no way was my question asked with this intention. As someone that has never been exposed to PowerBuilder, it was my intention to pose the question here in the hopes that someone more experienced with that development environment could point me in the right direction. – mreyeros May 02 '12 at 20:50
  • The reason that this is coming up now is, as stated, this application has been inherited after the acquisition of the company. This was a small "mom and pop" environment with one lone developer whose only concern was getting things to work. Attention was not paid to how the application was effecting the database, connection wise at least. After posing the question to the developer, his response was that he had never dealt with checking connections because it just worked and he then asked me was this really a problem that needed to be addressed. – mreyeros May 02 '12 at 20:52