With DDS, you can create binary fields with nonzero scale in a file; however, most client/server technologies interpret this field as an integer. DDS, a tool to externally define files, predates modern SQL standards, and there are some data types, such as binary, that do not map directly to an SQL type. For maximum portability and usability, it is recommended that these nonstandard types be avoided.

The data type in the DDS source is a B. The DDS Reference lists this type along with numerics, and it does allow the number to have scale (for example, 12.34) like a decimal type. The DDS source to generate a file with one binary field and a character field might look similar to the following:

                R BINARYREC          
                  ALPHACOL       5A  
                  BINARYCOL      5B01

When the source is compiled, it creates entries in the cross-reference tables describing the field. One of the critical mappings is to the field type field (DBITYP) in the QADBIFLD table. This defines the type of column that this field is described as in the relational database. The binary type is mapped to an integer type, integer, or smallint. Integers, of course, do not support nonzero scale values. Because the data type from the cross-reference tables are used to describe the table for ODBC, JDBC, and OLEDB, all of these technologies report this field as an integer rather than a decimal value.

Resolution

Do not use binary fields with nonzero scale on any new files. Or, work around the problem with existing files that contain binary types by creating a view that casts the binary type to a decimal type. You need to define this view using SQL because DDS does not support casting the binary type to a decimal type unless the scale is nonzero (no decimal digits). For example, a table is created by compiling the DDS shown previously named MYTABLE in the MYLIB library. You create the view, BINARYVIEW, by selecting all of the other fields by name and rather than selecting BINARYCOL, select a function on the field that would map it to a decimal type similar to the following.

CREATE VIEW MIKSWENS.BINARYVIEW AS SELECT ALPHACOL, ZONED(BINARYCOL, 5, 1) AS BINARYCOL FROM MIKSWENS.BINARYFILE

This view makes the BINARYCOL appear as a decimal field.