DB2 Version 10.1 for Linux, UNIX, and Windows

sqludrdt API - Redistribute data across a database partition group

Redistributes data across the database partitions in a database partition group. The current data distribution, whether it is uniform or skewed, can be specified. The redistribution algorithm selects the database partitions to be moved based on the current data distribution. This API does not support the NOT ROLLFORWARD RECOVERABLE option of the REDISTRIBUTE DATABASE PARTITION GROUP command.

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

Scope

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

Authorization

One of the following authorities:

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

API include file

sqlutil.h

API and data structure syntax

SQL_API_RC SQL_API_FN
  sqludrdt (
   char * pNodeGroupName,
   char * pTargetPMapFileName,
   char * pDataDistFileName,
   SQL_PDB_NODE_TYPE * pAddList,
   unsigned short AddCount,
   SQL_PDB_NODE_TYPE * pDropList,
   unsigned short DropCount,
   unsigned char DataRedistOption,
   struct sqlca * pSqlca);

SQL_API_RC SQL_API_FN
  sqlgdrdt (
   unsigned short NodeGroupNameLen,
   unsigned short TargetPMapFileNameLen,
   unsigned short DataDistFileNameLen,
   char * pNodeGroupName,
   char * pTargetPMapFileName,
   char * pDataDistFileName,
   SQL_PDB_NODE_TYPE * pAddList,
   unsigned short AddCount,
   SQL_PDB_NODE_TYPE * pDropList,
   unsigned short DropCount,
   unsigned char DataRedistOption,
   struct sqlca * pSqlca);

sqludrdt API parameters

pNodeGroupName

The name of the database partition group to be redistributed.

pTargetPMapFileName

The name of the file that contains the target distribution map. If a directory path is not specified as part of the file name, the current directory is used. This parameter is used when the DataRedistOption value is T. The file should be in character format and contain either 4 096 entries (for a multiple-partition database partition group) or 1 entry (for a single-partition database partition group). Entries in the file indicate node numbers. Entries can be in free format.

pDataDistFileName

The name of the file that contains input distribution information. If a directory path is not specified as part of the file name, the current directory is used. This parameter is used when the DataRedistOption value is U. The file should be in character format and contain 4 096 positive integer entries. Each entry in the file should indicate the weight of the corresponding database partition. The sum of the 4 096 values should be less than or equal to 4 294 967 295.

pAddList

The list of database partitions to add to the database partition group during the data redistribution. Entries in the list must be in the form: SQL_PDB_NODE_TYPE.

AddCount

The number of database partitions to add to the database partition group.

pDropList

The list of database partitions to drop from the database partition group during the data redistribution. Entries in the list must be in the form: SQL_PDB_NODE_TYPE.

DropCount

The number of database partitions to drop from the database partition group.

DataRedistOption

A single character that indicates the type of data redistribution to be done. Possible values are:

U: Specifies to redistribute the database partition group to achieve a balanced distribution. If pDataDistFileName is null, the current data distribution is assumed to be uniform (that is, each database partition represents the same amount of data). If pDataDistFileName parameter is not null, the values in this file are assumed to represent the current data distribution. When the DataRedistOption is U, the pTargetPMapFileName parameter should be null. Database partitions specified in the add list are added, and database partitions specified in the drop list are dropped from the database partition group.
T: Specifies to redistribute the database partition group using the pTargetPMapFileName parameter. For this option, the parameters, pDataDistFileName, pAddList, and pDropList should be null, and both the parameters, AddCount and DropCount must be zero.
C: Specifies to continue a redistribution operation that failed. For this option, the parameters, pTargetPMapFileName, pDataDistFileName, pAddList, and pDropList should be null, and both the parameters, AddCount and DropCount must be zero.
R: Specifies to roll back a redistribution operation that failed. For this option, the parameters, pTargetPMapFileName, pDataDistFileName, pAddList, and pDropList should be null, and both the parameters, AddCount and DropCount must be zero.

pSqlca

Output. A pointer to the sqlca structure.

sqlgdrdt API-specific parameters

NodeGroupNameLen: The length of the name of the database partition group.
TargetPMapFileNameLen: The length of the name of the target distribution map file.
DataDistFileNameLen: The length of the name of the data distribution file.

Usage notes

When a redistribution operation is done, a message file is written to:

The $HOME/sqllib/redist directory on Linux and UNIX operating systems, using the following format for subdirectories and file name: database-name.nodegroup-name.timestamp.
The $HOME\sqllib\redist\ directory on the Windows operating system, using the following format for subdirectories and file name: database-name\first-eight-characters-of-the-nodegroup-name\date\time.

The time stamp value is the time at which the API was called.

This utility performs intermittent COMMITs during processing.

Use the ALTER DATABASE PARTITION GROUP statement to add database partitions to a database partition group. This statement permits one to define the containers for the table spaces associated with the database partition group.

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.

It is also recommended to update statistics by issuing the db2Runstats API after the redistribute database partition group operation has completed.

Database partition groups containing replicated summary tables or tables defined with the DATA CAPTURE CHANGES clause cannot be redistributed.

Redistribution is not allowed if there are user temporary table spaces with existing declared temporary tables in the database partition group.

REXX API syntax

This API can be called from REXX through the SQLDB2 interface.