SET_SWRD_SETTINGS procedure - Create or change redistribute registry

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 identifiers

Field 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

EXECUTE privilege on the SET_SWRD_SETTINGS procedure.

Example

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.