Data types

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
Start of changeBegin figure summary.A diagram shows the relationship between the general data types and the specific data types.Detailed description available.End of change

The following table shows whether operands of any two data types are compatible, Y (Yes), or incompatible, N (No). Notes are indicated either as a superscript number next to Y or N or as a value in the column of the table.

Table 1. Supported casts between built-in data types
Cast from data type – To data type1
SMALLINT INTEGER BIGINT DECIMAL DECFLOAT REAL DOUBLE CHAR VARCHAR CLOB GRAPHIC VARGRAPHIC DBCLOB BINARY VARBINARY BLOB DATE TIME Start of changeTIMESTAMP WITHOUT TIME ZONEEnd of change Start of changeTIMESTAMP WITH TIME ZONEEnd of change ROWID XML
SMALLINT Y Y Y Y Y Y Y Y Y                          
INTEGER Y Y Y Y Y Y Y Y Y                          
BIGINT Y Y Y Y Y Y Y Y Y                          
DECIMAL Y Y Y Y Y Y Y Y Y                          
DECFLOAT Y Y Y Y Y Y Y Y Y                          
REAL Y Y Y Y Y Y Y Y Y                          
DOUBLE Y Y Y Y Y Y Y Y Y                          
CHAR Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Start of changeYEnd of change Y  
VARCHAR Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Start of changeYEnd of change Y  
CLOB               Y Y Y Y Y Y Y Y Y            
GRAPHIC Y Y Y Y Y Y Y Y2 Y2 Y2 Y Y Y Y Y Y Y3 Y3 Y3 Start of changeY3End of change    
VARGRAPHIC Y Y Y Y Y Y Y Y2 Y2 Y2 Y Y Y Y Y Y Y Y Y Start of changeY3End of change    
DBCLOB               Y2 Y2 Y2 Y Y Y Y Y Y            
BINARY                           Y Y Y            
VARBINARY                           Y Y Y            
BLOB                           Y Y Y            
DATE               Y Y               Y          
TIME               Y Y                 Y        
Start of changeTIMESTAMP WITHOUT TIME ZONEEnd of change               Y Y               Y Y Y Start of changeYEnd of change    
Start of changeTIMESTAMP WITH TIME ZONEEnd of change Start of change End of change Start of change End of change Start of change End of change Start of change End of change Start of change End of change Start of change End of change Start of change End of change Start of changeYEnd of change Start of changeYEnd of change Start of change End of change Start of change End of change Start of change End of change Start of change End of change Start of change End of change Start of change End of change Start of change End of change Start of changeYEnd of change Start of changeYEnd of change Start of changeYEnd of change Start of changeYEnd of change Start of change End of change Start of change End of change
ROWID               Y Y         Y Y Y         Y  
XML                                           Y
Note:
  1. Other synonyms for the listed data types are considered to be the same as the synonym listed. Some exceptions exist when the cast involves character string data if the subtype is FOR BIT DATA.
  2. The result length for these casts is 3 * LENGTH(graphic string).
  3. These data types are castable between each other only if the data is Unicode.