Connection Pooling

Big Data Warehouses and a large number of Data Scientists using these warehouses can put a strain on your Big Servers.  If you are a Database Administrator managing a large data warehouse, you are always trying to get the most out of your resources as the demands of your user's grow.  Having your users minimize the number of database connections they use can greatly reduce the amount of load on your servers.  With this in mind, we've added a Connection Pooling option which can be enabled on a per-server basis.  The Server Registration dialog has a new option in the Advanced tab called "Connections Pooled", which exists for all database types.  This option is disabled by default.  If the user enables this option, Aqua Data Studio will manage a connection pool which will be used by the Query Window.  The schema browser will still have its own connection.  Other components of Aqua Data Studio will still use their own connection.  

When using a Query Window with a connection pool, the query window will have the following actions disabled : Auto-commit, Commit, Rollback, Reconnect and Change Server.  It will also disable the ability to change the schema context for a connection (which is currently only supported by Oracle and DB2).  The user may still change the context of the database connection via the database combo box.  Because the Query Window does not have a single dedicated connection, the Query Window will set the database context on the connection at the time of query execution.  A Query Window using a connection pool will acquire a connection from the pool before query execution, then release the connection after the execution is completed.

Aqua Data Studio will manage a connection pool for each Server Registration which has the option "Connections Pooled" checked.  The connection pools do not have a minimum or maximum size, but do have an ideal size (default ideal size is 10 and can be changed in File > Options > General > [Connection Pool Ideal Size]).  Each pool starts with 0 connections and allocates as needed for a Query Window.  The connections are not closed until all Query Windows for that Server Registration are closed.  If a Query Window releases a connection and the connection pool size is greater then the "ideal" size, then the pool will close the connection instead of placing the connection back into the pool.  When a Query Window tries to acquire a connection from the connection pool, the pool will check to see if the connection is closed before returning the connection.  If the connection is closed, the pool will discard the connection and look for a new one.  The connection pool determines if a connection is closed by calling the JDBC driver method java.sql.Connection.isClosed().