Connection Pool in WebSphere

[chandubcs]

Hi all,
I have WSAD 5.1 and Oracle 8.1.7. Presently I am hardcoding the DB URL, username, password using JDBC thin driver. I want to prepare a connection pool for this Oracle connection in WSAD and use that connection pool name from my applications. How can I setup or create a connection pool in WSAD for Oracle database.
Thanks in advance.

[maneesh subherwal]

This is a slightly lengthy description, so please bear with me...
1. Go to the 'Server Perspective' in WSAD.
2. If you have a server set up already, u will need to click on the server to set up the configuration.
3. Go to the Datasource Tab
4. Under 'JDBC Provider List', select the Add button.
5. Select Oracle and the Oracle Thin Driver.
6. Set the implementation class name to oracle.jdbc.pool.OracleConnectionPoolDataSource and specify a name (oracleDS) for this datasource.
7. Under DataSource, set a name (ORADS) and JNDI name (jdbc/ORADS). leave everything else as is.
8. Under Resource properties (with your ORADS Datasource selected), Add name as 'URL' and value as 'jdbc racle:thin:@serverName ort b'.
9. Save this and restart your server.
10. Use the following code to retrieve the datasource:
try
{

Hashtable parms = new Hashtable();
parms.put(Context.INITIAL_CONTEXT_FACTORY,
"com.ibm.websphere.naming.WsnInitialContextFactory");
parms.put(Context.PROVIDER_URL, "iiop:///");
InitialContext ctx = new InitialContext(parms);
DataSource ds = (DataSource)ctx.lookup("jdbc/ORADS"); // if that is what you named it...

}
catch(NamingException e)
{
System.out.println(e);
}

11. To Use the datasource,
ds.getConnection(userId, password);
I hope this helps...
Thanks,
Maneesh

[Mahesh Chalil]

One point,
Please start using default InitialContext constructor. Now in version 5, there is local namespace for each server proces..

[chandubcs]

Hi again,
I tried that code, its good to know that it worked properly and I got connection. Thanks.
I have created a data source name as "DSN1", But I am getting some messages at server output, like:
Resource reference jdbc/DSN1 could not be located, so default values of the following are used: [Resource-ref settings]
res-auth: 1 (APPLICATION)
res-isolation-level: 0 (TRANSACTION_NONE)
res-sharing-scope: true (SHAREABLE)
res-resolution-control: 999 (undefined)
[Other attributes]
isCMP1_x: false (not CMP1.x)
isJMS: false (not JMS)
[11/19/03 13:40:02:793 EST] 40000c3d ConnectionFac I J2CA0107I: Component-managed authentication alias not specified for connection factory or datasource DSN1.
[11/19/03 13:40:05:306 EST] 40000c3d WSRdbDataSour u Database version is
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
[11/19/03 13:40:05:306 EST] 40000c3d WSRdbDataSour u JDBC Driver version is
8.1.7.1.0

[Mahesh Chalil]

Yes.. it will happen as you dont have a reference in your web deployment descriptor. You are trying to refer to this datasource from the Web project.. isnt it?

[chandubcs]

Yes, I am calling it from a Web Project (from JSP). What is the best way to change the code to avoid that warning message.
Thanks,

[Mahesh Chalil]

Go to Web Deployment Descriptor, click on references tab, click on Resources tab
Click on Add button
(New ResourceRef) - replace this with the jndi name you gave in ur program. For example if you gave java:comp/env/jdbc/testds, type jdbc/typeds.
Type = javax.sql.DataSource (Make sure you havent selected javax.activation.DataSource:-)
Under Websphere Bindings
jndiname - name you gave while created your datasource.
Save
Restart your server.
Good luck
-Mahesh