Technical Blog Post
Abstract
75 ways to demystify DB2: #3: Expert Advice: Inconsistent resultset for same query from Websphere Application
Body
Do you see different results for same query from Websphere Application, even though the underlying data is not changed?
Consider a table with column of datatype decimal(5,2). If you are inserting "0.18999999999999995" into this table multiple times from Websphere applications using JCC drivers, you will see the results as follows:
COL1
-----
0.19 < First Run
0.18 < Second Run
0.18 < Third Run
0.18 < Fourth Run
After the first run, you will see same result for all subsequent runs.
This behavior is caused due to deferPrepares=true (default setting).
With this default behavior, the JCC driver prepares the statement without querying the target data type.
In this case, JCC driver assumes the target data type (setDouble in this case). So it sends NFLOAT data during first run.
Data Type
Var[00] Type: 0X0B Len: 0X0008
Data
000: NFLOAT:
714960932333552070000000000000000000000000000000000000000000000000000000000000000.000000
The database server replies with actual data type of the target columns after first run, and then JCC driver casts the data to actual data type during subsequent runs.
Data Type
Var[00] Type: 0X0F Len: 0X0502
Data
000: NDECIMAL(5,2): 0.18
This is expected behavior from JCC driver's perspective.
There are two workarounds to get consistent or same results for all insert runs:
a) Set deferPrepares=false
This settings allows the driver to query the target type before preparing the statements.
So in the example discussed above, the JCC driver sends DECIMAL data always.
Please note that this settings needs additional communication between the server and driver, so it can cause performance impact on prepare.
b) Set sendDataAsIs=true
This settings allows the driver to bypass casting to actual data type. i.e. in the above case, JCC driver sends NFLOAT data always.
Please see the following page for more information on the Java driver's properties.
Properties for the IBM DB2 Driver for JDBC and SQLJ
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_r0052607.html
Note: these properties can be set by WebSphere admin console as:
Resources > JDBC Provider > Data Sources > ( Data source name) > Additional Properties > Custom Properties
Thank you for reading our blog series!
UID
ibm11141264