Distributing Columns for IBM DB2 for LUW 9.x (and later versions)
Go Up to Developing the Logical Model
For IBM DB2 for LUW 9.x (and later versions). The Distribute Columns tab of the Table Editor allows you to create the DISTIBUTE BY... statement for IBM DB2 that specifies how the table should be distributed across databases partitions within a single tablespace.
- Available Columns: Displays all the columns available to add to the partition. Select the column you want to add to the partition and move it to the Selected Columns box.
- Selected Columns: Displays the columns that make up the partition key. This guides how data is divided along the partition boundaries. Typically, a partition key is date-based or based on a numerically ordered data grouping. Use the left and right arrows to move columns to and from this grid. Columns that are in this grid make up the partition.
- Up / Down buttons: Let you reorder the columns on the partition. The partition order can affect the access speed. The most frequently accessed columns should be at the top of the partition list.
- By Hash: For IBM DB2. If selected, ER/Studio Data Architect partitions the table according to a hash function, which can speed up table lookup or data comparison tasks.
Completing this tab will produce SQL code similar to the following:
CREATE TABLE Entity1(
PK1 CHAR(10) NOT NULL,
A1 CHAR(10),
A2 CHAR(10),
CONSTRAINT PK1 PRIMARY KEY (PK1)
)
DISTRIBUTE BY HASH (PK1,A1)
A column cannot be used as part of a distribution key if the data type of the column is LONG VARCHAR, LONG VAR GRAPHIC, BLOB, CLOB, DATALINK, XML distinct type on any of these types, or structured type. Available columns are limited to columns that are common to all existing PKs or unique constraints.