Redistributes data across
the 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:
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 rollforward 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 rollforward 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 rollforward 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.
- 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:
10223
1345
112000
0
100
...
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
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.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.
- Data redistribution might create table spaces for all new database
partitions if the ADD DBPARTITIONNUMS parameter
is specified.
- 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
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.
- 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.
- 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. 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 and non-ITC
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 or ITC 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 or ITC table.
- PRECHECK
- Verifies that the database partition group can be redistributed.
This command parameter can be used only when the NOT ROLLFORWARD
RECOVERABLE 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 can be used only when the NOT
ROLLFORWARD RECOVERABLE 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.
Examples
Redistribute database partition
group DBPG_1 by providing the current data distribution through a
data distribution file,
distfile_for_dbpg_1.
Move the 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 rollforward 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
This redistribution operation also quiesces
the database and performs a precheck due to the default values for
the
QUIESCE DATABASE and
PRECHECK command
parameters.
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.