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:

Db2 Integer conversion example

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:

Db2 Integer example

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

Was this tutorial helpful ?