{"id":530,"date":"2019-06-02T00:00:14","date_gmt":"2019-06-02T08:00:14","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=530"},"modified":"2020-04-11T06:59:06","modified_gmt":"2020-04-11T14:59:06","slug":"db2-integer","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-integer\/","title":{"rendered":"Db2 Integers"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about various Db2 integer data types including SMALLINT<\/code>, INT<\/code>, and BIGINT<\/code>.<\/p>\n\n\n\n

Introduction to Db2 integer types<\/h2>\n\n\n\n

Db2 supports three types of integers: SMALLINT<\/code>, INT<\/code>, and BIGINT<\/code>.<\/p>\n\n\n\n

SMALLINT<\/h3>\n\n\n\n

SMALLINT<\/code> is used to stores small integers with a precision of 15 bits. The range of SMALLINT<\/code> is -32,768 to +32,767.<\/p>\n\n\n\n

INT<\/h3>\n\n\n\n

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

BIGINT<\/h3>\n\n\n\n

BIGINT<\/code> 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.<\/p>\n\n\n\n

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<\/code> is sufficient.<\/p>\n\n\n\n

Db2 integer example<\/h2>\n\n\n\n

First, create a table<\/a> named db2_integers<\/code> that consists of some integer columns:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> db2_integers(\n    smallint_col SMALLINT<\/span>,\n    int_col INT<\/span>,\n    bigint_col BIGINT<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> db2_integers (\n    smallint_col, \n    int_col, \n    bigint_col )\nVALUES<\/span> (\n    32767<\/span>,\n    2147483647<\/span>,\n    9223372036854775807<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, query data from the db2_integers<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> db2_integers;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"Db2<\/figure>\n\n\n\n
Fourth, try to insert integers that exceed the range of the integer columns:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> db2_integers (\n    smallint_col, \n    int_col, \n    bigint_col )\nVALUES<\/span> (\n    32768<\/span>,\n    2147483648<\/span>,\n    9223372036854775808<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Db2 issued the following error message:<\/p>\n\n\n
SQL0413N  Overflow occurred during numeric data type conversion.\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, attempt to insert decimal numbers instead of integers into the integer columns:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> db2_integers (\n    smallint_col, \n    int_col, \n    bigint_col )\nVALUES<\/span> (\n    32766.6<\/span>,\n    2147483646.6<\/span>,\n    9223372036854775806.6<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Db2 implicitly converted the decimal numbers to integer numbers before insert:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> db2_integers;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output is:<\/p>\n\n\n\n
\"Db2<\/figure>\n\n\n\n
In this tutorial, you have learned how to use various types of Db2 integers including SMALLINT<\/code>, INT<\/code>, and BIGINT<\/code>.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Second, insert<\/a> some values into the table:<\/p>\n\n\n