DB2 data types

Each column in the table of DB2 has a datatype associated. The data type affects the column stores and operation and functions we can apply.

Each column has a datatype assigned by the developer as the developer's needs. We mention the data type of column at the time of creation. We can also modify it later. Some datatypes accept parameters that decide the functions or operations we can apply to them. The datatypes those IBM offers are called Built-in data types, and the user-defined data types are called distinct types.

 

Built-in data types which IBM offer are as follows:

1. String Data Types:

DB2 offers support to various types of string data types such as graphic strings, binary strings, and character strings.

A graphic string holds graphic data with fixed length or variable length.

Character strings hold text, and they can be either varying length or fixed length.

Binary strings contain binary bytes strings and can be one of fixed or varying length.

Let's see the datatype and their range:

Data type	Description
CHARACTER(m)	It can hold fixed-length strings, and the length is m bytes. M should range between 0 to 255. 1 is the default value
VARCHAR(m)	It can hold varying length character strings with m bytes at the maximum. M should be between 0 to the page sizes of the tablespace. 32704 is the maximum length it allows.
CLOB(c)	It can hold varying length character strings with the length of c bytes at the maximum.  The limit m cannot exceed 2147483647. 1M is the default length
VARGRAPHIC(m)	Graphic string which is of variable length. Range is 0 to page size of table space.
GRAPHIC(m)	It is a fixed-length graphic string. The range should lie between 0 to 128. 1 is the default length.
BINARY(m)	It is a fixed-length or variable-length string. The range should be 0 to 255. 1 is the default value
VARBINARY(m)	It holds binary strings of variable length. The range is 0 to the page size of table space.
BLOB(m)	It holds variable-length binary strings with the length of m bytes. 1M is the default length.

2. Numeric Data types:

DB2 offers various types of numeric data types with various features.

Numeric columns need less space than strings.

Data type	Description
SMALLINT	It holds small integers. The range is -32768 to +32767
INTEGER or INT	It stores larger integers. It has precision of 31 bits.
BIGINT	It stores a big integer. It has a precision of 63 bits.
DECIMAL or NUMERIC	It stores a packed decimal number with an implicit decimal point. The decimal point is decided by the precision and the scale of the number. The fractional part of the number is scale, and it cannot be negative.
DECFLOAT	This is a decimal floating value which is a IEEE 754r number along with the decimal point. In every decimal floating point value the position of the decimal point is saved. 34digits is the limit of precision. The range lies between 16 to 34 digits of precision
REAL	It is a single-precision floating-point number, a 32-bit short floating-point number. The range is between -7.2E+75 to 7.2E+75. -5.4E-79 is the highest negative value, and 5.4E079 is the smallest positive value
DOUBLE	It is a double precision floating point number which is a 64bits long floating point number. The range lies between -7.2E+75 to 7.2E+75.

If we use integer values, we should use SMALLLINT, BIGINT, or INTEGER data types. Keep in mind not to use DECIMAL for integer numbers.

As an identity column, we can define an exact numeric column. Using the identity column, we can enable db2 to innately create a unique numeric value to be inserted in the table for every row.

3. Date, time, and timestamp data type

By using numeric data types, we can store dates and times by the recommended type is datetime. DATE, TIMESTAMP, and TIME are the datetime data types.

Let's look into the data types:

Datatype	Description
DATE	It is a data type which represents date in three part values i.e day, months and year. The range is 0001-01-01 to 9999-12-31
TIME	It is a data type which represents time of the day in three part values i.e hours, minutes and seconds. The range lies between 00.00.00 to 24.00.00
TIMESTAMP	It is a data type that represents timestamp with seven part values date and time by the year, month, day, hour, minute,secoind and mirosecond. The range lies from 0001-01-01-00.00.00.000000000 to 9999-12-31-24.00.00.000000000 with the precision of nanosecond. Timezone information is also stored by timestamp.

DB2 follows a special internal format value of date time data types.

4. XML data type:

To store XML values, we can use XML Data type for table columns. This data type allows us to store well-formed XML documents in the table.

Following the internal representation, XML data is saved. The UTF-8 encoding scheme is used for character data in the internal representation.

The XML values stored in the table are not an internal representation of the string, and neither are directly comparable to the string values. The XML value that we wish to store can be converted into the serialized string value, which depicts the XML document using the XMLSERIALIZA function. For the string value, we can similarly convert it to an XML document by using the XMLPARSER function.

There is no limit on the size of XML value stored in DB2. However, there is a limit of 2GB on the retrieving value from the XML column.

The validations on the XML documents against the XML Schema are done while INSERT or UPDATE into the XML column. And it is also supported by the XML schema repository.

5. Large object data types:

We can store large object data types to store images, audios, videos, and other files that need space larger than 32KB.

The data type VARCHAR, VARBINARY, and VARBINARY have a storage limit of 32KB.

Character large objects (CLOBs)

We use this data type to save SBCS or mixed data like documents with a single character set.

DOUBLE-byte character large objects (DBCLOBs)

We use this data type to save DBSC or large data like the documents with a DBSC character set.

Binary large objects (BLOBs)

We use this data type to save large amounts of noncharacter data like mixed media and voice pictures.