DB2 Version 9.7 for Linux, UNIX, and Windows

Promotion of data types

Data types can be classified into groups of related data types. Within such groups, a precedence order exists where one data type is considered to precede another data type. This precedence is used to allow the promotion of one data type to a data type later in the precedence ordering. For example, the data type CHAR can be promoted to VARCHAR; INTEGER can be promoted to DOUBLE-PRECISION; but CLOB is NOT promotable to VARCHAR.

Promotion of data types is used when:

Performing function resolution
Casting user-defined types
Assigning user-defined types to built-in data types

Table 1 shows the precedence list (in order) for each data type and can be used to determine the data types to which a given data type can be promoted. The table shows that the best choice is always the same data type instead of choosing to promote to another data type.

Table 1. Data Type Precedence Table
Data Type	Data Type Precedence List (in best-to-worst order)
SMALLINT	SMALLINT, INTEGER, BIGINT, decimal, real, double, DECFLOAT
INTEGER	INTEGER, BIGINT, decimal, real, double, DECFLOAT
BIGINT	BIGINT, decimal, real, double, DECFLOAT
decimal	decimal, real, double, DECFLOAT
real	real, double, DECFLOAT
double	double, DECFLOAT
DECFLOAT	DECFLOAT
CHAR	CHAR, VARCHAR, CLOB
VARCHAR	VARCHAR, CLOB
CLOB	CLOB
GRAPHIC	GRAPHIC, VARGRAPHIC, DBCLOB
VARGRAPHIC	VARGRAPHIC, DBCLOB
DBCLOB	DBCLOB
BLOB	BLOB
DATE	DATE, TIMESTAMP
TIME	TIME
TIMESTAMP	TIMESTAMP
BOOLEAN	BOOLEAN
CURSOR	CURSOR
ARRAY	ARRAY
udt	udt (same name) or a supertype of udt
REF(T)	REF(S) (provided that S is a supertype of T)
ROW	ROW
Note: The lowercase types above are defined as follows: decimal = DECIMAL(p,s) or NUMERIC(p,s) real = REAL or FLOAT(n), where n is not greater than 24 double = DOUBLE, DOUBLE-PRECISION, FLOAT or FLOAT(n), where n is greater than 24 udt = a user-defined type Shorter and longer form synonyms of the listed data types are considered to be the same as the listed form. For a Unicode database, the following are considered to be equivalent data types: CHAR and GRAPHIC VARCHAR and VARGRAPHIC CLOB and DBCLOB When resolving a function within a Unicode database, if a user-defined function and a built-in function are both applicable for a given function invocation, then generally the built-in function will be invoked. The UDF will be invoked only if its schema precedes SYSIBM in the CURRENT PATH special register and if its argument data types match all the function invocation argument data types, regardless of Unicode data type equivalence.