Prerequisites for data redistribution

Before data redistribution can be performed successfully for a set of tables within a database partition group, certain prerequisites must be met.

The following is a list of mandatory prerequisites:
  • Authorization to perform data redistribution from the supported data redistribution interface of choice. See the related links to the reference documentation for each of the supported interfaces which detail the specific prerequisites.
  • A significant amount of time during a period of low system activity in which to perform the redistribution operation.
  • All tables containing data to be redistributed as part of a data redistribution operation must be in a NORMAL state. For example, tables cannot be in LOAD PENDING state or other inaccessible load table states. To check the states of tables, establish a connection to each partition in the database partition group and issue the LOAD QUERY command. The output of this command contains information about the state of the table. The documentation of the LOAD QUERY command explains the meaning of each of the table states and how to move tables from one state to another.
  • All tables within the database partition being redistributed must have been defined with a distribution key. If a new database partition is added to a single-partition system, data redistribution cannot be performed until all of the tables within the partitions have a distribution key. For tables that were created using the CREATE TABLE statement and have definitions that do not contain a distribution key, you must alter the table by using the ALTER TABLE statement to add a distribution key before redistributing the data.
  • Replicated materialized query tables contained in a database partition group must be dropped before you redistribute the data. Store a copy of the materialized query table definitions so that they can be recreated after data redistribution completes.
  • If a non-uniform redistribution is desired a distribution map must be created as a target distribution map to be used a parameter to the redistribute interface.
  • A backup of the database must be created by using the BACKUP DATABASE command. This backup is not a mandatory prerequisite however it is strongly recommended that it be done.
  • A connection must be established to the database from the catalog database partition.
  • Adequate space must be available to rebuild all indexes either during or after the data redistribution. The INDEXING MODE command parameter affects when the indexes are rebuilt.
  • When the NOT ROLLFORWARD RECOVERABLE command parameter is specified, adequate space should be available for writing status information to control files used by IBM Service for problem determination. The control files are generated in the following paths and should be manually deleted when the data redistribution operation is complete:
    • 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\
    You can calculate the space requirements in bytes for the control files by using the following formula:
    (number of pages for all tables in the database partition group) * 64 bytes 
+ number of LOB values in the database partition group) * 600 bytes
    To estimate number of LOB values in the database partition group, add the number of LOB columns in your tables and multiply it by the number of rows in the largest table.
  • When the NOT ROLLFORWARD RECOVERABLE command parameter is not specified, adequate log file space must be available to contain the log entries associated with the INSERT and DELETE operations performed during data redistribution otherwise data redistribution will be interrupted or fail.

The util_heap_sz database configuration parameter is critical to the processing of data movement between database partitions - allocate as much memory as possible to util_heap_sz for the duration of the redistribution operation. Sufficient sortheap is also required if indexes are being rebuilt as part of the redistribution operation. Increase the value of util_heap_sz and sortheap database configuration parameter, as necessary, to improve redistribution performance.