DB2 DECIMAL data type

Describe DB2 z/OS DECIMAL data type

Usage Format

DECIMAL(PRECISION, SCALE)

• PRECISION refers to the maximum number of digits that are present in the number, and
  
• SCALE refers to the maximum number of decimal places.

For example: 123456.789 has a PRECISION of 9, and a SCALE of 3.

So the maximum allowed value for decimal(4,2) is 99.99

Default PRECISION is 5, and SCALE is 0, so
"COLNAME DECIMAL == COLNAME DECIMAL(5, 0)"

Notice:

1. The sign(+/-) does not occupy a PRECISION length, because packed decimal number format rule; refer to "Storage Format" section for details.

2. PRECISION must be greater than (or equal to) SCALE; otherwise, DDL definition will failure, for example,
    CREATE TABLE TESTTAB(A DECIMAL(2, 4))

    And if PRECISION is equal to SCALE, the acceptable value cannot greater than 1.

Value Truncate

DECIMAL value could be truncated if only SCALE part is longer, for example

CREATE TABLE TESTTAB(A DECIMAL(4, 2))

1.  INSERT INTO TESTTAB VALUES(12.345); # 12.34 is inserted, the last 5 is truncated.

Otherwise, failure reported.

2.  INSERT INTO TESTTAB VALUES(123.45); # SQL0413N  Overflow occurred during numeric data type conversion

Because 123 is exceed the precision, and it will not be truncated silently.

Storage Format:

DECIMAL data type is saved using packed decimal number, i.e., COMP-3

db2 => SELECT A, HEX(A) HEX FROM TESTTAB

A      HEX  

------ ------

 12.34 01234C

-23.45 02345D

The last hex('C') means a positive value, and hex('D') means a negative value.

BTW, there is no SCALE description in data value itself, that means the value could not be interpreted itself, a COPYBOOK like definition must be provide.

In the case HEX( 01234C) could be interpreted as +1.234, +12.34, +123.4, ...

So a decimal will occupy memory bytes is the integer no less than (PRECISION + 1)/2, the "plus 1" is for SIGN; for example
DECIMAL(4, 2) => (4 + 1)/2 =>2.5 => 3
DECIMAL(5, x) => (5 + 1)/2 =>3 =>3
DECIMAL(6, 2) => (6 + 1)/2 =>3.5 => 4
DECIMAL(7, x) => (7 + 1)/2 =>4 =>4