Redistributing data across database partitions by using the REDISTRIBUTE DATABASE PARTITION GROUP command

The REDISTRIBUTE DATABASE PARTITION GROUP command is the recommended interface for performing data redistribution.

To redistribute data across database partitions in a database partition group:

1. Optional: Perform a backup of the database. See the BACKUP DATABASE command.

   It is strongly recommended that you create a backup copy of the database before you perform a data redistribution that is not roll-forward recoverable.

2. Connect to the database partition that contains the system catalog tables. See the CONNECT statement.
3. Issue the REDISTRIBUTE DATABASE PARTITION GROUP command.
   Note: In previous versions of the DB2® database product, this command used the NODEGROUP keyword instead of the DATABASE PARTITION GROUP keywords.
   Specify the following arguments:

   database partition group name

   You must specify the database partition group within which data is to be redistributed.

   UNIFORM

   OPTIONAL: Specifies that data is to be evenly distributed. UNIFORM is the default when no distribution-type is specified, so it is valid to omit this option if no other distribution type has been specified.

   USING DISTFILE distfile-name

   OPTIONAL: Specifies that a customized distribution is desired and the file path name of a distribution file that contains data that defines the desired data skew. The contents of this file is used to generate a target distribution map.

   USING TARGETMAP targetmap-name

   OPTIONAL: Specifies that a target data redistribution map is to be used and the name of file that contains the target redistribution map.

   For details, refer to the REDISTRIBUTE DATABASE PARTITION GROUP command-line utility information.
4. Allow the command to run uninterrupted. When the command completes, perform the following actions if the data redistribution proceeded successfully:
   • Take a backup of all table spaces in the database partition group that are in the BACKUP PENDING state. Alternatively, a full database backup can be performed.
     Note: Table spaces are only put into the BACKUP PENDING state if the database is recoverable and the NOT ROLLFORWARD RECOVERABLE command parameter is used in the REDISTRIBUTE DATABASE PARTITION GROUP command.
   • Recreate any replicated materialized query tables dropped before redistribution.
   • Execute the RUNSTATS command if the following conditions are met:
     • The STATISTICS NONE command parameter was specified in the REDISTRIBUTE DATABASE PARTITION GROUP command, or the NOT ROLLFORWARD RECOVERABLE command parameter was omitted. Both of these conditions mean that the statistics were not collected during data redistribution.
     • There are tables in the database partition group possessing a statistics profile.
     The RUNSTATS command collects data distribution statistics for the SQL compiler and optimizer to use when choosing data access plans for queries.
   • If the NOT ROLLFORWARD RECOVERABLE command parameter was specified, delete the control files located in the following paths :
     • On Linux and UNIX operating systems: diagpath/redist/db_name/db_partitiongroup_name/timestamp/
     • On Windows operating systems: diagpath\redist\db_name\db_partitiongroup_name\timestamp\