IBM Support

75 ways to demystify DB2: #3: Expert Advice: Inconsistent resultset for same query from Websphere Application

Technical Blog Post


75 ways to demystify DB2: #3: Expert Advice: Inconsistent resultset for same query from Websphere Application


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:

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                                        
000: NFLOAT:                                                             

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                                        
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                                                       
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!

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]