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