Related to dimension tables are distributed domains, which are explicit domains used in the field definitions of dimension tables. A distributed domain, like a dimension table, is associated with a specific partitioned table on the head node. When this kind of domain is created, it is copied automatically to each node defined for the partitioned table.
Distributed domains are useful when standard tables are converted to dimension tables, if the table does not use the built-in domains for all columns. When a dimension table is created via the CREATE TABLE command in MPP mode, the table and any explicit domains used to define the table are automatically replicated on all of the partition nodes. However, if an existing table is converted to a dimension table using the ALTER TABLE...ADD DIMENSION or ...DIMENSION OF command, any explicit domains used to define the table are not copied to the other nodes, which means that the dimension conversion will fail as the table cannot be replicated on the other nodes. Distributed domains therefore simplify the use of explicit domains with potential dimension tables by automatically copying the head node domains to other nodes.
Keep in mind that a domain must be distributed before a participating dimension table is created or converted. Since a standard domain cannot be later converted into a distributed domain, a standard table must not be defined on any non-distributed domains if it will be converted to a dimension table.
To create a distributed domain:
In MPP mode
Not in MPP mode
To create a non-distributed domain in MPP mode, use the CREATE LOCAL DOMAIN syntax.
Note that when creating distributed domains, the containing schema on the head node must already exist on the remote nodes where they will be replicated, and the users defined for the connections to the remote nodes must have the necessary privileges, otherwise the domain creation command will fail.