Db2 Integers

Summary: in this tutorial, you will learn about various Db2 integer data types including SMALLINT, INT, and BIGINT.

Introduction to Db2 integer types

Db2 supports three types of integers: SMALLINT, INT, and BIGINT.

SMALLINT

SMALLINT is used to stores small integers with a precision of 15 bits. The range of SMALLINT is -32,768 to +32,767.

INT

INT is used to store large integers with a precision of 31 bits. The range of INT is -2,147,483,648 to +2,147,483,647. INTEGER is the synonym of INT, therefore, they are interchangeable.

BIGINT

BIGINT is used to store big integers with a precision of 63 bits. The range of big integers is -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807.

When you design database tables, it is a good practice to choose the appropriate integer types to save the space. For example, if you just need to store the number of pages of a books, the SMALLINT is sufficient.

Db2 integer example

First, create a table named db2_integers that consists of some integer columns:

CREATE TABLE db2_integers(
    smallint_col SMALLINT,
    int_col INT,
    bigint_col BIGINT
);
Code language: SQL (Structured Query Language) (sql)

Second, insert some values into the table:

INSERT INTO db2_integers (
    smallint_col, 
    int_col, 
    bigint_col )
VALUES (
    32767,
    2147483647,
    9223372036854775807
);
Code language: SQL (Structured Query Language) (sql)

Third, query data from the db2_integers table:

SELECT * FROM db2_integers;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Fourth, try to insert integers that exceed the range of the integer columns:

INSERT INTO db2_integers (
    smallint_col, 
    int_col, 
    bigint_col )
VALUES (
    32768,
    2147483648,
    9223372036854775808
);
Code language: SQL (Structured Query Language) (sql)

Db2 issued the following error message:

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

Fifth, attempt to insert decimal numbers instead of integers into the integer columns:

INSERT INTO db2_integers (
    smallint_col, 
    int_col, 
    bigint_col )
VALUES (
    32766.6,
    2147483646.6,
    9223372036854775806.6
);
Code language: SQL (Structured Query Language) (sql)

Db2 implicitly converted the decimal numbers to integer numbers before insert:

SELECT * FROM db2_integers;
Code language: SQL (Structured Query Language) (sql)

The output is:

In this tutorial, you have learned how to use various types of Db2 integers including SMALLINT, INT, and BIGINT.