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:
CREATE TABLE TESTTAB(A DECIMAL(2, 4))
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
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