executeQuery returns null with DB/2 v7 and JDBC2 driver under Websphere

have you prototyped the sql statement at the command line to see what is returned?
if null is not returned, then there is something on the test server setup that is not identical to the production setup.

Yes, the SQL has been tested at the command line and returns a single row.  I kind of realise there is a difference between the servers - what I'm really doing is asking for suggestions for what to check, as I think we've covered everything.  We've checked the JNDI setup, for example, all of that stuff.  I was hoping someone out there had had the same problem and knew that it was something obscure that we needed to fix.

If I can't find the problem, I'm going to recommend a re-install of the drivers, just in case.  Hopefully that won't represent too much inconvenience, as it's a test server.

try checking the db2cli setup on test machine and compare to production machine
db2 get cli cfg for section <dbname>
there may be a patch listed on production setup that interprets the null differently, or, perhaps test machine has no cli setup at all

That's interesting ghp7000 - I tried that on the test server, and all that came back was a DBALIAS=... line.  I do not have access to the live server, but I've send them an email asking them to repeat your suggestiond and report the output to me: I'll let you know if there's anything different in there.

which db2 driver are you using?

.app or .net or .jcc ?

Hi sachinwadhwa, I'm not actually sure what that means, although I can guarantee it won't be .net - this is a Websphere Java app written using Eclipse.  Is that information available in Websphere config?

Just for information, I've run a couple of other commands in addition to ghp7000's "db2 get cli..." one.  Namely:

db2set -all
db2 get cli cfg

Just in case that's relevant.  I've kept the output and I'm waiting to see whether these commands can be run on the production server (I have a time zone difference with my client, so email communication is sometimes inefficient - this may take a day or so).

easier way, from db2 command line go to sqllib\bin directory launch db2ca.bat.
from view menu select advanced view, then select data sources tab
hightlight db name and right click, choose cli settings
from cli settings window select settings tab
if you want to see all the possible settings, choose add

with this same tool you can see the db2set parameters and  the dbm (database manager) config
Note that you can have the production machine export all the settings and then import them with this tool.

Thanks ghp7000, I'll try that and see what it can do, then forward instructions to my client.

Hmm... I don't seem to have a db2ca.bat.  Is it the Control Centre you want me to run (i.e. the one on the Start Menu)?  I'm thinking that it probably isn't, as this doesn't have an "Advanced View" submenu as described.

oops sorry you are using ver 7 the command to run is db2cca, however, its been a while since I used ver 7 and now I'm not sure what you can do with it, but try itout anyways, you cant do yourself any harm

Not to worry ghp7000, I had already found that program on the menu anyway (client configuration assistant, I think it's called).  I have exported the client config from there to a .spf file, and I'm still waiting for a response from the customer - sorry for the delays, but we're really at their mercy as I have no access to the production server :(

OK, time for an update: Just to confirm, I have re-run the test and the null reference is still being returned from executeQuery().

Using the config assistant, I also turned on the trace service for the ODBC connection.  I got a reasonable amount of output while the app started up, and couldn't see anything too alarming (I can post this if you like).  However - and this is interesting - running the null reference test did NOT add any output to the trace file.  Hmmm... does this mean that there is something corrupt in the driver, perhaps?

Also, I've had the output files back from the production server.  The get cli cfg command output didn't seem to show any significant differences between the two machines - certainly I couldn't see any patches listed.

The differences between the two exported config outputs from db2cca was a bit tough to interpret, as there were various extra nodes listed on each machine, that the other didn't have, but there are other applications using these servers, so that's probably to be expected.  Here's a list of what I thought might be relevant (XXXX used for confidentiality: XXXXXX05 is the production server):

note: the d/b in use is the REG one, which isn't even listed in these differences, as its entries were all identical between the two systems.  I have a suspiscion that the text below is not going to be useful to you, but I'll post it anyway.

Live ------------------------------------------------------------

[NODE>TCP0000]
DB2SYSTEM=XXXXXXX05
Instance=DB2
ServerType=Unknown
Protocol=TCPIP
Hostname=xxxxxx.xxxx.xxx.xxx
Portnumber=50000
Security=0

Test ------------------------------------------------------------

[NODE>TCP0000]
ServerType=Unknown
Protocol=TCPIP
Hostname=10.131.40.42
Portnumber=50000
Security=0

(The test server is missing DB2SYSTEM= and Instance=)

=================================================================

Live ------------------------------------------------------------

[NODE>TCP0002]
ServerType=Unknown
Protocol=TCPIP
Hostname=10.151.13.46
Portnumber=50000
ServiceName=db2cDB2
Security=0

Test ------------------------------------------------------------

[NODE>TCP0002]
ServerType=Unknown
Protocol=TCPIP
Hostname=10.151.60.43
Portnumber=50000
Security=0

[NODE>TCP0003]
ServerType=Unknown
Protocol=TCPIP
Hostname=xxxxxx.xxxx.xxx.xxx
Portnumber=50000
Security=0

[NODE>WAS40]
ServerType=Unknown
Protocol=TCPIP
Hostname=10.131.13.47
Portnumber=50000
ServiceName=db2cDB2
Security=0

=================================================================


Live ------------------------------------------------------------

Test ------------------------------------------------------------

[DB>TCP0000:REGPRD]
Dir_entry_type=REMOTE
Authentication=NOTSPEC
DBName=REGPRD

(I don't think this is relevant)
=================================================================

Live ------------------------------------------------------------

[CLI_ODBC>WSCDBMED]
DataSourceName=WSCDBMED
DataSourceType=System
ODBCParameters=No

Test ------------------------------------------------------------

(test doesn't have this CLI_ODBC entry - again, I don't think it's relevant).

=================================================================


I really don't know what to try next. On the assumption that the driver may be corrupt, perhaps I should recommend a re-installation.  What do you think?

Interestingly, somebody else seems to have had the same problem a few months ago, here:
http://forums.agitar.com/agitar/board/message?board.id=Products&message.id=1295

...though I have no idea what "agitating" is :)

I have seen a few other references on the web during my searches, claiming that executeQuery() is returning null, contrary to the API docs - and invariably they are met with rebuttals - but I have so far not seen any of them resolved.  I think there's somthing fishy going on in that driver...

I just re-read sachinwadhwa's question, and due to my unfamiliarity with DB2 JDBC drivers, I misunderstood.  All I can tell you at this stage is that we are using the following class in Websphere as a DataSource:

com.ibm.db2.jdbc.DB2ConnectionPoolDataSource

In other words, we use connection pooling, but I am still unsure whether it's the type2 or type4 driver, as I don't know how Websphere handles the connections.  I don't think it's the Universal Driver though, I think it's the Legacy/CLI one.

OK, I've been told that it's almost certainly the Type 4 driver.

Found it! :D

When we tested SQL SELECT from the DB2 command line, we had run this from the Windows menu, and that particular item points to C:\Program Files\SQLLIB\bin\db2cl.bat, so it was running the DB2 drivers in C:\Program Files, which do actually work, so the SQL was running successfully.  However, because Websphere was pointing to the ones in C:\SQLLIB, it was running those instead.  When we ran db2cl.bat in C:\SQLLIB it failed immediately.  That must be a corrupted or old installation.

So we changed Websphere to point to the drivers in C:\PROGRA~1\SQLLIB, and restarted it, and it appears that the application is now able to access the database.

Sorry for all the red herrings, everyone!

its usually that type of problem, the error is difficult to trace because there is no documentation about the system which would have told you about the previous install, thats why documentation is so important

As I told you, its a DB2 Driver issue!

Geat you have found it yourself.

Yes indeed, knowledge of the previous installation was exactly what I was missing!

My thanks to both of you for your contributions.  I'm taking it that there are no objections to the closure of this question then?

closure is a wonderful thing