DB2 JDBC Driver Truncates Decimal

0
votes

DB2's JDBC driver appears to be truncating the precision of Numeric/Decimal fields. I have the following field defined in a SALES table:

AMOUNT AS DECIMAL(16,16)

I then do the following insert statement:

INSERT INTO SALES (AMOUNT) VALUES(14.12345678)

If I query using the query facility in DB2 Control Center, I get 14.1234567800000000.

If I do it using Squirrel (a DB client that uses DB2's JDBC driver to connect to the database, I am getting 14.12346. The same happens when I do the same from Java code using Hibernate.

This leads me to believe that the JDBC driver is truncating precision of the result. Is there any way to configure the JDBC driver with options to prevent this? I've seen the the JDBC options for the JDBC driver but none seem to describe one that affects this.

1
hibernatejdbcdb2
A DECIMAL(16,16) is a bit weird, a 14.1234567890123456 doesn't fit (that at minimum a would be a DECIMAL(18,16)). - Mark Rotteveel
Mark and Nayan are right. That value cannot fit in a DECIMAL(16,16) column in DB2. DECIMAL(16,16) has NO digits to the left of the decimal point. As the saying goes, you cant put 10 pounds of ____ into a 5 pound bag. Perhaps you mistyped it here? - WarrenT
yes. I actually misunderstood and thought that precision was before the decimal point and scale after. Our actual DB table had DECIMAL(19,8). After specifying the scale and precision it worked correctly. - jett

1 Answers

1
votes

If the record is getting inserted properly into the database, then while fetching, it is probably formatted/truncated implicitly.

I have encountered similar issue while retrieving records with Toad for Oracle. Then I have manually configured preferences to get it displayed properly, but not familiar with DB2.

You can try configuring it in column definition. @Column(precision=16, scale=8) for BigDecimal field.

precision : The precision for a decimal (exact numeric) column. (Applies only if a decimal column is used.)

scale : The scale for a decimal (exact numeric) column. (Applies only if a decimal column is used.)

Precision is the total number of digits and scale is the number of digits to the right of the decimal point. Try changing the values for both of them, currently it seems inappropriate.