DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 Basics » Db2 Decimal

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.

  • Was this tutorial helpful ?
  • YesNo
Previous Db2 Integers
Next Db2 CHAR

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.