Db2 DECIMAL and NUMERIC Types

DECIMAL and NUMERIC

A decimal number in Db2 is a packed decimal value with an implied decimal point. You declare:

1
2
DECIMAL(precision, scale)
NUMERIC(precision, scale)  -- synonym

• Precision (p): total number of digits; maximum 31
• Scale (s): digits to the right of the decimal point; 0 ≤ s ≤ p

Every value in a DECIMAL column shares the same precision and scale. The range is −n to +n where n is the largest number representable with that p and s (overall max about 10³¹−1 at full precision).

Precision and scale in practice

For DECIMAL(9,2), think “nine digit slots, two of them after the point.” The integer part can use up to 7 digits. Inserting 12345678.90 fails if it needs 8 integer digits plus 2 scale digits (10 total) against precision 9.

1
2
3
4
5
6
7
8
9
CREATE TABLE ACCOUNT (
  ACCT_ID     CHAR(10)       NOT NULL,
  BALANCE     DECIMAL(11,2)  NOT NULL,
  RATE        DECIMAL(5,4),
  PRIMARY KEY (ACCT_ID)
);
 
INSERT INTO ACCOUNT (ACCT_ID, BALANCE, RATE)
VALUES ('0001234567', 1250.50, 0.0325);

Choosing precision and scale

• Money in major currency units: often scale 2 (cents)
• Interest rates: scale 4–6 depending on policy
• Leave headroom in precision for growth and intermediate totals
• Match report and interface contracts—changing scale later is painful

Packed decimal mental model

Packed decimal stores digits in nibbles (half-bytes). Two decimal digits fit in one byte, with a sign nibble in the final byte. Approximate storage size is INTEGER(p/2)+1 bytes for precision p. You rarely hand-pack these bytes in application code—Db2 and COBOL COMP-3 handle the layout—but the model explains why DECIMAL(5,2) and DECIMAL(15,2) cost different amounts of space.

1
2
3
4
5
6
DECIMAL(5,2) value 123.45 (conceptual packing):
 
  digits: 1 2 3 4 5  + sign nibble
  bytes:  roughly 3 bytes on disk (INTEGER(5/2)+1)
 
Scale=2 means "decimal point sits before the last two digits."

Because the scale is part of the type, 123.45 and 123.450 are not different “precisions” inside a DECIMAL(5,2) column—the column always carries two fractional digits of scale.

DECIMAL vs integers and floating-point

• Vs INTEGER/BIGINT: use integers for whole counts; use DECIMAL when you need a fractional scale. Do not fake integers with DECIMAL(9,0) unless you have a strong compatibility reason.
• Vs REAL/DOUBLE: binary floating-point is approximate in base 2. DECIMAL is exact in decimal digits for values that fit precision/scale—preferred for money.
• Vs DECFLOAT: DECFLOAT is decimal floating-point with exponent and special values (NaN, Infinity). DECIMAL is fixed-point with a declared scale.

Arithmetic, overflow, and rounding

Decimal arithmetic follows Db2 rules for result precision and scale. Results that need more digits than the target column allows cause overflow or require casting. Division and some operations introduce rounding to the result scale. When assigning expression results into DECIMAL columns, confirm the target can hold worst-case magnitudes.

1
2
3
4
5
6
7
UPDATE ACCOUNT
   SET BALANCE = BALANCE + DECIMAL('10.00', 11, 2)
 WHERE ACCT_ID = '0001234567';
 
-- Explicit cast documents precision/scale for literals and expressions
SELECT CAST(BALANCE * RATE AS DECIMAL(13,4)) AS INTEREST_EST
  FROM ACCOUNT;

COBOL COMP-3 mapping

COBOL’s packed decimal USAGE COMP-3 is the natural twin of Db2 DECIMAL. Match digit counts carefully:

1
2
3
4
5
6
7
8
9
10
      *  Db2 DECIMAL(11,2) -> 9 integer digits + 2 decimal digits
       01  WS-BALANCE          PIC S9(9)V99 USAGE COMP-3.
       01  WS-RATE             PIC S9(1)V9(4) USAGE COMP-3.
 
       EXEC SQL
            SELECT BALANCE, RATE
              INTO :WS-BALANCE, :WS-RATE
              FROM ACCOUNT
             WHERE ACCT_ID = :WS-ACCT-ID
       END-EXEC.

• Picture integer digits + fractional digits should align with precision and scale
• Sign (S) should match signed DECIMAL columns
• DISPLAY pictures are for human output—do not SELECT INTO a DISPLAY field unless your standards and conversions are explicit
• Indicator variables are still required for nullable DECIMAL columns

A classic defect is mapping DECIMAL(11,2) to PIC S9(11)V99 COMP-3 (12 digits before considering how V works) or forgetting the V fractional part. Draw the digit budget on paper until it becomes muscle memory.

Explain It Like I'm Five

DECIMAL is a money jar with labeled slots for coins. If the jar says “two coin slots after the point,” every amount keeps two coin digits—like dollars and cents. Precision is how many digit stickers fit on the jar in total. Packed decimal is a clever way of squeezing two digit stickers into each tiny pocket so the jar does not waste space. INTEGER jars refuse coins; floating jars estimate with fuzzy numbers—DECIMAL keeps exact coin counts.

Exercises

1. For DECIMAL(7,2), how many digits are available for the integer part? What is the maximum scale allowed for precision 7?
2. Write CREATE TABLE SQL for an item price DECIMAL(9,2) and a tax rate DECIMAL(4,4).
3. Explain why NUMERIC(9,2) and DECIMAL(9,2) are interchangeable in Db2.
4. Propose a COMP-3 COBOL picture for DECIMAL(9,2) and explain the digit split.
5. Give one reason not to store currency in DOUBLE even if “it looks fine” in a demo.