Changing distribution keys - IBM DB2 9.7 for Linux, UNIX, and Windows

You can only change a distribution key on tables in a single database partition.

About this task

For partitioned database environments only.

First drop the existing distribution key, and then create another.

To change distribution keys using the Control Center:

Open the Alter Table notebook: From the Control Center, expand the object tree until you find the Tables folder. Click the Tables folder. Any existing tables are displayed in the pane on the right side of the window. Right-click the table you want and select Alter from the pop-up menu. The Alter Table notebook opens.
On the Keys page, select a distribution key in the table and click Change. The Change Distribution Key window opens.
Select the columns that you want to add as distribution key columns and move them to the Selected columns box.

To change distribution keys using the command line, use the DROP DISTRIBUTION option of the ALTER TABLE statement. For example, the following SQL statement drops the distribution key MIX_INT from the MIXREC table:

    ALTER TABLE MIXREC
       DROP DISTRIBUTION

You cannot change the distribution key of a table spanning multiple database partitions. If you try to drop it, an error is returned.

To change the distribution key of multiple database partitions, either:

Export all of the data to a single database partition and then follow the above instructions.
Export all of the data, drop the table, recreate the table redefining the distribution key, and then import all of the data.

Neither of these methods are practical for large databases; it is therefore essential that you define the appropriate distribution key before implementing the design of large databases.