Command Reference

REDISTRIBUTE NODEGROUP

Redistributes data across the nodes in a nodegroup. The current data distribution, whether it is uniform or skewed, can be specified. The redistribution algorithm selects the partitions to be moved based on the current data distribution.

This command can only be issued from the catalog node. Use LIST DATABASE DIRECTORY to determine which node is the catalog node for each database.

Scope

This command affects all nodes in the nodegroup.

Authorization

One of the following:

• sysadm
• sysctrl
• dbadm

Command Syntax

>>-REDISTRIBUTE NODEGROUP--nodegroup---------------------------->
 
>-----+-+-UNIFORM-------------------+-+------------------------><
      | '-USING DISTFILE--distfile--' |
      +-USING TARGETMAP--targetmap----+
      +-CONTINUE----------------------+
      '-ROLLBACK----------------------'
 

Command Parameters

NODEGROUP nodegroup

The name of the nodegroup. This one-part name identifies a nodegroup described in the SYSNODEGROUPS catalog table. The nodegroup cannot currently be undergoing redistribution.

Note:

Tables in the IBMCATGROUP and the IBMTEMPGROUP nodegroups cannot be redistributed.

UNIFORM

Specifies that the data is uniformly distributed across hash partitions (that is, every hash partition is assumed to have the same number of rows), but the same number of hash partitions do not map to each node. After redistribution, all nodes in the nodegroup have approximately the same number of hash partitions.

USING DISTFILE distfile

If the distribution of partitioning key values is skewed, use this option to achieve a uniform redistribution of data across the nodes of a nodegroup.

Use the distfile to indicate the current distribution of data across the 4 096 hash partitions.

Use row counts, byte volumes, or any other measure to indicate the amount of data represented by each hash partition. The utility reads the integer value associated with a partition as the weight of that partition. When a distfile is specified, the utility generates a target partitioning map that it uses to redistribute the data across the nodes in the nodegroup as uniformly as possible. After the redistribution, the weight of each node in the nodegroup is approximately the same (the weight of a node is the sum of the weights of all partitions that map to that node).

For example, the input distribution file may contain entries as follows:

   10223
   1345
   112000
   0
   100
   ...

In the example, hash partition 2 has a weight of 112 000, and partition 3 (with a weight of 0) has no data mapping to it at all.

The distfile should contain 4 096 positive integer values in character format. The sum of the values should be less than or equal to 4 294 967 295.

If the path for distfile is not specified, the current directory is used.

USING TARGETMAP targetmap

The file specified in targetmap is used as the target partitioning map. Data redistribution is done according to this file. If the path is not specified, the current directory is used.

If a node included in the target map is not in the nodegroup, an error is returned. Issue ALTER NODEGROUP ADD NODE before running REDISTRIBUTE NODEGROUP.

If a node excluded from the target map is in the nodegroup, that node will not be included in the partitioning. Such a node can be dropped using ALTER NODEGROUP DROP NODE either before or after REDISTRIBUTE NODEGROUP.

CONTINUE

Continues a previously failed REDISTRIBUTE NODEGROUP operation. If none occurred, an error is returned.

ROLLBACK

Rolls back a previously failed REDISTRIBUTE NODEGROUP operation. If none occurred, an error is returned.

Usage Notes

When a redistribution operation is done, a message file is written to:

• The /sqllib/redist directory on UNIX based systems, using the following format for subdirectories and file name: database-name.nodegroup-name.timestamp.
• The \sqllib\redist\ directory on Windows operating systems, using the following format for subdirectories and file name: database-name\first-eight-characters-of-the-nodegroup-name\date\time.

The time stamp value is the time when the command was issued.

This utility performs intermittent COMMITs during processing.

Use the ALTER NODEGROUP statement to add nodes to a nodegroup. This statement permits one to define the containers for the table spaces associated with the nodegroup. See the SQL Reference for details.

Note:

DB2 Parallel Edition for AIX Version 1 syntax, with ADD NODE and DROP NODE options, is supported for users with sysadm or sysctrl authority. For ADD NODE, containers are created like the containers on the lowest node number of the existing nodes within the nodegroup.

All packages having a dependency on a table that has undergone redistribution are invalidated. It is recommended to explicitly rebind such packages after the redistribute nodegroup operation has completed. Explicit rebinding eliminates the initial delay in the execution of the first SQL request for the invalid package. The redistribute message file contains a list of all the tables that have undergone redistribution.

It is also recommended to update statistics by issuing RUNSTATS after the redistribute nodegroup operation has completed.

Nodegroups containing replicated summary tables or tables defined with DATA CAPTURE CHANGES cannot be redistributed.

Redistribution is not allowed if there are user temporary table spaces with existing declared temporary tables in the nodegroup.

See Also

REBIND.