When you assign data to a nickname column, the data type might
change based on the assignment rules that the federated system uses. You should
understand the assignment rules so you get the results that you expect.
The rules for determining the target data type of an assignment
to a nickname column are:
- Determine the local source type: The local source type is determined by
the local column type or the local result type of the expressions. If the
source is constant, the local source type is the same as the type of the constant.
- Determine the target type:
- If the assignment source has no type, such as parameter markers and NULLs,
then the target type is MIN(local_target_type, remote_target_type), where
local_target_type is the updated column local data type and remote_target_type
is the updated column data source data type. The remote_target_type refers
to the default forward type mapping type of the remote target column's data
type.
- If the assignment source is not NULL or parameter markers, then the target
type is MIN(local_target_type, remote_target_type, local_source_type).
The definition of MIN(type1, type2)
- Type1 and type2 are not exactly the same.
- MIN(type1,type2) = MIN(type2, type1)
- MIN(type1, type2) = remote_target_type(local_target_type), when MIN(type1,
type2) = DECIMAL(0,0)
- BLOB is only compatible with BLOB, so MIN(BLOB(x), BLOB(y))=BLOB(z) where
z=min(x,y)
- TIME and DATE data types are not compatible.
- Datetime types and character strings are compatible.
- In Unicode databases, character strings and graphic strings are compatible.
The following tables list the minimum of two data types for numeric,
character string, graphic string, and date and time data types.
Table 1. Numeric data types
| type1 |
type2 |
MIN(type1, type2) |
| SMALLINT |
SMALLINT or INTEGER or BIGINT or REAL or DOUBLE |
SMALLINT |
| INTEGER |
BIGINT or REAL or DOUBLE |
INTEGER |
| BIGINT |
REAL or DOUBLE |
BIGINT |
| REAL |
DOUBLE |
REAL |
| DECIMAL(w,x) |
SMALLINT |
DECIMAL(p,0) where p=w-x, if p<5; SMALLINT, otherwise |
| DECIMAL(w,x) |
INTEGER |
DECIMAL(p,0) where p=w-x, if p<11; INTEGER, otherwise |
| DECIMAL(w,x) |
BIGINT |
DECIMAL(p,0) where p=w-x, if p<19; BIGINT, otherwise |
| DECIMAL(w,x) |
DECIMAL(y,z) |
DECIMAL(p,s) where p=min(w,y)+min(w-x,y-z), s=min(x,z) |
| DECIMAL(w,x) |
DOUBLE or REAL |
DECIMAL(w,x) |
The following table lists the minimum of two data types for character
string data types.
Table 2. Character string data types
| type1 |
type2 |
MIN(type1, type2) |
| CHAR(x) |
CHAR(y) or VARCHAR(y) or LONG VARCHAR or CLOB(y) |
CHAR(z) where z=min(x,y) |
| VARCHAR(x) |
VARCHAR(y) or LONG VARCHAR or CLOB(y) |
VARCHAR(z) where z=min(x,y) |
| LONG VARCHAR |
CLOB(y) |
LONG VARCHAR where x>32700, CLOB(x) where x<=32700 |
| CLOB(x) |
CLOB(y) |
CLOB(z) where z=min(x,y) |
The following table lists the minimum of two data types for graphic
string data types.
Table 3. Graphic string data types
| type1 |
type2 |
MIN(type1, type2) |
| GRAPHIC(x) |
GRAPHIC(y) or VARGRAPHIC(y) or LONG VARGRAPHIC or DBCLOB(y) |
GRAPHIC(z) where z=min(x,y) |
| VARGRAPHIC(x) |
VARGRAPHIC(y) or LONG VARGRAPHIC or DBCLOB(y) |
VARGRAPHIC(z) where z=min(x,y) |
| LONG VARGRAPHIC |
DBCLOB(y) |
LONG VARGRAPHIC where x>32700, DBCLOB(x) where x<=32700 |
| DBCLOB(x) |
DBCLOB(y) |
DBCLOB(z) where z=min(x,y) |
The following table lists the minimum of two data types for data and
time data types.
Table 4. Date and time data
types
| type1 |
type2 |
MIN(type1, type2) |
| DATE |
TIMESTAMP |
DATE |
| TIME |
TIMESTAMP |
TIME |
If the data of data type CHAR you are inserting is shorter
than the target length, the data source pads the rest of the column.
If
you are inserting data of DATE or TIME data type into a remote column of TIMESTAMP
data type, the data source pads the rest of the column.