News, views, and items of interest on IBM's Db2 database management system and mainframes.

Monday, February 16, 2015

Choosing Between DECIMAL and FLOAT Data Types

DB2 can use both DECIMAL and FLOAT data types to store
non-integer numeric data. But the two are not
equivalent. In general, use DECIMAL instead of FLOAT whenever you can. The main
problem with floating point numbers is that they are not precise. DECIMAL
values are precise. In other words, a FLOAT value will be an approximate value
whereas a DECIMAL value will be an exact value.

At times, if 100 percent precision is not required, you might want
to use floating point numbers to save on storage. DB2 provides a parameter to
size the floating point column; n where the data type
specification is FLOAT(n). If n is between 1 and 21, this is a single precision
floating point number and the column will require 4 bytes of storage; if n is
between 22 and 53, this is a double precision floating point number and it will
require 8 bytes to store it. A single precision floating-point number is a short (32 bits) floating-point number. A double precision
floating-point number is a long (64 bits) floating-point number.

For DECIMAL columns, the byte count is calculated as
INTEGER(p/2)+1; where p is the precision of the DECIMAL column. So, a
DECIMAL(10,2) column will require (10/2)+1 bytes = 6 bytes. An approximation of
the same number could be stored in a FLOAT(21) column that would require only 4
bytes of storage.

For very large or very small numbers, though, you will have
to use FLOAT columns. This is so because there is a limit of 31 on decimal
precision. The following outlines the largest and smallest values that can be
supported using DECIMAL and FLOAT data types:

Smallest FLOAT value
is about -7.2**75

Largest FLOAT value
is about 7.2**75

Smallest positive
FLOAT value is about 5.4**-79

Largest negative
FLOAT value is about -5.4**-79

Smallest DECIMAL
value is 1 – 10**31

Largest DECIMAL
value is 10**31 - 1

Note that the values for floating point numbers are
approximations. The ** is used to indicate “raised to the power of”.

If you are moving the between platforms, there is an
additional concern when using FLOAT. Mainframes use an IBM standard whereas
other platforms use different standards. Since floating point numbers are
imprecise to begin with this may not be a problem. However, if you want to make
sure that a particular column will be exactly the same value regardless of
platform, then floating point is not the way to go.

There is a third, newer option called DECFLOAT. Introduced in DB2 9 for z/OS, DECFLOAT is a combination of the two data types discussed here, or a decimal floating-point data type. Specified as DECXFLOAT(n), where the value of n can be either 16 or or 34, representing the number of significant digits that can be stored. If the n is not specified, then the DECFLOAT column can represent 34 significant digits.

A decimal floating-point value is an IEEE 754r number with a decimal point. The maximum precision is 34 digits and the range of a DECFLOAT number is as follows:

A DECFLOAT(16) value can range from a low of:

-9.999999999999999×10**384

to a high of:

9.999999999999999×10**384
And a DECFLOAT(34) value can range from a low of:

-9.999999999999999999999999999999999 ×10**6144

to a high of:

9.999999999999999999999999999999999 ×10**6144

In addition, the DECFLOAT data type can be used to represent several special values that represent "non-number numbers," as follows:

Infinity - a value that represents a number whose magnitude is infinitely large.

Quiet NaN - a value that represents undefined results which does not cause an invalid number condition. NaN is not a number.

Signaling NaN - a value that represents undefined results which will cause an invalid number condition if used in any numerical operation.

So decimal-floating point values can be more flexible and precise with the ability to range lower and higher than floating point values (or decimal values). However, before using DECFLOAT be careful and plan ahead. If you use COBOL programs to operate on your DB2 for z/OS data because there is no way to specify the SQL DECFLOAT data type in COBOL.