Along with other new features, Raima is introducing new data types in RDM 12.0. One of these new data types is the fixed-point decimal type. Most developers are familiar with the integer and floating-point types available in any modern programming language and these have a wide variety of uses, but there are certain situations where these are not ideal, such as monetary values, where exact values are required in all decimal digits.
In addition, some environments may not have floating point hardware and software implementations may be too costly performance-wise. RDM already has support for regular integer (8, 16, 32, and 64-bits long) as well as floating-point (32 and 64-bits long) types and the addition of the new decimal fixed-point type rounds out the ability to store numbers in the database.
RDM’s new decimal type is implemented as a BCD (Binary-Coded Decimal), where each digit is stored in the database in 4-bits, meaning that one byte of storage in the database can hold two digits, but the first byte of the field contains the sign flag in the first 4 bits, followed by the first digit in the second 4 bits. When stored in the database, this requires a number of bytes equal to the precision (the maximum number of decimal digits) divided by two (rounded down), plus one more. Thus a decimal value with precision 10 (up to 10 total digits) requires 6 bytes ((10/2) + 1 = 6). This format is the packed version of the decimal value which is optimal for storage in the database.
Unfortunately, this packed format is not ideal for use in arithmetic calculation. When doing calculations it is much easier to deal with each decimal digit separately, and that is not very convenient when two digits are in the same byte. Thus we have an unpacked version of the decimal type. In this format each digit uses a full byte. In addition, because parts of the calculation can exceed the database’s precision or scale (scale is the number of digits specified to the right of the decimal point), we make sure that there is sufficient space for the maximum precision and scale. Thus this format trades space for optimized computation time.
The new decimal data type in RDM combines the two formats. What is stored in the database is the packed version. What is expected from the application on insert or update and what is returned to the application on a read is the unpacked type. The RDM system takes care of converting between the two. In addition there is an API (functions starting with ‘bcd_’) for dealing with these decimal values. This includes functions for adding, subtracting, multiplying, and dividing decimal values along with functions to change sign, round, and do the floor and ceiling operations. And of course there are functions to convert to and from string and other number types.
However, some applications will not need the full unpacked type, such as when no calculations are going to be done on the values retrieved from the database. In this case, an alternate decimal type is available – the packed decimal type. As its name indicates, the data that the application will give to, and get from, the RDM system will already be in its packed format. This type can be used to avoid the cost of having the RDM system pack the decimal type on input and unpack it on output.
Note that the availability of two different decimal types only applies to Core level databases. SQL only handles the unpacked version, but this is because SQL is a general purpose language where an ad hoc query may need to do operations on the values retrieved from the database and the unpacked format is the one that simplifies this. This also means that if you plan to use SQL you should only select the unpacked format.
Whichever format you use, the new decimal data type will provide more capability for handling number values in the database than existed before, giving more options to the application developer.
