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?