Weird DB2 database issue : Websphere Connection Pooling

Question

I am running a query from my java based web app running in a Websphere container. This query however, being pretty simple, fails with a weird erorr as follows:

[5/15/09 16:50:33:828 IST] 0000001e SystemErr     R com.ibm.db2.jcc.b.zd: Invalid data conversion:Requested conversion would result in a loss of precision of 40000
[5/15/09 16:50:33:828 IST] 0000001e SystemErr     R     at com.ibm.db2.jcc.b.q.a(q.java:137)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr     R     at com.ibm.db2.jcc.b.q.a(q.java:1189)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr     R     at com.ibm.db2.jcc.b.ad.a(ad.java:1217)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr     R     at com.ibm.db2.jcc.b.ad.kb(ad.java:2977)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr     R     at com.ibm.db2.jcc.b.ad.d(ad.java:1970)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr     R     at com.ibm.db2.jcc.b.ad.d(ad.java:2342)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr     R     at com.ibm.db2.jcc.b.ad.U(ad.java:489)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr     R     at com.ibm.db2.jcc.b.ad.executeQuery(ad.java:472)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr     R     at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:559)

The query is pretty simple : it is as simple as

select field1, field2 from <xyz table> where <xyz_pk> = ?

The primary key is a INTEGER(4) and has data that has values up to 99999999. But however, when I run this query is run in my web app on a connection obtained from websphere connection pool, it starts failing for pk values > 35k+. In the jdbc binding code, I tried doing a preparedStatement.setInt() and preparedStatement.setFloat(). But nothing seems to work!! It just works for anything below 35k+ and fails for everything above that.

Java's int size is much bigger than 35k+, so why would this query fail with this error? This happens just from my application, when I try the same query with a database client of my choice, proper results are being obtained for all values of the pkey!

Did anyone faced this issue before? If yes, how did you get around it?

Answer 1

Integers don't have precision. There must be a float or real somewhere in the chain of column to variable binding. In that case it doesn't matter whether you start with an int, Int, long, or even a byte; the conversion between whole and real would trigger the warning. Look at all the the bindings that happen from the column to the last variable. Does your prepared statement redefine any of your field types?

Answer 2

I think the best thing you can do is to turn on the trace logs in WAS for the JDBC resource adapter and then look at the sql that is actually being issues to the DB.

Sorry cannot be much more help.

Karl

Answer 3

Could you maybe post the code that causes the failure?

I remember having something like this on websphere and had to change the precision of the database fields. It had to do with the conversion the database and the java space in that on the java side the data type was far bigger than the datatype on the database.

We changed the data type on the database and things improved.

Answer 4

I remember having this problem a while ago. I think I solved it by converting the value directly in the SQL statement. Something like this:

select DECIMAL(field1) AS field1, field2 from ...

Answer 5

My bad.. the issue was in binding. My query had an integer and small int field and I was binding small to int and int to smallint, hence the problem.