DB2 Data Types – Numeric, String & Datetime

In the DB2 database, while structuring tables, data types must be assigned to each and every field. The DB2 datatypes specify what type of value, what type of functions and operators can be used with a particular field, the range of allowed values and the length of the field can that particular column contains in DB2. The data type assigned can be modified after a table is created and values are inserted in it. In this case, the applied datatype is implemented for all the values of that column at the time of reorganization of the table. There are two types of data types in DB2, one provided by IBM called built-in data types and the other ones are user-defined datatypes called distinct data types.

Built-in Data Types

The datatypes of DB2 can be segregated into the following main categories –

Numeric DB2 Data Types

1. SMALLINT Data types – It is a binary data type integer and stores small integers with a precision of 15 bits. The range of SMALLINT is -32682+32767. It occupies 2 bytes. For a smaller number, we can define as the SMALLINT.
2. INT Data types – Integer occupies 4 bytes in memory and it has 31 precession. The range for this integer is -21474836482 to +2147493647. It occupies 4 bytes. For the bigger number, we can define INTEGER. INTEGER is the synonym of INT, therefore, they are interchangeable.
3. BIGINT Data types – 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
4. DECIMAL Data types – A decimal number is a packed decimal number with an implicit decimal point. The position of the decimal point is determined by the precision and the scale of the number. The scale, which is the number of digits in the fractional part of the number, cannot be negative or greater than the precision. The maximum precision is 31 digits. All values of a decimal column have the same precision and scale. The range of a decimal variable or the numbers in a decimal column is -n to +n, where n is the largest positive number that can be represented with the applicable precision and scale. The maximum range is 1 – 10³¹ to 10³¹ – 1.
5. DECFLOAT Data types – A decimal floating-point value is an IEEE 754r number with a decimal point. The position of the decimal point is stored in each decimal floating-point value. The maximum precision is 34 digits. The range of a decimal floating-point number is either 16 or 34 digits of precision; the exponent range is respectively 10-383 to 10+384 or 10-6143 to 10+6144.
6. REAL (Single Floating Point) Data types – It is also called REAL and the range is -7.2E+75 to 7.2E to 75. It is also used very less it has 32 bits floating number.
7. DOUBLE (Double Floating Point) Data types – Floating number is 64 bits and the range is -7.2E + 75 to + 7.2E + 75.

For integer values, use SMALLINT, INTEGER, or BIGINT (depending on the range of the values). Do not use DECIMAL for integer values.

String DB2 Data Types

1. CHARACTER(n) – Fixed-length character strings with a length of n bytes. n must be greater than 0 and not greater than 255. The default length is 1.
2. VARCHAR(n) – Varying-length character strings with a maximum length of n bytes. n must be greater than 0 and less than a number that depends on the page size of the tablespace. The maximum length is 32704.
3. CLOB(n) Character large objects – Varying-length character strings with a maximum of n characters. n cannot exceed 2,147,483,647. The default length is 1M. Use the CLOB data type to store SBCS or mixed data, such as documents that contain a single character set. Use this data type if your data is larger (or might grow larger) than the VARCHAR data type permits.
4. GRAPHIC(n) – Fixed-length graphic strings that contain n double-byte characters. n must be greater than 0 and less than 128. The default length is 1.
5. VARGRAPHIC(n) – Varying-length graphic strings. The maximum length, n, must be greater than 0 and less than a number that depends on the page size of the tablespace. The maximum length is 16352.
6. DBCLOB(n) Double-byte character large objects – Varying-length strings of double-byte characters with a maximum of n double-byte characters. n cannot exceed 1,073,741,824. The default length is 1M. Use the DBCLOB data type to store large amounts of DBCS data, such as documents that use a DBCS character set.
7. BINARY(n) – Fixed-length or varying-length binary strings with a length of n bytes. n must be greater than 0 and not greater than 255. The default length is 1.
8. VARBINARY(n) – Varying-length binary strings with a length of n bytes. The length of n must be greater than 0 and less than a number that depends on the page size of the tablespace. The maximum length is 32704.
9. BLOB(n) Binary large objects – Varying-length binary strings with a length of n bytes. n cannot exceed 2,147,483,647. The default length is 1M. Use the BLOB data type to store large amounts of noncharacter data, such as pictures, voice, and mixed media.

In most cases, the content of the data that a column is to store dictates the data type that you choose.

Datetime Db2 Data Types

1. Date – The Date is 4 bytes and it has three parts DAY, MONTH, YEAR. (YYYY)A year can occupy a value of 000129999 while MM(momnth0 can occupy a value from 1 to 12 and DD(Day) can occupy 1 to 28 or 1 to 30 or 1 to 31 or 1 to 29, it all depends on the month and year. In memory, it occupies 4 bytes. So the range is 0001-01-01 to 9999-12-31.
2. Time – In time we have three bytes the first byte is (HH)hour, (SEC)second byte is (MM)minute and the third byte is second. So the range is 00.00.00 to 24.00.00.
3. TimeStamp – It contains the year, month, date, hour, minute, second and sometimes it also contains optional fractional seconds, and a few times it also contains a time zone as well. the range of 0001-01-01-00.00.00.000000000 to 9999-12-31-24.00.00.000000000 with nanosecond precision.

There are other DB2 data types as well

eXtensible Mark-up Language (XML): We can store the XML values in the columns by giving it XML datatype. It is possible to store the XML documents that are well structured in the database by using this datatype.

NULL values – It is a special type of value that is supported by all the datatypes when we want to store nothing that means an absence of NONNULL value in the column.

ROWID Data Types – You use the ROWID data type to uniquely identify rows in a Db2 subsystem. Db2 can generate a value for the column when a row is added, depending on the option that you choose (GENERATED ALWAYS or GENERATED BY DEFAULT) when you define the column. You can use a ROWID column in a table for several reasons.

1. You can define a ROWID column to include LOB data in a table.
2. You can use direct-row access so that Db2 accesses a row directly through the ROWID column. If an application selects a row from a table that contains a ROWID column, the row ID value implicitly contains the location of the row. If you use that row ID value in the search condition of subsequent SELECT statements, Db2 might be able to navigate directly to the row.

Boolean Data Types – Using this data type, we can store the values true or false which are internally stored as 1 and 0 in the field. Note that this data type does not fall in the string, DateTime, or numeric data type categories.

User-defined/Distinct Data Types

A distinct type is a user-defined data type that is based on existing built-in Db2 data types. A distinct type is internally the same as a built-in data type, but Db2 treats them as a separate and incompatible type for semantic purposes. Defining your own distinct type ensures that only functions that are explicitly defined on a distinct type can be applied to its instances. 

e.g. CREATE DISTINCT TYPE US_DOLLAR AS DECIMAL (9,2);

CREATE TABLE US_SALES
  (PRODUCT_ITEM_NO INTEGER,
   MONTH           INTEGER,
   YEAR            INTEGER,
   TOTAL_AMOUNT    US_DOLLAR);
CREATE TABLE CANADIAN_SALES
  (PRODUCT_ITEM_NO INTEGER,
   MONTH           INTEGER,
   YEAR            INTEGER,
   TOTAL_AMOUNT    CANADIAN_DOLLAR);

Summary

DB2 RDBMS comes with a wide range of data types that can be assigned to the columns of the table while table creation and can also be modified later. These datatypes help in determining the behavior and characteristics of that column that include range, type of values stored in it, operators and function supported by that field, length, and many other things. The two main categories in which these data types are divided are the built-in datatypes and the user-defined datatypes. Built-in datatypes are the ones provided by IBM in DB2.

Read DB2 blogs: Click Here IBM DB2 Manual: Click Here