Restrictions on data redistribution

Restrictions on data redistribution are important to note before proceeding with data redistribution or when troubleshooting problems related to data redistribution.

The following restrictions apply to data redistribution:
  • Data redistribution on partitions where tables do not have partitioning key definitions is restricted.
  • When data redistribution is in progress:
    • Starting another redistribution operation on the same database partition group is restricted.
    • Dropping the database partition group is restricted.
    • Altering the database partition group is restricted.
    • Executing an ALTER TABLE statement on any table in the database partition group is restricted.
    • Creating new indexes in the table undergoing data redistribution is restricted.
    • Dropping indexes defined on the table undergoing data redistribution is restricted.
    • Querying data in the table undergoing data redistribution is restricted.
    • Updating the table undergoing data redistribution is restricted.
  • Updating tables in a database undergoing a data redistribution that was started using the REDISTRIBUTE DATABASE PARTITION GROUP command where the NOT ROLLFORWARD RECOVERABLE command parameter was specified is restricted. Although the updates can be made, if data redistribution is interrupted the changes made to the data might be lost and so this practice is strongly discouraged.
  • When the REDISTRIBUTE DATABASE PARTITION GROUP command is issued and the NOT ROLLFORWARD RECOVERABLE command parameter is specified:
    • Data distribution changes that occur during the redistribution are not roll-forward recoverable.
    • If the database is recoverable, the table space is put into the BACKUP PENDING state after accessing the first table within the table space. To remove the table from this state, you must take a backup of the table space changes when the redistribution operation completes.
    • During data redistribution, the data in the tables in the database partition group being redistributed cannot be updated - the data is read-only. Tables that are actively being redistributed are inaccessible.
  • For typed (hierarchy) tables, if the REDISTRIBUTE DATABASE PARTITION GROUP command is used and the TABLE parameter is specified with the value ONLY, then the table name is restricted to being the name of the root table only. Sub-table names cannot be specified.
  • Data redistribution is supported for the movement of data between database partitions. For partitioned tables, however, movement of data between ranges of a data partitioned table is restricted unless both of the following are true:
    • The partitioned table has an access mode of FULL ACCESS in the SYSTABLES.ACCESS_MODE catalog table.
    • The partitioned table does not have any partitions currently being attached or detached.
  • For replicated materialized query tables, if the data in a database partition group contains replicated materialized query tables, you must drop these tables before you redistribute the data. After data is redistributed, you can recreate the materialized query tables.
  • For database partitions that contain multi-dimensional-clustered tables (MDCs) use of the REDISTRIBUTE DATABASE PARTITION GROUP command is restricted and will not proceed successfully if there are any multi-dimensional-clustered tables in the database partition group that contain rolled out blocks that are pending cleanup. These MDC tables must be cleaned up before data redistribution can be resumed or restarted.
  • Dropping tables that are currently marked in the Db2® catalog views as being in the state "Redistribute in Progress" is restricted. To drop a table in this state, first run the REDISTRIBUTE DATABASE PARTITION GROUP command with the ABORT or CONTINUE parameters and an appropriate table list so that redistribution of the table is either completed or aborted.