Technical Blog Post
Abstract
100 Tech Tips: #78: Improve Redistribute Performance
Body
While running redistribute in a DPF environment, the following suggestions may help improve redistribute performance
Starting DB2 V9.5 FP1 and V9.7 GA, the "NOT ROLLFORWARD RECOVERABLE" parameter was introduced. With this option logging is minimal because the data is moved in bulk instead of by internal insert and delete operations which improves performance. (But please consider recovery implications as there is minimal logging.)
Some other options to help performance would be to tune the following parameters:
DATA BUFFER:
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.
Set this parameter to at least 100000 (approximately 400 MB). If you have additional memory then set it to a value of 180000. 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. (By default it is 50% of the memory available in the utility heap at the time redistribution)
UTIL_HEAP_SZ:
The util_heap_sz is critical to the data movement processing between database partitions – allocate as much memory as possible to util_heap_sz for the duration of the redistribution (If DATA BUFFER parameter is set, set util_heap_sz to about 10% greater to that of DATA BUFFER value. If DATA BUFFER is not set, set util_heap_sz to a very high value, such as 200000 or greater depending on the available memory)
SORTHEAP:
Sufficient sortheap is required, if index rebuild is done as part of the redistribution. Set to a value between 10000 and 20000
SHEAPTHRES:
This parameter is an instance-wide soft limit on the total amount of memory that can be consumed by private sorts at any given time. This parameter should be set to at least two times the largest sortheap defined for any database within the instance.
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
UID
ibm11141798