Data types of columns

When you create a Db2 table, you define each column to have a specific data type. The data type of a column determines what you can and cannot do with the column.

When you perform operations on columns, the data must be compatible with the data type of the referenced column. For example, you cannot insert character data, such as a last name, into a column whose data type is numeric. Similarly, you cannot compare columns that contain incompatible data types.

The data type for a column can be a distinct type, which is a user-defined data type, or a Db2 built-in data type. As shown in the following figure, Db2 built-in data types have four general categories: datetime, string, numeric, and row identifier (ROWID).

Figure 1. Db2 built-in data types
Begin figure summary.A diagram shows the relationship between the general data types and the specific data types.Detailed description available.

Summary of data types in Db2 for z/OS®

Table 1. Ranges or lengths and byte counts of columns by data type
Data Type Range or length Byte Count
SMALLINT -32768 to +32767 2
INTEGER -2147483648 to +2147483647 4
BIGINT -9223372036854775808 to +9223372036854775807 8
FLOAT(n) approximately -7.2E+75 to 7.2E+75

If n is in the range 1–21, the byte count is 4.

If n is in the range 22–53, the byte count is 8.
DECIMAL 1 - 1031 to 1031 - 1. INTEGER(p/2)+1, where p is the precision
DECFLOAT(16) 10-383 to 10+384 9
DECFLOAT(34) 10-6143 to 10+6144 17
CHAR(n) the length attribute must be in the range 1–255 n
VARCHAR(n) the length attribute must be in the range 1–32704 n+2
CLOB the length attribute must be in the range 1–2147483647 inclusive 6
Inline CLOB the length attribute must be in the range 1–2147483647 inclusive; the inline length must be in the range 0–32680 inclusive 6 + inline byte count
GRAPHIC(n) the length attribute must be in the range 1–127 inclusive 2n
VARGRAPHIC(n) the length attribute must in the range 1–16352 2n+2
DBCLOB the length attribute must be in the range 1–1 073 741 823 inclusive; the inline length must be in the range 0–32680 inclusive 6
Inline DBCLOB the length attribute must be in the range 1–1 073 741 823 inclusive; the inline length must be in the range 0 –16340 6 + (inline char count * 2)
BINARY(n) the length attribute must in the range 1–255 inclusive n
VARBINARY(n) the length attribute must in the range 1–32704 n+2
BLOB the length attribute must in the range 1–32704 6
Inline BLOB the length attribute must in the range 1–32704 6 + inline byte count
DATE See Date 4
TIME See Time 3
TIMESTAMP(p) WITHOUT TIME ZONE See Timestamp INTEGER((p+1)/2) + 7 where p is the precision
TIMESTAMP(p) WITH TIME ZONE See Timestamp INTEGER((p+1)/2) + 9 where p is the precision
ROWID See Row ID values 19
Distinct type See Distinct types The length of the source data type upon which the distinct type was based
XML See XML values

6 - If column cannot contain multiple versions of an XML document.

14 - If column can contain multiple versions of an XML document.

For more information, see How Db2 uses XML versions.