DB2 FOR LUW / DB2FORLUW / 910 - DB2

Using the JCC driver (Java Common Client, also known as 
IBM Data Server Driver for JDBC and SQLJ), 
when you call the executeBatch() method of 
PreparedStatement to execute certain INSERT statements, and 
you have used the setLong() method of PreparedStatement to set 
the value of a statement parameter that is cast to 
DECIMAL, a java.lang.ClassCastException occurs. 
 
The problem occurs only if all the following conditions are 
true: 
- the JCC property deferPrepares=true 
- the database server is DB2 on zOS 
- the INSERT statement is of the form 
   INSERT INTO ... SELECT ... CAST(? AS DECIMAL(...))" 
 
 
 
The problem can be reproduced by creating a table like this: 
 
 create table tab1(col1 decimal(18, 0)); 
 
 
and then running a program like this: 
 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.SQLException; 
 
public class TestPrepare 
{ 
    public static void main(String[] args) 
    { 
        Connection cnn = null; 
        PreparedStatement ps = null; 
 
        try 
        { 
 
            Class.forName("com.ibm.db2.jcc.DB2Driver"); 
 
            cnn = DriverManager.getConnection( 
             "<connection string>","<username>","<password>"); 
 
            ps = cnn.prepareStatement( 
             "insert into tab1(col1) select cast(? as 
               decimal(18,0)) from tab1" 
             ); 
            ps.setLong(1, 6); 
 
            ps.addBatch(); 
            ps.executeBatch(); 
        } 
 
        catch (Exception e) 
        { 
            if (e.getMessage() != null) 
            { 
                System.out.println(e.getMessage()); 
            } 
            e.printStackTrace(); 
        } 
        finally 
        { 
           if (ps != null) 
            { 
                try 
                { 
                    ps.close(); 
                } 
                catch (SQLException e) {} 
            } 
            if (cnn != null) 
            { 
                try 
                { 
                    cnn.close(); 
                } 
                catch (SQLException e) {} 
            } 
        } 
    } 
}

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users of the IBM Data Server Driver for JDBC and SQLJ (also  * 
* called the JCC driver) who connect to DB2 on zOS             * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* .                                                            * 
****************************************************************

To set the value of the statement parameter, call 
setBigDecimal() or setObject() instead of setLong(). 
 
That means, in the example program, replace the line: 
 ps.setLong(1, 6) 
with: 
 ps.setBigDecimal(1, new java.math.BigDecimal(6)); 
or: 
 ps.setObject(1, "6");

DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
 DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
 DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows
 

Problem was first fixed in Version 9 Fix Pack 9

As a workaround, to set the value of the statement parameter, 
call setBigDecimal() or setObject() instead of setLong(). 
 
That means, in the example program, replace the line: 
ps.setLong(1, 6) 
with: 
ps.setBigDecimal(1, new java.math.BigDecimal(6)); 
or: 
ps.setObject(1, "6");

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

14.08.2009
02.02.2011
08.02.2011

9.0.FP9