Precision n Scale in Real and Double


  I am hoping someone can explain to me the real and Double/Float datatypes in terms of precision and scale. Precision is defined as no of total digits in the number and scale as the total number of digits on the right of decimal point.

Does Real (defined as a single precision floating point number in DB2 docs) only have a single digit on the left hand side of decimal?

Does Double/Float (defined as a double precision floating point number) have only 2 digits to the left of decimal?

Who is Participating?
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Hi Lucky,

Double, Float, and Real all perform similar mathematics, it's just that the underlying data type is different.  Double and Float deal with binary objects (probably using IEEE754 compliant hardware.  Check the spec.  Even if your hardware different it will give you a great insight into these data types.)  Real used a form of packed or BCD data.

What these data types do is give you a fixed number of digits of precision (according to the datatype), but allow you to move these digits relative to the decimal point.  That's a bit confusing, huh?

Arguably, an integer is 32 bits.  The maximum value is about 2 billion, which is 10 digits.  Floating point (real) data types let you move the decimal point with respect to those ten digits.  Even many dozens of places away.

The maximum value for a float is about 7.2E+75.  That's 7 followed by 75 zeroes.

Adding .00000000000000000000000000000000000001 to 10 doesn't make much sense.  The precision of the floating point arithmetic is such that the result will be rounded to 10.

But adding .000000000000000000000000000000000001 to .000000000000000000000000000000000005 can be.  The result is .000000000000000000000000000000000006.  Put in mathematician's terms, the value increased by 20%.

You can define the number of digits to the left AND right of the decimal place.  Just keep in mind that DB2 (and any other engine) is limited in the number of digits for which the mathematics remains precise.

Good Luck,
LuckyLucksAuthor Commented:
Thx, so just trying to understand some of the issues you explained

I have posed some examples below, kindly check those interpretations.
real(5) - see float(5) below
real(5,2) - see float(5,2) below
float(5) - We can assign valid values as 123.45 as well as 12.345. Invalid values are 12.3456 and 123.456, correct?
float(5,2) - We can assign valid values as 123.45. Invalid values are 123.456, correct?

double(5) - We can assign valid values as 12345.0 or 123.45 or 12.345? (total of 5 digits, no bar on the number of digits to right of decimal as long as total digits dont exceed 5?)
double(5,3) - We can assign valid values as 12.345 or 123.45, correct? (total of 5 digits and a max of 3 to right of decimal)
Kent OlsenData Warehouse Architect / DBACommented:
Hi Lucky,

You're close.

float and double use the native hardware and can not be size delimited.  A float is a single-precision binary value and a double is a double-precision binary value.

If you want to define the size, you'll have to use DECIMAL.  Keep in mind that a DECIMAL (5,3) indicates that the value is 5 total digits in length, the 3 rightmost are to the right of the decimal point.  DB2 doesn't store the decimal point, just the digit so it is the column definition that indicates where the decimal point goes.

For a DECIMAL (5,3), an internal value of 12345 is a user value of 12.345.  00001 is 00.001.  You can't shift the decimal point when it's convenient.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.