The SET_SWRD_SETTINGS procedure creates
or make changes to the redistribute registry.
If the registry
does not exist, it creates it and add records into it. If the registry
already exists, it uses overwriteSpec to
identify which of the field values need to be overwritten. The overwriteSpec field enables this function to take
NULL inputs for the fields that do not need to be updated.
Syntax
>>-SET_SWRD_SETTINGS--(--dbpgName--,--overwriteSpec--,---------->
>--redistMethod--,--pMapFile--,--distFile --,--stepSize --,----->
>--totalSteps --,--stageSize --,--nextStep --,------------------>
>--processState --,--pNumber --,--pWeight --)------------------><
The schema is SYSPROC.
Procedure parameters
- dbpgName
- An input argument of type VARCHAR(128) that specifies the database
partition group name against which the redistribute process is to
run.
- overwriteSpec
- Bitwise field identifier(s) from Table 1 indicating the target fields
to be written or overwritten into the redistribute settings registry.
Table 1. Bitwise field identifiersField Name |
Hexadecimal value |
Decimal value |
REDIST_METHOD |
0x0001<<0 |
1 |
REDIST_PMAP_FILE |
0x0001<<1 |
2 |
REDIST_DIST_FILE |
0x0001<<2 |
4 |
REDIST_STEP_SIZE |
0x0001<<3 |
8 |
REDIST_NUM_STEPS |
0x0001<<4 |
16 |
REDIST_STAGE_SIZE |
0x0001<<5 |
32 |
REDIST_NEXT_STEP |
0x0001<<6 |
64 |
REDIST_PROCESS_STATE |
0x0001<<7 |
128 |
REDIST_PWEIGHT_START_NODE |
0x0001<<8 |
256 |
REDIST_PWEIGHT |
0x0001<<9 |
512 |
- redistMethod
- An input argument of type SMALLINT that specifies whether the
redistribute is to run using the data distribution file or the target
distribution map. The two valid input values are:
- 2: indicate that the redistribute process will work with a data
distribution file as input.
- 3: indicate that the redistribute process will work with a target
distribution map as input.
- pMapFile
- An input argument of type VARCHAR (255) that specifies the full
path file name of the target distribution map on the database server.
- distFile
- An input argument of type VARCHAR (255) that specifies the full
path file name of the data distribution file on the database server..
- stepSize
- An input argument of type BIGINT that specifies the maximum number
of rows that can be moved before a commit must be called to prevent
a log full situation. The number can be changed in each redistribution
step. The value "-2" can be used for stepSize to indicate that the number is unlimited.
- totalSteps
- An input argument of type SMALLINT that specifies the number of
steps it takes to completely redistribute the given database partition
group. The value "-2" can be used totalSteps to indicate that the number is unlimited.
- stageSize
- An input argument of type SMALLINT that specifies the number of
steps to be run consecutively.
- nextStep
- An input argument of type SMALLINT that specifies the index separating
which steps have been completed, and what still needs to be run.
- processState
- An input argument of type SMALLINT that indicates whether or not
the redistribute process will be stopped at the next check point.
A check point is placed at beginning of each redistribute step. If
this argument is set to 1, the step will not start; if the value is
0, the step will proceed.
- pNumber
- An input argument of type VARCHAR (6000) that can contain a list
of comma-separated database partition numbers in a string format.
These partition numbers can be either the database partitions that
are currently used by the database partition group, or the ones to
be added or dropped. The sequence and the count of these partition
numbers correspond to the target partition weight returned by the pWeight variable. Each database partition number
is between 0 and 999, and there are no spaces are allowed in the string.
- pWeight
- An input argument of type VARCHAR (6000) that can contain a comma-separated
string of all the database partition weights the user has specified,
corresponding to the database partition numbers in the pNumber string. Each database partition weight is a
number between 0 and 32767, and no spaces are allowed in the string.
Authorization
One of the following authorities is required to execute the routine:
- EXECUTE privilege on the routine
- DATAACCESS authority
- DBADM authority
- SQLADM authority
Default PUBLIC privilege
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC
when the procedure is automatically created.
Example
Write a step wise redistribution
plan into a registry. Setting
processState to 1, might cause a currently running step wise redistribute stored
procedure to complete the current step and stop, until this parameter
is reset to 0, and the redistribute stored procedure is called again.
CALL SYSPROC.SET_SWRD_SETTINGS('IBMDEFAULTGROUP', 255, 0, ' ',
'$HOME/sqllib/function/TEST.IBMDEFAULTGROUP_swrdData.dst', 1000,
12, 2, 1, 0, '10,20,30', '50,50,50')
Usage notes
The redistribute stored procedures
and functions work only in partitioned database environments, where
a distribution key has been defined for each table.