Setting up a JNDI data source in WebSphere 6.0/6.1

Yes. I am now one of those, those people who work with WebsSphere.

WebSphere has its way of doing things that is clear or convoluted or both once you wrap your head around it. On my current project we needed to set up a data source, using JNDI on the server. The data source was an Oracle 10g database.
1. Setting up the database drivers
To be truly precise, the first thing you would need to do is get and store a copy of the Oracle driver jar of your choice. Getting
the driver is pretty rudimentary, but where to store them and how WebSphere will find them is a separate issue.

Log into WebSphere’s administrative console and from the left navigation menu, choose Environment. Expanded, select WebSphere Variables from the menu, which will list WebSphere’s environment variables. Here, look at the value of the WAS_INSTALL_ROOT variable. The location specified will have a lib directory under it. That will be the location where you would drop the Oracle driver jar file.

Next, on the same or previous screen, select to update the value of ‘Oracle JDBC Driver Path’. In the page that will appear, enter the value ${WAS_INSTALL_ROOT}/lib which is where you saved the Oracle drivers.

2. Set up user credentials for the JNDI database connection

WebSphere stores all user name and password information centrally and does so in a different area of the administrative console. Since your JNDI connection will require a user name and password, you will need to store them in advance as a ‘JAAS J2C Authentication Data’. From the left navigation, click Security and then Secure administration, applications, and infrastructure. In the screen that will appear, expand the Java Authentication and Authorization Service on the right part of the page, and then click on the J2C Authentication Data link.

The next screen will show a list of existing WebSphere identities. Click to add a new one, enter the information for it and click save. Always remember to click save if asked to so your changes will be save to the server’s master configuration.

3. Setting up the JDBC provider

Now, expand the Resources section of the left navigation and click JDBC and then JDBC Providers. According to IBM: “The JDBC provider object encapsulates the specific JDBC driver implementation class for the data sources that you define and associate with the provider.” Therefore, select the appropriate scope from the drop down list and click ‘New’. On the next screen, select the database type, provide type, etc., giving it a name you will understand. On the next screen, point to the driver path we specified in step 1 above. Now we can connect to a database from Oracle. Now to connect to THE database we want to.

4. Creating the data source
Now, go back to the list of available data providers. Click the name of the one you just set up and in the screen that again outlines its properties, click the Data Sources link from the right-hand column.

Note: on WebSphere 6.0 most of the information appears on one screen while in version 6.1 the form is split across multiple screens. The gist of the information below is identical.
On the next screen, click the New button. A new form, this time to specify the data source will appear half pre-populated with the data provider information. Here, specify the JNDI name you would want to access the database with (e.g. jdbc/MyDatabase) and from the bottom drop-down, select the J2C identity you will use to log into the database. Then, enter the URL of the Oracle database using a URL that does not use the user name and password. For example:
jdbc:oracle:thin:@[ip address]:[Oracle port (1521)]:[Oracle database name (orcl)]
Finally, hit the OK or Apply button and test the connection.

If all is well, and on the first try something did probably go wrong, look into the steps above and try again. As bad as it looks, if you know the steps, this is way easier than the average open source maze.

MySQL appendix

To set up a MySQL datasource, be sure to download the driver from the MySQL website and follow the general directions above. The only twist is that MySQL requires you to set up a user-defined JDBC provider. The only thing that makes this different is that you need to use the connection pool driver for MySQL instead of its usual driver. The name of this connection pool driver is:
com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource

To specify the database name, port and IP address you need to specify custom properties for the data source. This DevX article has the complete details.

Update (Oct. 15, 2007):
I tried setting up the JNDI data source using the new version of the MySQL connector, and it did not go so well, giving me odd errors. Version 3.x seems stable enough and works.

Tags: , , , , ,

10 comments

  1. Hello Yuval,

    What a great and informative article you have posted for creating a DS on WAS. Thank you so much for it. I had been struggling since couple of days for it. Your article solved my problem and basics about DS on WAS.

    Thanks and Regards
    Sachin

  2. Thanks! Glad it helped!
    If I suffer through something, I try to help others avoid the process.

  3. awesome. thanks.

  4. Thank you.

  5. Hey Yuval,

    you are the man.. I was stuck up with the set up of datasource and then i found out your post… what a descriptive and explanatory post… i just followed the steps and BINGO!!! it got set up in the first attempt itself… thanx dude

  6. Thank you so much for this guide, but I'm still getting stuck. My Oracle database URL uses a OID (Oracle Internet Directory — basically an ldap directory for looking up which specific Oracle host to use…). My URL looks like this:

    jdbc:oracle:thin:@ldap://ldap.acme.com:7777/sales,cn=OracleContext,dc=com

    I cannot for the life of me get it working – the URL is accepted fine, but when I attempt to connect I get a 17,068 sql error (Invalid argument(s) in call)
    Do you know if OID LDAP is supported in WebSphere? Any suggestions on how to debug this error? 

  7. Ouch! Having not worked with WebSphere in a good while, the only clue I can think of is the ldap server.
    If you have an ldap server you can configure WebSphere to communicate with it using JNDI. Maybe that way you can make it findable by WebSphere and possibly get a less ‘offensive’ URL.
    Sorry. It’s been a while.

  8. Thanks a lot. It is really very informative.

  9. Neetha Menon

    how to configure oracle.jdbc.editionName property in a datasource?

Leave a Reply

Your email address will not be published. Required fields are marked *