Search code examples
hashdb2partitionalter

DB2 Alter Table and add Hash

Is it possible to alter an existing table in DB2 to add a hash partition? Something like...

ALTER TABLE EXAMPLE.TEST_TABLE
PARITION BY HASH(UNIQUE_ID)

Thanks!

Solution

  • If you run a Db2-LUW local server on zLinux, the following syntax may be available:

    ALTER TABLE .. ADD DISTRIBUTE BY HASH (...)

    This syntax is not available if the zLinux is not running a Db2-LUW server but is instead only a client of Db2-for-z/OS.

    For this syntax to be meaningful, there are various pre-requisites. Refer to the documentation for details of partitioned instances, database partition groups , distribution key rules and default behaviours etc.

    The intention of distributed tables (spread over multiple physical and/or logical partitions of a partitioned database in a partitioned Db2-instance) is to exploit hardware capabilities. So if your Db2-instance and database and tablespaces are not appropriately configured, this syntax has limited value.

    Depending on your true motivations, partition by range may offer functionality that is useful. Note that partition by range can be combined with distribute by hash if the configuration is appropriate.