Data types in Db2 for z/OS

Db2 supports both IBM®-supplied data types (built-in data types) and user-defined data types (distinct types).

The smallest unit of data that can be manipulated in SQL is called a value. How values are interpreted depends on the data type of their source. Values have the following sources:

  • Columns
  • Constants
  • Expressions
  • Functions
  • Special registers
  • Variables (such as host variables, SQL variables, global variables, parameter markers, and parameters of routines)

The following topics describes the built-in data types and distinct types.

Figure 1 shows the built-in data types that Db2 for z/OS supports.
Figure 1. Built-in data types supported by Db2 for z/OS
Begin figure description. A hierarchical diagram shows the relationship between the general data types and the specific data types that are contained in each general category. End figure description.

NULLS

All data types include the null value. Distinct from all nonnull values, the null value is a special value that denotes the absence of a (nonnull) value.

Although all data types include the null value, some sources of values cannot provide the null value. For example, constants, columns that are defined as NOT NULL, and special registers cannot contain null values; the COUNT and COUNT_BIG functions cannot return a null value; and ROWID columns cannot store a null value although a null value can be returned for a ROWID column as the result of a query.

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.