Data redistribution mechanism

Data redistribution can be performed by using different methods in different interfaces however internally the mechanism by which the data is moved is the same. It can be helpful to understand this mechanism so that you are aware of automatic changes being made within the Db2® database environment.

Data redistribution involves the use of the available source distribution map and target distribution map to identify hash database partitions that have been assigned to a new location. The new location is identified by a new database partition number. All rows that correspond to a database partition that have a new location are moved from the database partition specified in the source distribution map to the database partition specified in the target distribution map.

Data redistribution internally invokes a utility that performs the following ordered actions:

  1. Obtains a new distribution map ID for the target distribution map, and inserts it into the SYSCAT.PARTITIONMAPS catalog view.
  2. Updates the REDISTRIBUTE_PMAP_ID column in the SYSCAT.DBPARTITIONGROUPS catalog view for the database partition group with the new distribution map ID.
  3. Adds any new database partitions to the SYSCAT.DBPARTITIONGROUPDEF catalog view.
  4. Sets the IN_USE column in the SYSCAT.DBPARTITIONGROUPDEF catalog view to 'D' for any database partition that is to be dropped, if the DROP DBPARTITIONNUM command parameter was specified.
  5. Commits all catalog updates.
  6. Creates database files for all new database partitions if the ADD DBPARTITIONNUM command parameter is specified; also might create table spaces in the new database partitions.
  7. Redistributes the data on a table-by-table basis for every table in the database partition group, in the following steps:
    1. Puts the table spaces into the BACKUP PENDING state, if the utility did not put them into that state already.
    2. Locks the row for the table in the SYSTABLES catalog table.
    3. Invalidates all packages that involve this table. The distribution map ID associated with the table changes because the table rows are redistributed. Because the packages are invalidated, the compiler must obtain the new database partitioning information for the table and generate packages accordingly.
    4. Locks the table in super exclusive mode (with a z-lock).
    5. Redistributes data by using bulk data movement operations.
    6. If the redistribution operation succeeds, the distribution map ID for the table is updated in SYSCAT.TABLES. The utility issues a COMMIT for the table and continues with the next table in the database partition group. If the operation fails before the table is fully redistributed, the utility fails. Any partially redistributed tables are left in the REDIST_IN_PGRS state and the table is inaccessible until the redistribute operation is either continued or aborted.
    Deletes database files and deletes entries in the SYSCAT.DBPARTITIONGROUPDEF catalog view for database partitions that were previously marked to be dropped.
  8. Updates the database partition group record in the SYSCAT.DBPARTITIONGROUPS catalog view to set PMAP_ID to the value of REDISTRIBUTE_PMAP_ID and REDISTRIBUTE_PMAP_ID to NULL.
  9. Deletes the old distribution map from the SYSCAT.PARTITIONMAPS catalog view.
  10. Does a COMMIT for all changes.

When these steps are done data redistribution is complete. For more information about the success or failure status of the data redistribution and each of the individual data redistributions, review the redistribution log file.