IBM Support

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

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!

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

UID

ibm11141264