The DB2 documentation is a good place to start ... here are some excerpts that may help
you ...
"In an environment where the database is physically divided among two or more database
partitions, there must be a way to keep the related pieces of the divided tables as close
together as possible. The ability to do this is called table collocation .
Tables are collocated when they are stored in the same database partition group, and when
their partitioning keys are compatible. Placing both tables in the same database partition
group ensures a common partitioning map. The tables may be in different table spaces, but
the table spaces must be associated with the same database partition group. The data types
of the corresponding columns in each partitioning key must be partition-compatible . "
"The base data types of corresponding columns of partitioning keys are compared and can be
declared partition compatible . Partition compatible data types have the property that two
variables, one of each type, with the same value, are mapped to the same partition number
by the same partitioning algorithm.
Partition compatibility has the following characteristics :
a.. A base data type is compatible with another of the same base data type.
b.. Internal formats are used for DATE, TIME, and TIMESTAMP data types. They are not
compatible with each other, and none are compatible with CHAR.
c.. Partition compatibility is not affected by columns with NOT NULL or FOR BIT DATA
definitions.
d.. NULL values of compatible data types are treated identically; those of
non-compatible data types may not be.
e.. Base data types of a user-defined type are used to analyze partition compatibility.
f.. Decimals of the same value in the partitioning key are treated identically, even if
their scale and precision differ.
g.. Trailing blanks in character strings (CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC) are
ignored by the hashing algorithm.
h.. BIGINT, SMALLINT, and INTEGER are compatible data types.
i.. REAL and FLOAT are compatible data types.
j.. CHAR and VARCHAR of different lengths are compatible data types.
k.. GRAPHIC and VARGRAPHIC are compatible data types.
l.. Partition compatibility does not apply to LONG VARCHAR, LONG VARGRAPHIC, CLOB,
DBCLOB, and BLOB data types, because they are not supported as partitioning keys. "
--
Bob
Engagement Specialist - DB2 Information Management Software - IBM Software Group
IBM Toronto Lab
[My comments are solely my own and are not meant to represent an official IBM position -
ask my cat!]
"Doug Crowson" <cr******@mayo. edu> wrote in message news:cl******** **@tribune.mayo .edu...
I have some questions about co-located joins and db partitions. I've heard
various things from various people and was looking for confirmation.
Assume the following tables and partitioning keys:
DEMOG
----------
WHKEY BIGINT NOT NULL -- index and partitioning key
FINDNG
-----------
PAT_NUM INTEGER NOT NULL -- index and partitioning key
Would a join on these tables (DEMOG.WHKEY = FINDNG.PAT_NUM)
be considered co-located by the optimizer?
One person indicated the fields had to have the same name / datatype.
Is that the case, or do the data types just have to be compatible?
Are BIGINT and INTEGER considered compatible datatypes?
Is there a RedBook or Tech guide that gives a good summary
of colocated joins and partitioning?
--
Douglas Crowson Rochester, MN 55905
Information Services (507) 284-0311
Mayo Foundation cr******@mayo.e du
