Creating tables in an IBM Db2 Warehouse on Cloud Enterprise MPP or IBM Db2 Warehouse MPP database
You can create tables in a IBM® Db2® Warehouse on Cloud Enterprise MPP or IBM Db2 Warehouse MPP database by using the web console or through an application that is connected to the database. When you create a table, you must decide how to distribute the table data.
About this task
- Hash distribution
-
Data is distributed by applying a hashing algorithm to the values in the columns that are listed in a distribution key.
A well-chosen distribution key can balance two objectives:- Maximizing parallel processing of queries and maximizing use of available storage space by distributing table data evenly across the system.
- Minimizing the time that it takes to fetch data by collocating data that is likely to be fetched together.
In general, a well-chosen hash distribution key produces the best performance.
You can specify the distribution key by including the DISTRIBUTE BY HASH clause in the CREATE TABLE statement.
- Random distribution
-
Data is distributed evenly across the system.
Random distribution maximizes parallel processing of queries and maximizes use of available storage space.
Consider using random distribution as a simpler alternative to hash distribution:- If you don't have enough information to choose an effective hash distribution key.
- If you know that collocation isn't needed for the queries that are entered against the table.
- If the table is small enough that performance is not affected by the distribution.
You can use random distribution by including the DISTRIBUTE BY RANDOM clause in the CREATE TABLE statement.
If you specify DISTRIBUTE BY RANDOM when creating a table with a primary or unique key, the database manager will implement the random distribution by creating a hash key on the unique or primary key. In this case, when you view the table definition in the web console or the catalog tables, you will see this hash distribution key despite the fact that you specified DISTRIBUTE BY RANDOM when you created the table.
- Default
-
If you do not specify a distribution clause with the CREATE TABLE statement, a default hash distribution key is used.
Procedure
Issue the CREATE TABLE statement in either the web console or an application that is connected to your MPP database.