DB2 10.5 for Linux, UNIX, and Windows

Redistributing database partition groups using the STEPWISE_REDISTRIBUTE_DBPG procedure

Data redistribution can be performed using built-in procedures.

Procedure

To redistribute a database partition group using the STEPWISE_REDISTRIBUTE_DBPG procedure:

Analyze the database partition group regarding log space availability and data skew using the ANALYZE_LOG_SPACE procedure.
The ANALYZE_LOG_SPACE procedure returns a result set (an open cursor) of the log space analysis results, containing fields for each of the database partitions of the given database partition group.
Create a data distribution file for a given table using the GENERATE_DISTFILE procedure.
The GENERATE_DISTFILE procedure generates a data distribution file for the given table and saves it using the provided file name.
Create and report the content of a stepwise redistribution plan for the database partition group using the STEPWISE_REDISTRIBUTE_DBPG procedure.
Create a data distribution file for a given table using the GET_SWRD_SETTINGS and SET_SWRD_SETTINGS procedures.
The GET_SWRD_SETTINGS procedure reads the existing redistribute registry records for the given database partition group.

The SET_SWRD_SETTINGS procedure creates or makes 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.
Redistribute the database partition group according to the plan using the STEPWISE_REDISTRIBUTE_DBPG procedure.
The STEPWISE_REDISTRIBUTE_DBPG procedure redistributes part of the database partition group according to the input and the setting file.

Example

The following is an example of a CLP script on AIX®:

# -------------------------------------------------------------------------------------
# Set the database you wish to connect to
# -------------------------------------------------------------------------------------
dbName="SAMPLE"

# -------------------------------------------------------------------------------------
# Set the target database partition group name
# -------------------------------------------------------------------------------------
dbpgName="IBMDEFAULTGROUP"

# -------------------------------------------------------------------------------------
# Specify the table name and schema
# -------------------------------------------------------------------------------------
tbSchema="$USER"
tbName="STAFF"

# -------------------------------------------------------------------------------------
# Specify the name of the data distribution file
# -------------------------------------------------------------------------------------
distFile="$HOME/sqllib/function/$dbName.IBMDEFAULTGROUP_swrdData.dst"

export DB2INSTANCE=$USER
export DB2COMM=TCPIP

# -------------------------------------------------------------------------------------
# Invoke call statements in clp
# -------------------------------------------------------------------------------------
db2start
db2 -v "connect to $dbName"

# -------------------------------------------------------------------------------------
# Analysing the effect of adding a database partition without applying the changes - a 'what if'
# hypothetical analysis
#
# - In the following case, the hypothesis is adding database partition 40, 50 and 60 to the
#   database partition group, and for database partitions 10,20,30,40,50,60, using a respective 
#   target ratio of 1:2:1:2:1:2.
#
# NOTE: in this example only partitions 10, 20 and 30 actually exist in the database
#       partition group
# -------------------------------------------------------------------------------------
db2 -v "call sysproc.analyze_log_space('$dbpgName', '$tbSchema', '$tbName', 2, ' ', 
'A', '40,50,60', '10,20,30,40,50,60', '1,2,1,2,1,2')"

# -------------------------------------------------------------------------------------
# Analysing the effect of dropping a database partition without applying the changes 
#
# - In the following case, the hypothesis is dropping database partition 30 from the database
#   partition group, and redistributing the data in database partitions 10 and 20 using a
#   respective target ratio of 1 : 1
#
# NOTE: In this example all database partitions 10, 20 and 30 should exist in the database
#       partition group
# -------------------------------------------------------------------------------------
db2 -v "call sysproc.analyze_log_space('$dbpgName', '$tbSchema', '$tbName', 2, ' ', 
'D', '30', '10,20','1,1')"

# -------------------------------------------------------------------------------------
# Generate a data distribution file to be used by the redistribute process
# -------------------------------------------------------------------------------------
db2 -v "call sysproc.generate_distfile('$tbSchema', '$tbName', '$distFile')"

# -------------------------------------------------------------------------------------
# Write a step wise redistribution plan into a registry 
#
# Setting the 10th parameter to 1, may 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.
# -------------------------------------------------------------------------------------
db2 -v "call sysproc.set_swrd_settings('$dbpgName', 255, 0, ' ', '$distFile', 1000, 
12, 2, 1, 0, '10,20,30', '50,50,50')"

# -------------------------------------------------------------------------------------
# Report the content of the step wise redistribution plan for the given database 
# partition group.
# -------------------------------------------------------------------------------------
db2 -v "call sysproc.get_swrd_settings('$dbpgName', 255, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

# -------------------------------------------------------------------------------------
# Redistribute the database partition group "dbpgName" according to the redistribution 
# plan stored in the registry by set_swrd_settings. It starting with step 3 and
# redistributes the data until 2 steps in the redistribution plan are completed.
# -------------------------------------------------------------------------------------
db2 -v "call sysproc.stepwise_redistribute_dbpg('$dbpgName', 3, 2)"