Solved

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

Posted on 2006-07-10
23
1,051 Views
Last Modified: 2008-03-04
I am investigating a database connectivity problem involving DB/2 under Websphere.  Details:

DB/2 v7 (remote d/b server, and local client on web app server)
Websphere v5
JDBC 2.0 DB/2 driver in SQLLIB\java\db2java.zip (it appears that the usejdbc2 script has been run)

The machine is a test server, set up identically (as far as my client can tell) to a live server that is known to work.  However, they reported a problem connecting to the database.

Careful debugging revealed that the following line of code is behaving in a way that contradicts the documentation:

ResultSet set = statement.executeQuery();

executeQuery() is never supposed to return null, but it demonstrably does.  All the other objects, statement, connection etc. have been checked and look fine - it's just that instead of a resultSet (even an empty one would be good) we are getting a null reference back.

Any ideas?



Edit: just wanted to add that this is on Windows 2000 Server.  Thanks!
0
Comment
Question by:mtorr
  • 13
  • 6
  • 2
  • +1
23 Comments
 
LVL 13

Expert Comment

by:ghp7000
ID: 17073733
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.
0
 

Author Comment

by:mtorr
ID: 17073798
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.
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 17073975
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
0
 

Author Comment

by:mtorr
ID: 17074254
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.
0
 
LVL 7

Expert Comment

by:sachinwadhwa
ID: 17079682
which db2 driver are you using?

.app or .net or .jcc ?
0
 

Author Comment

by:mtorr
ID: 17079749
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?
0
 

Author Comment

by:mtorr
ID: 17080681
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).
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 17080986
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.
0
 

Author Comment

by:mtorr
ID: 17081013
Thanks ghp7000, I'll try that and see what it can do, then forward instructions to my client.
0
 

Author Comment

by:mtorr
ID: 17081091
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.
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 17082611
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
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:mtorr
ID: 17088794
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 :(
0
 

Author Comment

by:mtorr
ID: 17098122
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?
0
 

Author Comment

by:mtorr
ID: 17099739
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...
0
 

Author Comment

by:mtorr
ID: 17106905
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.
0
 

Author Comment

by:mtorr
ID: 17107629
OK, I've been told that it's almost certainly the Type 4 driver.
0
 

Author Comment

by:mtorr
ID: 17108580
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!
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 17130158
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
0
 
LVL 7

Expert Comment

by:sachinwadhwa
ID: 17130188
As I told you, its a DB2 Driver issue!

Geat you have found it yourself.
0
 

Author Comment

by:mtorr
ID: 17130266
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?
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 17131349
closure is a wonderful thing
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 17156968
Closed, 400 points refunded.
Netminder
Site Admin
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
iseries sql set statement failing 2 242
Tricky SQL question 13 382
AS400 Forms 9 97
DB2 return first match 3 91
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now