DB2 Data Types

A data type tells you what kind of data can be saved in a column or in a variable, and how large the value may be. There are two categories of data types in DB2:

• Built-in data types

• User-defined data types

Valid DB2 Built-In Data Types and Their Value Ranges

The built-in data types are provided with DB2. DB2 supports a wide range of data types for your business need. A summary of DB2 built-in data types are shown in Figure 3.1.

Figure 3.1. DB2 built-in data types.

Note

LONG VARCHAR and LONG VARGRAPHIC data types are supported in DB2 for LUW for backward compatibility only. They are being deprecated, which means that these data types will not be supported in the future. Use VARCHAR and VARGRAPHIC instead.

DB2 for iSeries and zSeries supports the ROWID data type. A ROWID data type is one that uniquely identifies a row. A query that uses ROWID navigates directly to the row because the column implicitly contains the location of the row. When a row is inserted into a table, DB2 generates a value for the ROWID column, unless one is supplied. If it is supplied, it must be a value that was previously generated. The value of ROWID cannot be updated and does not change, even after table space reorganizations. There can only be one ROWID column in a table.

There are six numeric data types in DB2. Their precisions and value ranges are listed in Table 3.1.

Table 3.1. DB2 Built-In Numeric Data Types

Data Type	Precision (Digits)	Data Value Range
SMALLINT	5	32,768 to 32,767
INTEGER	10	2,147,483,648 to 2,147,483,647
BIGINT	19	9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
DECIMAL/NUMERIC	LUW, zSeries: 31 iSeries: 63	LUW, zSeries: Any value with 31 digits or less. iSeries: Any value with 63 digits or less.
REAL	LUW, iSeries: 24 zSeries: 21	LUW: Smallest REAL value 3.402E+38 Largest REAL value +3.402E+38 Smallest positive REAL value +1.175E-37 Largest negative REAL value 1.175E-37 iSeries: Smallest REAL value 3.4E+38 Largest REAL value +3.4E+38 Smallest positive REAL value +1.18E-38 Largest negative REAL value 1.18E-38 zSeries: Smallest REAL value 7.2E+75 Largest REAL value +7.2E+75 Smallest positive REAL value +5.4E79 Largest negative REAL value 5.4E79
DOUBLE	53	LUW: Smallest DOUBLE value 1.79769E+308 Largest DOUBLE value +1.79769E+308 Smallest positive DOUBLE value +2.225E307 Largest negative DOUBLE value 2.225E307 iSeries: Smallest DOUBLE value 1.79E+308 Largest DOUBLE value +1.79E+308 Smallest positive DOUBLE value +2.23E308 Largest negative DOUBLE value 2.23E308 zSeries: Smallest REAL value 7.2E+75 Largest REAL value +7.2E+75 Smallest positive REAL value +5.4E79 Largest negative REAL value 5.4E79

DB2 supports both single-byte and double-byte character strings. DB2 uses 2 bytes to represent each character in double-byte strings. Their maximum lengths are listed in Table 3.2.

Table 3.2. DB2 Built-In String Data Types

Data Type	Maximum Length
CHAR	LUW: 254 bytes iSeries: 32,766 bytes zSeries: 255 bytes
VARCHAR	LUW: 32,672 bytes iSeries: 32,740 bytes zSeries: 32,704 bytes
LONG VARCHAR (LUW only)	32,700 bytes
CLOB	2,147,483,647 bytes
GRAPHIC	LUW: 127 characters iSeries: 16,383 characters zSeries: 127 characters
VARGRAPHIC	LUW: 16,336 characters iSeries: 16,370 characters zSeries: 16,352 characters
DBCLOB	1,073,741,823 characters
BINARY (iSeries only)	32,766 bytes
VARBINARY (iSeries only)	32,740 bytes
BLOB	2,147,483,647 bytes

You can also specify a subtype for string data types. For example, CHAR and VARCHAR columns can be defined as FOR BIT DATA to store binary data. On iSeries, other subtypes can be specified such as FOR SBCS DATA, FOR DBCS DATA, and CCSID. On zSeries, other subtypes that can be specified are FOR SBCS DATA and FOR MIXED DATA.

DB2 date and time data types include DATE, TIME, and TIMESTAMP. The TIMESTAMP data type consists of both the date part and the time part, while DATE and TIME data types only deal with the date and the time component, respectively. Their limits are listed in Table 3.3.

Table 3.3. DB2 Built-In Date Time Data Types

Description	Limits
Smallest DATE value	0001-01-01
Largest DATE value	9999-12-31
Smallest TIME value	00:00:00
Largest TIME value	24:00:00
Smallest TIMESTAMP value	0001-01-01-00.00.00.000000
Largest TIMESTAMP value	9999-12-31-24.00.00.000000

The last data type in Figure 3.1, DATALINK, is used to work with files stored outside the database. It is not covered in this book.