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?

Continue reading →