Connection pool properties

Connection pool Properties:

A connection pool has 3 tabs

1  General
2  XML
3  Writeback

1  General 

Name: This is to give a name for the connection pool

Call Interface: This specifies that the analytics will use this driver to connect to the data source. In this we have a list of options available such as ODBC, OCI, etc. ODBC can be used for any kind of data source whereas OCI is used only with Oracle data source.

Maximum Connections: This is used to define the number of concurrent users in the organisation. This is generally specified by the DBA. The default value is 10. Once the limit is reached BI server checks for the other available connection pools, if none of them exists it waits until the connection is available

Required fully qualified table names: When this option is selected, all the requests sent from this connection pool use fully qualified table names i.e., DB.schema.tablename

Data Source Name: As the name suggests Data source name to which the queries will be routed.

Shared logon: If this option is checked all the requests through this connection pool use the username and password specified in the connection pool. If this option is unchecked all the connections through this connection pool use the database user ID and password specified in the DSN

Enable Connection Pooling: It allows multiple concurrent query requests to share a single database connection. This reduces the overhead of connecting to a database because it doesn't open and close a new connection for every query. If this option is unchecked each query sent to the database opens a new connection
Timeout: It is the idle time (after the request completes) for the connection to be closed. During the idle time new requests use this connection instead of opening a new connection. If this is set to 0(zero) then it means that the connection pooling is disabled.

Use multithreaded connections: If this option is checked Oracle BI terminates idle physical threads or queries else the one thread is tied to one database and these idle threads consume memory

Execute queries asynchronously: As the name suggests if this option is checked then the queries run asynchronously else they run synchronously. By default this option is unchecked

Execute on Connect: This is used for the server to specify a command each time the connection is established with the database. This can be any database accepted command. 

Parameters supported: If this option is checked, that means all the database parameters mentioned in the database features are supported by the server. This option is checked by default

Isolation level: This option controls the transaction locking for all the requests issued by this connection. These are of 4 types

a. Dirty Read: This is known as 0(zero) locking. It can read uncommitted or dirty data, change values in data during read process in a transaction. Lease restrictive of all types
b. Committed Read: Locks are held while the data is read to avoid dirty reads. Data can be changed before the transaction ends with that connection
c. Repeatable Read: This places locks on all data used in a query so that nobody can update the data. However new rows can be inserted by other users but will be available in later reads in the current transactions
d. Serialization: This places a range lock on data set preventing other users to insert or update the rows in data set until the transaction is complete. It is the most restrictive of all types