Numeric types overview in DB2

The numeric type family

Db2 groups numeric storage into several families:

• Binary integers — SMALLINT, INTEGER, BIGINT (exact whole numbers in fixed binary ranges)
• Decimal — DECIMAL / NUMERIC (exact fractional values with precision and scale; packed decimal storage)
• Binary floating-point — REAL, FLOAT, DOUBLE (approximate binary fractions)
• Decimal floating-point — DECFLOAT(16), DECFLOAT(34) (decimal float with huge ranges and special values like NaN)

Rule of thumb for beginners: use INTEGER or BIGINT for whole counts and keys; use DECIMAL for money and exact fractions; use floating types only when approximation is acceptable. SMALLINT saves space for small codes and flags when the range is guaranteed.

Precision

Precision is the total number of digits a decimal value can hold. In DECIMAL(9,2), precision is 9—so at most nine digits altogether. Binary integers do not use DECIMAL-style precision attributes; their “precision” is fixed by the type (SMALLINT is described as a binary integer with 15-bit precision in the manuals).

1
2
3
4
5
-- Precision 9, scale 2 → max like 9999999.99
CREATE TABLE APP.PRICES (
  SKU   CHAR(8) NOT NULL,
  PRICE DECIMAL(9,2) NOT NULL
);

Choosing precision too small causes overflow on insert. Choosing precision far too large wastes storage (packed decimal length grows with precision) and can affect some comparisons and displays. Size columns to real business limits plus a little headroom.

Scale

Scale is the number of digits to the right of the decimal point. In DECIMAL(9,2), scale 2 means cents for currency in many shops. Scale 0 means an integer stored in decimal form. Scale cannot exceed precision.

1
2
VALUES DECIMAL(123.456, 9, 2);  -- becomes 123.46 (rounding rules apply)
VALUES DECIMAL(123.456, 9, 0);  -- whole number decimal

When converting from a higher scale to a lower scale, fractional digits are rounded or truncated per Db2 rules for that operation. Design scale to match the business unit—do not store dollars with scale 0 if you need cents.

Numeric overflow

Numeric overflow occurs when a value cannot be represented in the target or result type. Classic examples:

• Inserting 40000 into a SMALLINT column
• Multiplying two large INTEGERs into an INTEGER result that exceeds 2,147,483,647
• Casting a wide DECIMAL into a narrower DECIMAL(p,s)

1
2
3
4
5
-- Overflow risk: SMALLINT max 32767
INSERT INTO APP.FLAGS (CODE) VALUES (40000);  -- fails if CODE is SMALLINT
 
-- Safer intermediate typing
SELECT CAST(A AS BIGINT) * CAST(B AS BIGINT) FROM APP.NUMS;

Unlike some programming languages that wrap on overflow, Db2 SQL typically raises an error. That protects data integrity. Watch intermediate results in expressions—not only the final column type.

Numeric conversion

Numeric conversion changes a value from one numeric type to another during assignment, CAST, or expression evaluation. Conversion may:

• Preserve the exact value (INTEGER 5 → DECIMAL(9,2) 5.00)
• Round or truncate fractional parts when scale shrinks
• Approximate when moving to binary floating-point
• Fail with overflow when magnitude does not fit

1
2
3
4
SELECT CAST(SMALLINT_COL AS INTEGER),
       CAST(INT_COL AS DECIMAL(11,2)),
       CAST(DEC_COL AS DOUBLE)
  FROM APP.SAMPLE;

String↔numeric conversion is a related topic on the casting page. Prefer CAST when reading external character files into numeric columns so bad tokens fail clearly.

Numeric promotion

Numeric promotion follows the precedence list used in function resolution and related contexts: SMALLINT toward INTEGER, BIGINT, decimal, real, double, DECFLOAT; and similarly for other starting types. Promotion helps Db2 pick a function overload or treat arguments as a common wider type.

In mixed arithmetic, result types follow detailed operation rules in the SQL Reference (not always “whatever you hoped”). When exact scale matters, CAST operands before calculating.

Choosing a type (starter guide)

• Status codes 1–100 — SMALLINT or even CHAR if they are truly codes
• Row counters, ordinary IDs — INTEGER or BIGINT
• Money, rates, exact fractions — DECIMAL with appropriate p,s
• Scientific sensor data — DOUBLE / DECFLOAT as appropriate

The next pages walk each major type with ranges, host-variable mapping, and overflow notes—starting with SMALLINT.

Explain It Like I'm Five

Numeric types are different sized jars for numbers. A tiny jar (SMALLINT) only holds small counts. A bigger jar (INTEGER/BIGINT) holds huge whole counts. A measuring jar with marks for pennies (DECIMAL) holds exact money. A fuzzy science jar (FLOAT) holds “about this many.” Precision is how many digit stickers fit on the jar. Scale is how many stickers sit after the decimal point. Overflow is trying to pour too much into a small jar—Db2 spills and complains instead of secretly overflowing onto the floor.

Exercises

1. For DECIMAL(7,2), what is precision and what is scale? What is the largest positive value shape?
2. Pick a type for storing US dollar amounts with cents and justify it.
3. Why might SALARY * 100 overflow INTEGER even if SALARY alone fits?
4. Name two binary integer types and their approximate byte sizes.
5. Explain one difference between DECIMAL and DOUBLE for storing 0.10.