By Tim Dexter-Oracle on May 09, 2008
Another tenous and cryptic title, today we're talking connection pooling with BIP.
We have had a question recently on how to set up a connection pool for BIP rather than direct JDBC connections. Pooling has its advantages of course - rather than each report having its own 'personal' connection to the database at runtime it which can get expensive very quickly in terms of resources. Instead, BIP can pick up a connection from a pool, assign it to the report, once the data is fetched the connection is released back to the pool.
Now BIP provides a page to enter the JNDI connection pool information but we are little vague or we at least expect you to know what you are doing when it comes to setting up the pool in the J2EE container. To supplement the doc I thought I'd walk through setting up the connection pool in OC4J and then how to get BIP to use it.
Firstly, you are going to need the oc4jAdmin password to be able to do any of this. If you installed BIP on your server then the installer will have prompted you for a password - hopefully you can remember it. If someone else installed it for you - either talk nicely to them to get the password or buy them a beer and get them to read this blog article and get them to set up the pool. Once you have the password login to the Enterprise Manager, typically:
This will get you to the main home page.
Now click on the Administration tab and then on the Services > JDBC Resources icon. You'll end up here:
There is a sample pool and data source set up already for reference. We are going to create a new pool and datasource to use it. Click on the 'Create' button for the Connection Pools section.
Select the BIP application and 'New Connection', this is going to make the pool available to the BIP server. Now hit continue.
Fill out the form providing a name for the pool, etc. Check the documentation if you need to get into Connection Factory Properties for your database.
Before you move on test the connection.
Now move to the Attributes page to fill out the size of the pool, etc. Worth reading the OC4J doc here to get some advice on the numbers to use. It going to depend on your expected reporting load on the system, etc.
The Proxy Interfaces tab is only needed to wrap vendor implementations of java.sql objects when using managed data sources. I dont need it as Im using Oracle objects - check the doc if you need more info.
The pool is now set up so click Finish. Next we need to create a DataSource for the pool - this will manage the pool.
Assign the Application and select Managed Datasource to allow OC4J to 'look after' it for us.
Fill out a name for the dataource and enter the JNDI location. Check the doc for more details but here's what it states on the JNDI name.
Enter the Java Naming and Directory Interface (JNDI) location (or path) for the data source. Deployed applications use this information to locate the data source. For example, the JNDI location of the OracleManagedDS data source is jdbc/OracleManagedDS.
The one I created has the following location, 'jdbc/BIP10gSource', its a virtual location so you will not find it on the disk. The location is important as we are going to reference it in the BIP JNDI connection setup. Once you're done click Finish, you are taken back to the main data source page where you can test the datasource. We now have a pooled connection we can use in the BIP server to a database.
Now bounce the OC4J instance and bring up the BIP server and get in as an Administrator and navigate to the JNDI Connection page (Admin > Datasources > JNDI Connections) and Add Data Source.
Give it a name and the JNDI location that you defined in the OC4J UI - then hit Test Connection to make sure its all working.
You can now use the connection for your reports - just select the pool name rather than a direct database connection. You're now swimming so gimme 30 laps!