DB2 Version 9.7 for Linux, UNIX, and Windows

REDISTRIBUTE DATABASE PARTITION GROUP command

Redistributes data across all partitions in a database partition group. This command affects all objects present in the database partition group and cannot be restricted to one object alone.

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

Scope

This command affects all database partitions in the database partition group.

Authorization

One of the following authorities is required:

SYSADM
SYSCTRL
DBADM

In addition, one of the following groups of authorizations is also required:

DELETE, INSERT, and SELECT privileges on all tables in the database partition group being redistributed
DATAACCESS authority

Command syntax


>>-REDISTRIBUTE DATABASE PARTITION GROUP--db-partition-group---->

>--+-| Action |------------------------------------------------------------------------+-->
   '-NOT ROLLFORWARD RECOVERABLE--| Action |--| Not roll-forward recoverable options |-'   

>--●--+----------------------------------------+--●------------->
      |           .-,----------.               |      
      |           V            |     .-ONLY--. |      
      '-TABLE--(----table-name-+--)--+-------+-'      
                                     '-FIRST-'        

>--●--+-------------------------------+--●---------------------->
      |             .-,----------.    |      
      |             V            |    |      
      '-EXCLUDE--(----table-name-+--)-'      

>--●--+------------------------+--●----------------------------><
      '-STOP AT--local-isotime-'      

Action

|--+-+-UNIFORM----------------------+--| Add/Drop DB partition |-+--|
   | '-USING DISTFILE--distfilename-'                            |   
   +-USING TARGETMAP--targetmapfilename--------------------------+   
   +-CONTINUE----------------------------------------------------+   
   '-ABORT-------------------------------------------------------'   

Add/Drop DB partition

|--+--------------------------------------------------+--------->
   |                              .-,------------.    |   
   |                              V              |    |   
   '-ADD--+-DBPARTITIONNUM--+--(----n--+-------+-+--)-'   
          '-DBPARTITIONNUMS-'          '-TO--m-'          

>--+---------------------------------------------------+--------|
   |                               .-,------------.    |   
   |                               V              |    |   
   '-DROP--+-DBPARTITIONNUM--+--(----n--+-------+-+--)-'   
           '-DBPARTITIONNUMS-'          '-TO--m-'          

Not roll-forward recoverable options

                          .-INDEXING MODE REBUILD--.   .-PRECHECK YES--.     
|--●-+----------------+-●-+------------------------+-●-+---------------+-●-->
     '-DATA BUFFER--n-'   '-INDEXING MODE DEFERRED-'   +-PRECHECK NO---+     
                                                       '-PRECHECK ONLY-'     

     .-QUIESCE DATABASE YES-.   .-STATISTICS USE PROFILE-.     
>--●-+----------------------+-●-+------------------------+-●----|
     '-QUIESCE DATABASE NO--'   '-STATISTICS NONE--------'

Command parameters

DATABASE PARTITION GROUP db-partition-group

The name of the database partition group. This one-part name identifies a database partition group described in the SYSCAT.DBPARTITIONGROUPS catalog table. The database partition group cannot currently be undergoing redistribution.

Note: Tables in the IBMCATGROUP and the IBMTEMPGROUP database partition groups cannot be redistributed.

NOT ROLLFORWARD RECOVERABLE

When this option is used, the REDISTRIBUTE DATABASE PARTITION GROUP command is not roll-forward recoverable.

Data is moved in bulk instead of by internal insert and delete operations. This reduces the number of times that a table must be scanned and accessed, which results in better performance.
Log records are no longer required for each of the insert and delete operations. This means that you no longer need to manage large amounts of active log space and log archiving space in your system when performing data redistribution. This is particularly beneficial if, in the past, large active log space and storage requirements forced you to break a single data redistribution operation into multiple smaller redistribution tasks, which might have resulted in even more time required to complete the end-to-end data redistribution operation.
When using the REDISTRIBUTE DATABASE PARTITION GROUP command with the NOT ROLLFORWARD RECOVERABLE option, the redistribute operation uses the INDEXING MODE DEFERRED option for tables that contain XML columns. If a table does not contain an XML column, the redistribute operation uses the indexing mode specified when issuing the command.

When this option is not used, extensive logging of all row movement is performed such that the database can be recovered later in the event of any interruptions, errors, or other business need.

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 database partition. After redistribution, all database partitions in the database partition group have approximately the same number of hash partitions.

USING DISTFILE distfilename

If the distribution of distribution key values is skewed, use this option to achieve a uniform redistribution of data across the database partitions of a database partition group.

Use the distfilename to indicate the current distribution of data across the 32 768 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 distfilename is specified, the utility generates a target distribution map that it uses to redistribute the data across the database partitions in the database partition group as uniformly as possible. After the redistribution, the weight of each database partition in the database partition group is approximately the same (the weight of a database partition is the sum of the weights of all hash partitions that map to that database partition).

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

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

The distfilename should contain 32 768 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 distfilename is not specified, the current directory is used.

USING TARGETMAP targetmapfilename

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

The targetmapfilename should contain 32 768 integers, each representing a valid database partition number. The number on any row maps a hash value to a database partition. This means that if row X contains value Y, then every record with HASHEDVALUE() of X is to be located on database partition Y.

If a database partition, included in the target map, is not in the database partition group, an error is returned. Issue ALTER DATABASE PARTITION GROUP ADD DBPARTITIONNUM statement before running REDISTRIBUTE DATABASE PARTITION GROUP command.

If a database partition, excluded from the target map, is in the database partition group, that database partition will not be included in the partitioning. Such a database partition can be dropped using ALTER DATABASE PARTITION GROUP DROP DBPARTITIONNUM statement either before or after the REDISTRIBUTE DATABASE PARTITION GROUP command.

CONTINUE

Continues a previously failed or stopped REDISTRIBUTE DATABASE PARTITION GROUP operation. If none occurred, an error is returned.

ABORT

Aborts a previously failed or stopped REDISTRIBUTE DATABASE PARTITION GROUP operation. If none occurred, an error is returned.

ADD

DBPARTITIONNUM n

TO m

n or n TO m specifies a list or lists of database partition numbers which are to be added into the database partition group. Any specified partition must not already be defined in the database partition group (SQLSTATE 42728). This is equivalent to executing the ALTER DATABASE PARTITION GROUP statement with ADD DBPARTITIONNUM clause specified.

DBPARTITIONNUMS n

TO m

Note: When a database partition is added using this option, containers for table spaces are based on the containers of the corresponding table space on the lowest numbered existing partition in the database partition group. If this would result in a naming conflict among containers, which could happen if the new partitions are on the same physical machine as existing containers, this option should not be used. Instead, the ALTER DATABASE PARTITION GROUP statement should be used with the WITHOUT TABLESPACES option before issuing the REDISTRIBUTE DATABASE PARTITION GROUP command. Table space containers can then be created manually specifying appropriate names.

DROP

DBPARTITIONNUM n

TO m

n or n TO m specifies a list or lists of database partition numbers which are to be dropped from the database partition group. Any specified partition must already be defined in the database partition group (SQLSTATE 42729). This is equivalent to executing the ALTER DATABASE PARTITION GROUP statement with the DROP DBPARTITIONNUM clause specified.

DBPARTITIONNUMS n

TO m

TABLE tablename

Specifies a table order for redistribution processing.

ONLY: If the table order is followed by the ONLY keyword (which is the default), then, only the specified tables will be redistributed. The remaining tables can be later processed by REDISTRIBUTE CONTINUE commands. This is the default.
FIRST: If the table order is followed by the FIRST keyword, then, the specified tables will be redistributed with the given order and the remaining tables in the database partition group will be redistributed with random order.

EXCLUDE tablename

Specifies tables to omit from redistribution processing. For example, you can temporarily omit a table until you can configure it to meet the requirements for data redistribution. The omitted tables can be later processed by REDISTRIBUTE CONTINUE commands. This command parameter is available in DB2® Version 9.7 Fix Pack 5 and later fix packs.

STOP AT local-isotime

When this option is specified, before beginning data redistribution for each table, the local-isotime is compared with the current local timestamp. If the specified local-isotime is equal to or earlier than the current local timestamp, the utility stops with a warning message. Data redistribution processing of tables in progress at the stop time will complete without interruption. No new data redistribution processing of tables begins. The unprocessed tables can be redistributed using the CONTINUE option. This local-isotime value is specified as a time stamp, a 7-part character string that identifies a combined date and time. The format is yyyy-mm-dd-hh.mm.ss.nnnnnn (year, month, day, hour, minutes, seconds, microseconds) expressed in local time.

DATA BUFFER n

Specifies the number of 4 KB pages to use as buffered space for transferring data within the utility. This command parameter can be used only when the NOT ROLLFORWARD RECOVERABLE parameter is also specified.

If the value specified is lower than the minimum supported value, the minimum value is used and no warning is returned. If a DATA BUFFER value is not specified, an intelligent default is calculated by the utility at runtime at the beginning of processing each table. Specifically, the default is to use 50% of the memory available in the utility heap at the time redistribution of the table begins and to take into account various table properties as well.

This memory is allocated directly from the utility heap, whose size can be modified through the util_heap_sz database configuration parameter. Beginning in version 9.5, the value of the DATA BUFFER parameter of the REDISTRIBUTE DATABASE PARTITION GROUP command can temporarily exceed util_heap_sz if more memory is available in the system.

INDEXING MODE

Specifies how indexes are maintained during redistribution. This command parameter can be used only when the NOT ROLLFORWARD RECOVERABLE parameter is also specified.

Valid values are:

REBUILD: Indexes will be rebuilt from scratch. Indexes do not have to be valid to use this option. As a result of using this option, index pages will be clustered together on disk.
DEFERRED: Redistribute will not attempt to maintain any indexes. Indexes will be marked as needing a refresh. The first access to such indexes might force a rebuild, or indexes might be rebuilt when the database is restarted.
Note: For non-MDC tables, if there are invalid indexes on the tables, the REDISTRIBUTE DATABASE PARTITION GROUP command automatically rebuilds them if you do not specify INDEXING MODE DEFERRED. For an MDC table, even if you specify INDEXING MODE DEFERRED, a composite index that is invalid is rebuilt before table redistribution begins because the utility needs the composite index to process an MDC table.

PRECHECK

Verifies that the database partition group can be redistributed. This command parameter is available in DB2 Version 9.7 Fix Pack 5 and later fix packs. It can be used only when the NOT ROLLFORWARD RECOVERABLE command parameter is also specified.

YES: This is the default value. The redistribution operation begins only if the verification completes successfully. If the verification fails, the command terminates and returns an error message related to the first check that failed.
NO: The redistribution operation begins immediately; no verification occurs.
ONLY: The command terminates after performing the verification; no redistribution occurs. By default it will not quiesce the database. If the QUIESCE DATABASE command parameter was set to YES or defaulted to a value of YES, the database remains quiesced. To restore connectivity to the database, perform the redistribution operation or issue UNQUIESCE DATABASE command.

QUIESCE DATABASE

Specifies to force all users off the database and put it into a quiesced mode. This command parameter is available in DB2 Version 9.7 Fix Pack 5 and later fix packs. It can be used only when the NOT ROLLFORWARD RECOVERABLE command parameter is also specified.

YES: This is the default value. Only users with SYSADM, SYSMAINT, or SYSCTRL authority or users who have been granted QUIESCE_CONNECT authority will be able to access the database or its objects. Once the redistribution completes successfully, the database is unquiesced.
NO: The redistribution operation does not quiesce the database; no users are forced off the database.

For more information, refer to the QUIESCE DATABASE command.

STATISTICS

Specifies that the utility should collect statistics for the tables that have a statistics profile. This command parameter can be used only when the NOT ROLLFORWARD RECOVERABLE parameter is also specified.

Specifying this option is more efficient than separately issuing the RUNSTATS command after the data redistribution is completed.

USE PROFILE: Statistics will be collected for the tables with a statistics profile. For tables without a statistics profile, nothing will be done. This is the default.
NONE: Statistics will not be collected for tables.

Consequences of using the NOT ROLLFORWARD RECOVERABLE option

When the REDISTRIBUTE DATABASE PARTITION GROUP command is issued and the NOT ROLLFORWARD RECOVERABLE option is specified, a minimal logging strategy is used that minimizes the writing of log records for each moved row. This type of logging is important for the usability of the redistribute operation since an approach that fully logs all data movement could, for large systems, require an impractical amount of active and permanent log space and would generally have poorer performance characteristics. It is important, however, for users to be aware that as a result of this minimal logging model, the REDISTRIBUTE DATABASE PARTITION GROUP command is not roll-forward recoverable. This means that any operation that results in the database rolling forward through a redistribute operation results in all tables touched by the redistribution operation being left in the UNAVAILABLE state. Such tables can only be dropped, which means there is no way to recover the data in these tables. This is why, for recoverable databases, the REDISTRIBUTE DATABASE PARTITION GROUP utility when issued with the NOT ROLLFORWARD RECOVERABLE option puts all table spaces it touches into the BACKUP PENDING state, forcing the user to back up all redistributed table spaces at the end of a successful redistribute operation. With a backup taken after the redistribution operation, the user should not have a need to roll-forward through the redistribute operation itself.

There is one important consequence of the redistribute utility's lack of roll-forward recoverability of which the user should be aware: If the user chooses to allow updates to be made against tables in the database (even tables outside the database partition group being redistributed) while the redistribute operation is running, including the period at the end of redistribute where the table spaces touched by redistribute are being backed up by the user, such updates can be lost in the event of a serious failure, for example, a database container is destroyed. The reason that such updates can be lost is that the redistribute operation is not roll-forward recoverable. If it is necessary to restore the database from a backup taken before the redistribution operation, then it will not be possible to roll forward through the logs in order to replay the updates that were made during the redistribution operation without also rolling forward through the redistribute operation which, as was described above, leaves the redistributed tables in the UNAVAILABLE state. Thus, the only thing that can be done in this situation is to restore the database from the backup taken before the redistribution without rolling forward. Then the redistribute operation can be performed again. Unfortunately, all the updates that occurred during the original redistribute operation are lost.

The importance of this point cannot be overemphasized. In order to be certain that there will be no lost updates during a redistribution operation, one of the following must be true:

The user avoids making updates during the operation of the REDISTRIBUTE DATABASE PARTITION GROUP command, including the period after the command finishes where the affected table spaces are being backed up.
The redistribution operation is performed with the QUIESCE DATABASE command parameter set to YES. You must still ensure that any applications or users that are allowed to access the quiesced database are not making updates.
Updates that are applied during the redistribute operation come from a repeatable source, meaning that they can be applied again at any time. For example, if the source of updates is data that is stored in a file and the updates are applied during batch processing, then clearly even in the event of a failure requiring a database restore, the updates would not be lost since they could simply be applied again at any time.

With respect to allowing updates to the database during the redistribution operation, the user must decide whether such updates are appropriate or not for their scenario based on whether or not the updates can be repeated after a database restore, if necessary.

Note: Not every failure during operation of the REDISTRIBUTE DATABASE PARTITION GROUP command results in this problem. In fact, most do not. The REDISTRIBUTE DATABASE PARTITION GROUP command is fully restartable, meaning that if the utility fails in the middle of its work, it can be easily continued or aborted with the CONTINUE or ABORT options. The failures mentioned above are failures that require the user to restore from the backup taken prior to the redistribute operation.

Examples

Redistribute database partition group DBPG_1 by providing the current data distribution through a data distribution file, distfile_for_dbpg_1, and moving data onto two new database partitions, 6 and 7.

REDISTRIBUTE DATABASE PARTITION GROUP DBPG_1 
   USING DISTFILE /home/user1/data/distfile_for_dbpg_1 
   ADD DATABASE PARTITION (6 TO 7)

Redistribute database partition group DBPG_2 such that:

The redistribution is not roll-forward recoverable;
Data is uniformly distributed across hash partitions;
Indexes are rebuilt from scratch;
Statistics are not collected;
180,000 4 KB pages are used as buffered space for transferring the data.

REDISTRIBUTE DATABASE PARTITION GROUP DBPG_2 
	NOT ROLLFORWARD RECOVERABLE 
	UNIFORM 
	INDEXING MODE REBUILD 
	DATA BUFFER 180000
	STATISTICS NONE

In DB2 Version 9.7 Fix Pack 5 and later fix packs, this redistribution operation would also quiesce the database and perform a precheck due to the default values for the QUIESCE DATABASE and PRECHECK command parameters.

Usage notes

Before starting a redistribute operation, ensure that the tables are in normal state and not in "load pending" state or "reorg pending" state. Table states can be checked by using the LOAD QUERY command.
When the NOT ROLLFORWARD RECOVERABLE option is specified and the database is a recoverable database, the first time the utility accesses a table space, it is put into the BACKUP PENDING state. All the tables in that table space will become read-only until the table space is backed-up, which can only be done when all tables in the table space have finished being redistributed.
When a redistribution operation is running, it produces an event log file containing general information about the redistribution operation and information such as the starting and ending time of each table processed. This event log file is written to:
- The homeinst/sqllib/redist directory on Linux and UNIX operating systems, using the following format for subdirectories and file name: database-name.database-partition-group-name.timestamp.log.
- The DB2INSTPROF\instance\redist directory on Windows operating systems (where DB2INSTPROF is the value of the DB2INSTPROF registry variable), using the following format for subdirectories and file name: database-name.database-partition-group-name.timestamp.log.
- The time stamp value is the time when the command was issued.
For more information about the redistribute event log, refer to the "Redistribution event log file" topic.
This utility performs intermittent COMMITs during processing.
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 database partition group 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.
By default, the redistribute utility will update the statistics for those tables that have a statistics profile. For the tables without a statistics profile, it is recommended that you separately update the table and index statistics for these tables by calling the db2Runstats API or by issuing the RUNSTATS command after the redistribute operation has completed.
Database partition groups containing replicated materialized query 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 or created temporary tables in the database partition group.
Options such as INDEXING MODE are ignored on tables, on which they do not apply, without warning. For example, INDEXING MODE will be ignored on tables without indexes.
The REDISTRIBUTE DATABASE PARTITION GROUP command might fail (SQLSTATE 55071) if an add database partition server request is either pending or in progress. This command might also fail (SQLSTATE 55077) if a new database partition server is added online to the instance and not all applications are aware of the new database partition server.

Compatibilities

Tables containing XML columns that use the DB2 Version 9.5 or earlier XML record format cannot be redistributed. Use the ADMIN_MOVE_TABLE stored procedure to migrate the table to the new format.