Db2 Floating-Point and DECFLOAT Types

Binary floating-point: REAL, FLOAT, DOUBLE

Binary floating-point approximates real numbers using a base-2 representation. That is excellent for wide dynamic range, but many everyday decimal fractions (0.10, 0.01) are repeating patterns in binary—so equality checks and money math can surprise you.

REAL

REAL is a single-precision (short) floating-point number occupying 32 bits (4 bytes). Approximate range is about −7.2E+75 to 7.2E+75, with tiny magnitudes near ±5.4E−79 at the extremes of normalized positive/negative small values. Use REAL when storage matters and single precision is enough.

DOUBLE

DOUBLE (double precision) uses 64 bits (8 bytes). The approximate magnitude range is similar in order to REAL’s huge span, but you get more significant bits—better precision for calculations that still accept binary approximation.

FLOAT(n)

FLOAT(n) lets you request precision with an integer n from 1 to 53:

• n = 1–21: single-precision float (4 bytes)—like REAL
• n = 22–53: double-precision float (8 bytes)—like DOUBLE

1
2
3
4
5
6
7
CREATE TABLE SENSOR_READING (
  SENSOR_ID   INTEGER       NOT NULL,
  READING_TS  TIMESTAMP     NOT NULL,
  TEMP_C      REAL,
  PRESSURE    DOUBLE,
  ALT_METRIC  FLOAT(53)
);

DECFLOAT(16) and DECFLOAT(34)

DECFLOAT is decimal floating-point: coefficients are decimal digits, so values that are exact in base 10 stay exact more naturally than in binary float. Db2 provides two precisions:

• DECFLOAT(16): 16-digit coefficient, 9 bytes storage, exponents on the order of 10⁻³⁸³ to 10⁺³⁸⁴ for normal numbers
• DECFLOAT(34): 34-digit coefficient, 17 bytes storage, vastly wider exponent range (roughly 10⁻⁶¹⁴³ to 10⁺⁶¹⁴⁴)

1
2
3
4
5
6
7
8
CREATE TABLE RISK_FACTOR (
  MODEL_ID    INTEGER        NOT NULL,
  FACTOR_16   DECFLOAT(16),
  FACTOR_34   DECFLOAT(34)
);
 
INSERT INTO RISK_FACTOR VALUES
  (1, DECFLOAT('1.234567890123456E+10'), DECFLOAT('1.23E+100'));

Prefer DECFLOAT when you need decimal semantics with exponential range—analytics, scientific decimal data, or interoperability with decimal float APIs—while still remembering it is not a drop-in replacement for fixed-scale DECIMAL money columns.

Decimal floating-point special values

Beyond ordinary numbers, DECFLOAT supports special values defined by decimal floating-point arithmetic. The two beginners meet first are NaN and Infinity.

NaN — Not a Number

NaN represents an undefined or unrepresentable result (for example, certain invalid operations). It is not the same as SQL NULL. A column can be NOT NULL and still store a NaN if the type is DECFLOAT and your operations produce one. Predicates involving NaN do not behave like ordinary numeric comparisons—consult DECFLOAT comparison and COMPARE_DECFLOAT documentation before writing filters.

Infinity

Infinity (positive or negative) represents overflow beyond the normal numeric range in floating-point semantics. Like NaN, it is a special value, not NULL. Display, export, and host-language mappings must understand these tokens or you will see confusing conversions.

1
2
3
4
5
6
Mental model:
 
  NULL        -> "no value / unknown"
  NaN         -> "value exists but is not a number"
  Infinity    -> "value exists but is infinite magnitude"
  1.25E+5     -> ordinary finite DECFLOAT number

When to use which family

• DECIMAL: money and fixed-scale business quantities
• INTEGER/BIGINT: counts and whole identifiers
• REAL/DOUBLE/FLOAT: approximate binary float for engineering-style data where tiny binary rounding is acceptable
• DECFLOAT: decimal float with wide exponents and special values

Mixing families in one expression triggers conversions and can inject rounding. Keep columns in one numeric story when possible, and CAST explicitly at boundaries.

Host languages and application notes

COBOL historically maps binary floats to COMP-1 (single) and COMP-2 (double). DECFLOAT support depends on compiler and Db2 version features—confirm your shop’s approved host types before designing DECFLOAT-heavy COBOL. Java and other DDF clients often have dedicated decimal-float or BigDecimal pathways; do not silently shove DECFLOAT into binary double without accepting precision loss.

Explain It Like I'm Five

Floating-point is a number written like “three times ten to the power of something”—a short note that can describe very tiny or very huge amounts without using a thousand digit stickers. Binary float (REAL/DOUBLE) writes that note in computer twos; DECFLOAT writes it with ordinary decimal digits. Sometimes the note says “Infinity” (too huge) or “NaN” (that is not a real number). Those sticky notes are different from an empty box (NULL). For lunch money, use DECIMAL jars with fixed cents—not float notes.

Exercises

1. Explain what FLOAT(10) and FLOAT(53) each mean for storage size.
2. List two differences between DECFLOAT(16) and DOUBLE.
3. Why is NaN not the same as NULL? Give a one-sentence example scenario for each.
4. Pick DECIMAL, DOUBLE, or DECFLOAT for: (a) checking account balance, (b) satellite sensor voltage with wide range, (c) a decimal scientific coefficient needing special values.
5. Write a CREATE TABLE with one REAL column and one DECFLOAT(34) column and justify each choice in a comment.