Summary: in this tutorial, you’ll learn about Db2 decimal type and how to use it to store decimal numbers in tables.
Introduction to Db2 decimal type
A decimal number consists of two parts: the whole part and the fractional part. The whole part and fractional part is separated by a decimal point (.)
In Db2, you declare a decimal number using one of the following forms:
Code language: SQL (Structured Query Language) (sql)NUMERIC(p,s) DECIMAL(p,s) DEC(p,s)
In these syntaxes:
- p is the precision which is the maximum number of decimal digits including both whole part and fractional part. e.g., 12.345 has a maximum precision of 5.
- s is called scale which is the number of decimal digits in the fractional part e.g., for the number 12.345, s is 3
So to store the number like 12.345 you need to declare the column that has one of the following syntaxes:
Code language: SQL (Structured Query Language) (sql)NUMERIC(5,3) DECIMAL(5,3) DEC(5,3)
The maximum precision of a decimal in Db2 is 31 digits. And decimal type has the maximum range from 1 – 10³¹ to 10³¹ – 1.
Db2 decimal type example
First, create a table named db2_decimals
that has a decimal column:
CREATE TABLE db2_decimals(
dec_col NUMERIC(5,3)
);
Code language: SQL (Structured Query Language) (sql)
Second, insert a decimal number into the table:
INSERT INTO db2_decimals(dec_col)
VALUES(12.345);
Code language: SQL (Structured Query Language) (sql)
Third, query data from the db2_decimals
table:
SELECT dec_col
FROM db2_decimals;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
DEC_COL
-------
12.345
Code language: SQL (Structured Query Language) (sql)
Fourth, insert another decimal number into the db2_decimals
table:
INSERT INTO db2_decimals(dec_col)
VALUES(12.3456);
Code language: SQL (Structured Query Language) (sql)
Db2 truncated the decimal number before insert. Here are the contents of the table:
DEC_COL
-------
12.345
12.345
Code language: SQL (Structured Query Language) (sql)
Fifth, insert a decimal number that exceeds the range of the decimal column:
INSERT INTO db2_decimals(dec_col)
VALUES(123.456);
Code language: SQL (Structured Query Language) (sql)
Db2 issued the following error:
Code language: SQL (Structured Query Language) (sql)SQL0413N Overflow occurred during numeric data type conversion.
In this tutorial, you have learned about Db2 decimal type and how to use it to store decimal numbers in tables.