Toad World® Forums

Release multiple connections within Toad Data Point

I am currently using TDP 4.0.0.396, and our primary data tables are stored on a production server with an IBM iSeries DB2 platform. Whenever I open up a query with multiple subqueries, I can hover over the connection name and see that multiple connections are open. For example, earlier today I had a query open with 10 subqueries and the hover message shows “0 of 12 connections in pool active”. Apparently these multiple connections are consuming large volumes of server resources, and the production server dba has asked if there is a way to limit this.

So I guess my question is: Is there a way (short of closing the query) to release or reduce these multiple connections while in an active session?

I have looked through the Tools / Options / Database folder but have not made any changes (everything is currently default settings). Is there some setting that I should change here?

Thanks for your help.

Each subquery is really a subquery with it’s own connection. We also use a separate connection for each editor, one for object explorer and one for data tab. We use connection pooling and use idle connections but once one is added it stays in the connection pool. This usually is only an issue in shops where the DBA is trying to restrict connections.

Debbie,

Thank you for responding.

I guess we are a shop where the DBA is trying to restrict connections. As such, is there anything that we can do to limit the number of connections used or to release the connections as quickly as possible? I guess other than the obvious of limiting the number of subqueries and leaving queries open for only short amounts of time. Specifically I am wondering if there is some kind of setting that we could change within Toad.

There isn’t really an option for that. But if you send the sql statement to the SQL Editor it will only use one connection.

As a side note, we’ve run into the exact same situation with a vendor’s Oracle database - they only allow three connections per ID, so just one session in Toad is going to use all three.

I get why they’re broken out the way they are, but perhaps it’s worth another look for these types of situations? Perhaps an option to cache data from Object Explorer on a connection-by-connection basis, with the caveat that you’ll have to refresh it manually?

I had this type of problem with Oracle for a long time…

I used to query hundreds of servers via dblinks and connections remain active too long !

In Oracle, if you want to close databases links, you have to commit before, even “select” statements…

You can try to commit statements…

I hope this helps…

Franck

We have added caching beyond connections for the ODBC provider but this does not limit the number of connections. This request is very difficult to consider. Most users have multiple editors and windows open performing various actions. if we limit the connections the user would need to limit their work. it is best to use the features that exist in Toad already and see if they like the behavior. Change the option for the editor to share connections and stop using Navigation manager/object explorer.

Debbie,

Is this the location where I would change the option for the editor to share connections? The check box was previously checked and I un-checked it.

yes, that is the one

Sorry to revive this post years later, but I thought it was better than starting a new thread...

I am having the same issue with Teradata at my company. The limit is set to 6 concurrent connections per user...

I typically have automation scheduled to run under my credentials, while I query within my own tabs, and Toad takes up two additional connections for the Object Explorer and data tab. As such, I generally feel that is only 'safe' to use 1 or 2 editor windows at once, and will often change a tab's connection to a different server when I need to keep it open but am not actively using it.

I've petitioned the DBAs to increase the limit with no luck. The big issue, though, is when I breach the limit accidentally... The server seems to respond by booting ALL of my connections, which throws an error in Toad as it attempts to reconnect, then my connections get stuck open in an 'idle' state while Toad shows I'm not connected. Even restarting my computer won't release these connections, so I have to message the DBA team to have them abort on their end...

Having the option to have Object Explorer, etc. cache for longer periods of time and close the connection between refreshes would help immensely. I generally have to right click and refresh Object Explorer upon table creation anyway, as I've disabled the option that auto-refreshes it upon every create/drop/etc. command.

Thank you!

Hi Jesse,
we'll discuss it and write back.

Hi @JesseJamesP,
unfortunately we don't see any reasonable solution to this. Please try some external 3rd party tool that could be able to do it.