Working with Connection Pools in the Admin Tool

What is a Connection Pool ?
The connection pool is an object in the Physical layer that describes access to the data source. It contains information about the connection between the Oracle BI Server and that data source.
There is at least one connection pool for each database defined in the presentation layer of the Admin Tool. Depending on the need one can configure multiple connection pools to a database. 
Connection pools allow multiple concurrent data source requests (queries) to share a single database connection, reducing the overhead of connecting to a database. The maximum number of concurrent connections to the database must be defined by consulting the DBA because increasing the allowed number of concurrent connections can potentially increase the load on the underlying database.

Connection Pool Properties in the General Tab...

 

Name:  Assign a name for the connection pool. A name is assigned automatically for connection pools created upon import.

Permissions: This option can be used to assign permissions for individual users or application roles to access the connection pool. This feature is not intended to be used for data access security.

Call interface: Identifies the application programming interface (API) with which to access the data source. Some databases can be accessed using native APIs, some use ODBC, and some work both ways.

Maximum connections: The maximum number of connections allowed for this connection pool. The default is 10. This value should be determined by the database make and model and the configuration of the hardware for the computer on which the database runs, as well as the number of concurrent users who

require access.

Require fully qualified table names: This option is to be selected if the database or database configuration

requires fully qualified table names. This option is not available for some data source types.

When this option is selected, all requests sent from the connection pool use fully qualified names to query the

underlying database. The fully qualified names are based on the physical object names in the repository.

Data source name: The name of the data source to which you want this connection pool to connect and send physical queries.

Shared logon: Select this option if you want all users whose queries use the connection pool to access the underlying database using the same user name and password.

If this option is selected, then all connections to the database that use the connection pool use the user name and password specified in the connection pool, even if the user has specified a database user name and password in the DSN (or in user configuration). If this option is not selected, connections through the connection pool use the database user ID and password specified in the DSN or in the user profile.

Enable connection pooling: When selected, allows a single database connection to remain open for the specified time for use by future query requests. Connection pooling saves the overhead of opening and closing

a new connection for every query. If you do not select this option, each query sent to the database opens a new connection.

Timeout: Specify the amount of time and in what increment (such as minutes) that a connection to the data source remains open after a request completes. During this time, new requests use this connection rather than open a new one (up to the number specified for the maximum connections). The time is reset after each completed connection request.

Use multithreaded connections: When this option is selected, the Oracle BI Server terminates idle physical queries (threads). When not selected, one thread is tied to one database connection (number of threads = maximum connections). Even if threads are idle, they consume memory.

The parameter DB_GATEWAY_THREAD_RANGE in the Server section of NQSConfig.ini establishes when the Oracle BI Server terminates idle threads.

Parameters Supported:  When checked tells that that all the DB parameters mentioned in the DB features file are supported by the Oracle BI Server.

Isolation level: The isolation level setting controls the default transaction locking behavior for all statements issued by a connection. Only one option can be set at a time. It remains set for that connection until it is explicitly changed.  

The following options are available:

• Dirty read: Implements dirty read (isolation level 0 locking). This is the least restrictive isolation level. When this option is set, it is possible to read uncommitted or dirty data, change values in the data, and have rows appear or disappear in thedata set before the end of the transaction.                             Dirty data is data that needs to be cleaned before being queried to obtain correct results (for example, duplicate records, records with inconsistent naming conventions, or records with incompatible data types). 
• Committed read: Specifies that shared locks are held while the data is read to avoid dirty reads. However, the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data. 
• Repeatable read: Places locks on all data that is used in a query, preventing other users from updating the data. However, new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. 
• Serializable: Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only if necessary.