Database partition compatibility is defined
between the base data types of corresponding columns of distribution
keys. Database partition-compatible data types have the property that
two variables, one of each type, with the same value, are mapped to
the same distribution map index by the same database partitioning
function.
Table 1 shows the compatibility
of data types in database partitions.
Database partition compatibility has the following characteristics:
- Internal formats are used for DATE, TIME, and TIMESTAMP.
They are not compatible with each other, and none are compatible with
character or graphic data types.
- Partition compatibility is not affected by the nullability
of a column.
- Partition compatibility is affected by collation.
Locale-sensitive UCA-based collations require an exact match in collation,
except that the strength (S) attribute of the collation is ignored.
All other collations are considered equivalent for the purposes of
determining partition compatibility.
- Character columns defined with FOR BIT DATA are only
compatible with character columns without FOR BIT DATA when a collation
other than a locale-sensitive UCA-based collation is used.
- Null values of compatible data types are treated identically.
Different results might be produced for null values of
non-compatible data types.
- Base data type of the UDT is used to analyze database partition
compatibility.
- Timestamps of the same value in the distribution
key are treated identically, even if their timestamp precisions differ.
- Decimals of the same value in the distribution key are treated
identically, even if their scale and precision differ.
- Trailing blanks in character strings (CHAR, VARCHAR, GRAPHIC or
VARGRAPHIC) are ignored by the system-provided hashing function.
- When a locale-sensitive UCA-based collation is used,
CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are compatible data types.
When other collations are used, CHAR and VARCHAR are compatible types
and GRAPHIC and VARGRAPHIC are compatible types, but CHAR and VARCHAR
are not compatible types with GRAPHIC and VARGRAPHIC. CHAR or VARCHAR
of different lengths are compatible data types.
- DECFLOAT values that are equal are treated identically even if
their precision differs. DECFLOAT values that are numerically equal
are treated identically even if they have a different number of significant
digits.
- Data types that are not supported as part of a
distribution key are not applicable for database partition compatibility.
This includes columns whose data type is BLOB, CLOB, DBCLOB, XML,
distinct type based on any of these data types, or structured type.
Table 1. Database
Partition Compatibilities| Operands |
Binary Integer |
Decimal Number |
Floating- point |
Decimal Floating-point |
Character String |
Graphic String |
Date |
Time |
Time- stamp |
Distinct Type |
|---|
| Binary Integer |
Yes |
No |
No |
No |
No |
No |
No |
No |
No |
1 |
| Decimal Number |
No |
Yes |
No |
No |
No |
No |
No |
No |
No |
1 |
| Floating-point |
No |
No |
Yes |
No |
No |
No |
No |
No |
No |
1 |
| Decimal Floating-point |
No |
No |
No |
Yes |
No |
No |
No |
No |
No |
1 |
| Character String |
No |
No |
No |
No |
Yes2 |
2, 3 |
No |
No |
No |
1 |
| Graphic String |
No |
No |
No |
No |
2, 3 |
Yes2 |
No |
No |
No |
1 |
| Date |
No |
No |
No |
No |
No |
No |
Yes |
No |
No |
1 |
| Time |
No |
No |
No |
No |
No |
No |
No |
Yes |
No |
1 |
| Timestamp |
No |
No |
No |
No |
No |
No |
No |
No |
Yes |
1 |
| Distinct Type |
1 |
1 |
1 |
1 |
1 |
1 |
1 |
1 |
1 |
1 |
Note:
- 1
- A distinct type value is database partition compatible
with the source data type of the distinct type or with any other distinct
type with the same source data type. The source data type of the distinct
type must be a data type that is supported as part of a distribution
key. A user-defined distinct type (UDT) value is database partition
compatible with the source type of the UDT or any other UDT with a
database partition compatible source type. A distinct type cannot
be based on BLOB, CLOB, DBCLOB, or XML.
- 2
- Character and graphic string types are compatible
when they have compatible collations.
- 3
- Character and graphic string types are compatible
when a locale-sensitive UCA-based collation is in effect. Otherwise,
they are not compatible types.
|
DB2 Version 9.7 for Linux, UNIX, and Windows