Precision n Scale in Real and Double

Posted on 2007-07-26
Medium Priority
Last Modified: 2008-02-01

  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?

Question by:LuckyLucks
  • 2
LVL 46

Accepted Solution

Kent Olsen earned 2000 total points
ID: 19578111
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,

Author Comment

ID: 19582110
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)
LVL 46

Expert Comment

by:Kent Olsen
ID: 19583059
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.


Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question