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:
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.
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.
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.
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.
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.