In a database partitioned environment (MPP), DB2 can store rows of a single table on multiple nodes.
For example, let’s create a sample table as follows:
CREATE TABLE cities ( id INT, name VARCHAR(30), state CHAR(2) ) DISTRIBUTE BY HASH (id);
Now let’s insert a few rows:
INSERT INTO cities VALUES (1, 'Seattle', 'WA'); INSERT INTO cities VALUES (2, 'Boston', 'MA'); INSERT INTO cities VALUES (3, 'Los Angeles', 'CA'); COMMIT;
DISTRIBUTE BY clause specifies that rows of CITIES table are distributed among all nodes in the partition group by ID column value. Using DBPARTITIONNUM built-in function you can see on which nodes the rows are actually located:
SELECT name, DBPARTITIONNUM(id) FROM cities; Seattle 8 Boston 24 Los Angeles 24
You can see that rows are stored on nodes 8 and 24 in DB2.
What about Indexes?
If you create an index on a table, will DB2 distribute its blocks to nodes different from the corresponding table row nodes? No, DB2 uses a local index, each node creates an index only for the table rows stored on the node.
That’s why DB2 does not allow you to create an unique index on columns that are not used in DISTRIBUTE BY:
CREATE UNIQUE INDEX idx_cities ON cities(name); SQLSTATE 42997: Capability is not supported by this version of the DB2 application requester, DB2 application server, or the combination of the two.
Uniqueness must be enforced among all rows in the table while each node can only check uniqueness on local data only. If you insert one more row for city
Seattle it can be stored on another node:
INSERT INTO cities VALUES (4, 'Seattle', 'WA'); COMMIT;
You can see that this row in stored on node 24:
SELECT name, DBPARTITIONNUM(id) FROM cities; Boston 24 Los Angeles 24 Seattle 24 Seattle 8
To ensure uniqueness on
name column all nodes should communicate with each other on each INSERT statement, but DB2 LUW 9.7 just does not allow you to create such unique indexes.