More on Connection Pools…
14 January 2010
6,748 views
9 Comments
(8 votes, average: 4.38 out of 5)
Loading ...We have discussed the basics about the connection pool in one of the previous post. Connection Pool is a very important repository object and we should have a good knowledge of that. Moreover, many interview questions are based out of this. Now, lets understand something more about it…
Below is the image of the connection pool
We have 3 tabs:
1)General 2)XML 3)Write Back
In the General tab,
We need to give the Name of the connection pool.
Call Interface specifies that the Siebel Analytics will be making use of this application program interface(API) to access the Datasource. We have a list of call interfaces available based on the type of DB we are using as the data source. Like for Oracle we have OCI, ODBC etc.
Maximum Connections tells the maximum number of users that can connect through this connection pool. The default value is 10.
Require fully qualified table names is used to have the tables names with complete details like DB.schema.table_name. When this option is selected, all requests sent from the connection pool use fully qualified names to query the underlying database.
In Data Source Name we need to give the Host String of the DB to which the queries will be routed. We need to give a valid logon info for the data source else will fail to connect to the particular Data source.
If Shared Logon option is checked , then all connections to the database that use the connection pool will use the user name and password specified in the connection pool. 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 Siebel user profile.
In Username & Password and password u need to specify the Username and password of the underlying DB. We can also create a variable for the Username and make changes in that when we intend to change the underlying DB.
The Enable Connection Pooling allows a single DB connection to remain open for specified time so that the future query requests could route through the same connection. It saves the overhead of opening and closing new connections and if this option is unchecked then we for each query the DB need to open a new connection.
Timeout specifies the time for which the connection to data source remains open after the request completes. During this time the new requests make use of this connection instead of opening a new connection. If timeout value set to 0 then connection pooling is disabled.
Execute queries Asynchronously specifies that whether the data source supports asynchronous queries or not.
Execute on connect allows the Siebel Analytics Admin server to specify a command to the DB each time the connection to the DB is established. It can be any command accepted by the DB.
Parameters Supported when checked tells that that all the DB parameters mentioned in the DB features file are supported by Siebel Analytics server.
Isolation Level controls the transaction locking behaviour for all statements issued by a connection.
-
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.
-
Dirty Read- 0 Locking. Can read uncommitted or dirty data, change values in data during read process in a transaction. Least restrictive of all types.
-
Repeatable Read- 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 transaction.
-
Serialization- Places a range lock on data set preventing other users to insert or update the rows in data set until the transaction is complete. Most restrictive of all.
Great information.thanks for this piece
Thanks for your coments Pooja and keep posting if you have any other queries…. I will try to answer them with best of my knowledge..
Thanks rohit i learned some new info
Thanks Rohit, This helps a lot..
How to setup two different connection pools for two different schemas in BI?
I created two diffenent connection for two different schemas, but I got the ODBC errors. Any idea?
View Display Error
Odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)
Thanks,
Kien
Hi Kien,
First thing is that you dont require 2 different connection pools for 2 different DB schemas until they are in different DBs or you want it that way. The other thing is that, this error generally comes when there is some problem with Schema name or DB connections. Try selecting the option Require Fully Qualified table names in connection pool. I think that might solve your issue.
Let me know if this doesnt work…
Thanks Rohit! It works! Kien
Hi Robit,
I ran into an issue of importing the objects into existing table in the physical layer. It keeps creating two connection pools names with same name. I checked the ODBC and tnsname.ora have a correct SID. Any idea why?
Thanks,
Kien
Hi Rohit,
Any help with above issue. ( It keeps creating the new connection of the same connection name, I end up to copy the partial field names into existing tables and delete the new connection)
I have an another issue is that I was able to import the data from database into Physical layer of BI through either OCI or ODBC 3.5 connection type, but I could not be able to view the data fro some field names, but the values are existing in the database. Any idea?
Thanks,
Kien
Leave your response!
Recent Posts
Categories
Archives
Tags
Most Commented
Most Viewed