Portaldomosphere

You Got your JDBC Driver Stuck in My iSeries!


Whenever I run in to an issue like this, I try to remember and blog it. This is one of those issues that was not obvious and quite honestly, I came by the answer completely by accident.

In the Websphere admin console, you can set up JDBC resources to use in your applications. When I am working on my local portal, I just use Cloudscape Network Server as a data source. But in this case, my customer has an iSeries that the Portal application will be reading. Specifically, DB2 UDB for iSeries.

BACKGROUND

For years, users and administrators alike have enjoyed querying the iSeries for data without having to specify a library, just as long as the username on the iSeries has the library in their default list. You would easily query the iSeries server and specify the table that you needed access to. This is very helpful when going from a development box to a test box or a production box. On the other servers, you may not have the table in the same library as your dev server so if your SQL statements only have the table specified, you will not have to change your code.

When my customer let me know about this, I had to make changes across my SQL calls considering I was specifying the schema in cloudscape. (BTW - schema and library - when it comes to SQL - are fairly synonymous). I DID have to move the tables in cloudscape to a schema with the same name of the user I was using in my cloudscape database. That way I could create SQL calls without specifying a schema or a library. We changed the advanced parameters on the JDBC library for the parameter "libraries" and added the library the table resided in.

THE ISSUE

Using the JDBC Driver in that comes with Websphere 6, we were able to connect to the iSeries. The problem was that everytime we tried to make a call to the iSeries with just the table, it would come back stating that there was no table in library *QTEMP. This made it appear that the calls were specifying the QTEMP library.

TROUBLESHOOTING

1. The fact that I am using Portlet Factory was considered as the first culprit. Although improbable, God know what kind of code it is generating against the iSeries. We disassembled the code being generated by Portlet Factory and found no evidence of the library QTEMP.
2. We looked at the advanced property "libraries" again and read the instructions for the property. In the instructions it stated that you can separate the libraries with commas or spaces. We had both in between each of the libraries, so we changed this parameter so we just had commas and one HUGE line of libraries. After a restart of the WAS server and Portal, this did not fix the issue.
3. After this, I refocused my efforts on the driver itself. There are three possibilities for JDBC iSeries drivers in WAS : "JDBC iSeries Toolbox Driver for DB2", "JDBC iSeries Toolbox Driver for DB2 (XA)", JDBC iSeries Driver Native. After trying each of these drivers, and searching through them, I noticed instructions in the Native driver's advanced properties that did not exist in the Toolbox drivers.

THE SOLUTION

In the Native iSeries DB2 driver, there is a advanced property called "naming". The property is in the toolbox drivers as well but the instructions are different. The native driver stated that when using the "libraries" property, this "naming" property should be set to system so that the jdbc driver uses the native database calls for the iSeries. This property is valued by default as sql. If left as sql the jdbc driver ignores the "libraries" property. So we changed the naming property to system and restarted the server. This allowed everything to work and the portlets started working.

CONCLUSION

When using the "libraries" advanced property in the jdbc driver for iSeries toolbox, making sure you set the "naming" parameter to system instead of sql.