DB2 Version 10.1 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:
  1. The lowercase types in the preceding table 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 (except for a weakly typed distinct type which uses the source type to determine data type precedence)

    Shorter and longer form synonyms of the listed data types are considered to be the same as the listed form.

  2. For a Unicode database, the following data types are considered to be equivalent:
    • 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.