Db2 Decimal

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:

NUMERIC(p,s)
DECIMAL(p,s)
DEC(p,s)
Code language: SQL (Structured Query Language) (sql)

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:

NUMERIC(5,3)
DECIMAL(5,3)
DEC(5,3)
Code language: SQL (Structured Query Language) (sql)

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:

SQL0413N  Overflow occurred during numeric data type conversion.
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned about Db2 decimal type and how to use it to store decimal numbers in tables.