Ambiguos character reading db2 char field from jdbc

Question

I'm working in a new java app that works against a legacy database in db2 that cannot be altered. In one of the tables there is char(32) column that contains comp3 characters.

We read this column using the ibm db2 jdbc type 4 driver. When we examine the bytes to unpack the characters we find that whenever in the bbdd is a byte with the value 25, the jdbc driver recovers it as the value 15. A byte with a value 15 is also recovered as a 15 value, so we have an ambiguous value that we don't know how to solve.

Reading in the ibm documentation it seems that reading strings transforms them from ebcdic to ascii/unicode and this seems to be the root of the problem.

¿ It's there a way to recover the real bytes of the field without this transformation ?

Please note that resultSet.getBinaryStream() isn't working since is a char column and we cannot alter the database in any way. We can only affect to the jdbc connection and the java code.

As part of the query, perhaps try to CAST the char(32) column to some compatible type to be able to use getBinaryStream(). — Andrew S
COMP-3 maps to the SQL DECIMAL data type, which in turn should be automatically converted by the JDBC driver to java.math.BigDecimal -- simply use resultSet.getBigDecimal() — mustaccio

Charles Charles · Accepted Answer · 2017-05-30T16:49:17

COMP-3 is a COBOL term for packed numeric.

Quick lesson on numeric values on the IBM mid-range or mainframe systems. Fix Decimal numbers are described with a (precision, scale). So a (5,2) number has a max value of 999.99.

Given the value: 012.34
Zoned decimal (5,2) would be x'F0F1F2F3F4' (5 bytes)
Packed decimal (5,2) would be x'01234F' (3 bytes)

Given the value: 012.34-
Zoned decimal (5,2) would be x'F0F1F2F3D4'
Packed decimal (5,2) would be x'01234D'

Also note the string '01234' would be x'F0F1F2F3F4'

It doesn't make sense for those to be stored in a character column in the DB.

Ideally, you should fix your DB so that numeric values are stored in numeric columns. Next best, would be to get a view defined that properly extracts the data into it's component columns.

If you were dealing with a positive zoned decimal embedded in a string, it's not to big a deal to convert with CAST(). Negative values are a little harder

Packed decimal is another story. Probably easiest to do in your Java app, a quick google turns up some possible solutions.

Do it in SQL requires the use of the HEX() function to return a string you can then deal with. You mention the column is 32 characters, lets assume first 29 characters are a valid string and the last 3 characters contain a packed (5,2) number...

select substr(mycol,1,29) as string_value 
      dec(
         dec(substr(hex(substr(mycol,30,3)),1,3)
             concat '.' concat
             substr(hex(substr(mycol,30,3)),4,2),
         5,2) *
         (case when substr(hex(substr(mycol,30,3)),6,1) = 'D'
                 then -1 else 1 end),
       5,2) as numeric_value
  from mytable

Ambiguos character reading db2 char field from jdbc

2 Answers