IBM encourages clients to use a combination of batchsize and transaction size parameters for improved performance
Specifies the number of consolidated records to include in a batch
Setting batchsize to 0 may help identify failing records
By default, the batchsize value is set to 100. If the stagingProp utility fails, a batch size of 100 is not very useful in determining the record causing the failure because the error message may not point to the actual failing record because there are 100 records in the failing batch. If stagingProp fails, we recommend clients to change batchsize to zero. Setting -batchsize 0 turns off JDBC batching and might help you identify the exact records that are causing errors.
Specifies number of changes before a commit is done to the production database
Each transaction consists of multiple batches which are committed when transaction count is reached or exceeded
If you do not specify this parameter, change logs are committed as a single transaction
This diagram visualizes the relationship between the batchsize and transaction parameters.
Batchsize and Transaction Relationship (click image to enlarge)
For example, if transaction size is set to 100 and batchsize is 30, changes to production are committed every 4 batches.
Recommendations for batchsize and transaction size parameters
The recommended ratio for batchsize to transaction size is 1:10. To resolve general performance issues,consider using larger values for batchsize and transaction size. The optimal values for IBM internal testing were 100,000 and 1 million for the batchsize and transaction parameters respectively (based on internal testing). JVM heap may need to be tweaked if a large batchsize value is used.
If stagingProp is taking too long, consider using the -batchsize or -transaction parameters to process LESS data at once. If you are propagating too many rows each time, consider running stagingprop at a more frequent interval, which would require less data per propagation. However, keep in mind that the larger the batch size, the bigger the heap required to hold the SQL statements in memory before a commit is performed.