By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,784 Members | 2,933 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,784 IT Pros & Developers. It's quick & easy.

Solution: DB2 JDBC addBatch truncates DECIMAL fields during inserts, if the last row has null values

P: n/a
This is the solution to the issue in the subject, question itself was
posted in a previous thread long back.

Use the latest DB2 db2cc.jar fixpack on the client, or add
"deferPrepares=false" to the connection string when opening the
connection (or connection pool properties, if deployed on a server like
weblogic). The issue can be resolved using either of these options.

Gopal
On Dec 21 2005, 11:18 am, bunty.go...@gmail.com wrote:
I am trying to use the PreparedStatement's addBatch to load data in an
ETL-like process into a table. There is a single prepared statement.
The parameters are set on it and prepStmt.addBatch() is called. Then
the next iteration of setting the params and adding to batch begins.
Finally after setting the batch of 2 to 2000 rows, the
prepStmt.executeBatch() is called.

If the last row contains a null value for a particular DECIMAL field,
then the value for that field in all the rows is stored truncated to
their integer value in the table. Tried using different methods of
setting the null - setNull, setObject, setBigDecimal etc, but still the
value is truncated. If the DECIMAL fields in the last row are not null,
then this truncation does not happen. Note that it only happens for
those DECIMAL columns, that have a null value in the last row.

E.g.:
statement:

INSERT INTO PAY (EMP_ID, AMT_PAID) VALUES (?, ?);EMP_ID is INTEGER
AMT_PAID is DECIMAL(12,4)

If the parameters were set as follows, in the same order:

EMP_ID, AMT_PAID
----------------
1001, 3414.5512
1002, null
1003, 3151.6633

Then there is no truncation.

But if the parameters were set in this order:

EMP_ID, AMT_PAID
----------------
1001, 3414.55
121003, 3151.6633
1002, null

Then, the values of AMT_PAID get truncated in all rows. i.e. in DB, it
appears as:

EMP_ID, AMT_PAID
----------------
1001, 3414.0000
1003, 3151.0000
1002, null

All decimal values are stored using setBigDecimal. Integer values are
stored using setInt. All methods of setting the null were tried -
setNull, setObject, setBigDecimal etc. but with same error. I debugged,
and found that until the executeBatch is called, the value contained in
PS is proper, but the value appears truncated in DB. :(

Is there something wrong being done? Is this a known issue? Any
workarounds or patches?

The JDBC driver files are from the db2_jdbc_t4.zip available from IBM's
website. DB2 version: DB2/6000 8.1.6.

Thanks,
Gopal
Dec 23 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
test
<bu*********@gmail.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...
This is the solution to the issue in the subject, question itself was
posted in a previous thread long back.

Use the latest DB2 db2cc.jar fixpack on the client, or add
"deferPrepares=false" to the connection string when opening the
connection (or connection pool properties, if deployed on a server like
weblogic). The issue can be resolved using either of these options.

Gopal
On Dec 21 2005, 11:18 am, bunty.go...@gmail.com wrote:
>I am trying to use the PreparedStatement's addBatch to load data in an
ETL-like process into a table. There is a single prepared statement.
The parameters are set on it and prepStmt.addBatch() is called. Then
the next iteration of setting the params and adding to batch begins.
Finally after setting the batch of 2 to 2000 rows, the
prepStmt.executeBatch() is called.

If the last row contains a null value for a particular DECIMAL field,
then the value for that field in all the rows is stored truncated to
their integer value in the table. Tried using different methods of
setting the null - setNull, setObject, setBigDecimal etc, but still the
value is truncated. If the DECIMAL fields in the last row are not null,
then this truncation does not happen. Note that it only happens for
those DECIMAL columns, that have a null value in the last row.

E.g.:
statement:

INSERT INTO PAY (EMP_ID, AMT_PAID) VALUES (?, ?);EMP_ID is INTEGER
AMT_PAID is DECIMAL(12,4)

If the parameters were set as follows, in the same order:

EMP_ID, AMT_PAID
----------------
1001, 3414.5512
1002, null
1003, 3151.6633

Then there is no truncation.

But if the parameters were set in this order:

EMP_ID, AMT_PAID
----------------
1001, 3414.55
121003, 3151.6633
1002, null

Then, the values of AMT_PAID get truncated in all rows. i.e. in DB, it
appears as:

EMP_ID, AMT_PAID
----------------
1001, 3414.0000
1003, 3151.0000
1002, null

All decimal values are stored using setBigDecimal. Integer values are
stored using setInt. All methods of setting the null were tried -
setNull, setObject, setBigDecimal etc. but with same error. I debugged,
and found that until the executeBatch is called, the value contained in
PS is proper, but the value appears truncated in DB. :(

Is there something wrong being done? Is this a known issue? Any
workarounds or patches?

The JDBC driver files are from the db2_jdbc_t4.zip available from IBM's
website. DB2 version: DB2/6000 8.1.6.

Thanks,
Gopal

Jan 31 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.