ETL Tuning
When you configure the contact and response history module, the module uses a background Extract, Transform, Load (ETL) process to move data from the runtime staging tables to the Campaign contact and response history tables.
This section describes the optional configuration settings you might want to change in IBM® Interact to improve the performance of the ETL tool. You might not need to modify any of these configuration parameters from their default settings; however, if you do, follow the guidelines here and in the IBM® Interact Administrator's Guide to modify the tool's performance.
All of the properties described here are found in Campaign configuration properties, in Campaign | partitions | partition[n] | Interact | contactAndResponseHistTracking.
Configuration Property
Value and Description
 
processSleepIntervalInMinutes
The number of minutes the Interact contact and response history module waits between copying data from the Interact runtime staging tables to the Campaign contact and response history tables. The default value is 60.
 
purgeOrphanResponseThresholdInMinutes
This property determines how long Interact waits before purging responses that have no corresponding contact (also known as "orphaned responses"). The default is 180; however, for processing many records, the delay between processing contacts and responses may be greater, and you would therefore increase this value to prevent responses from being purged too quickly.
 
maxJDBCInsertBatchSize
Out of the total number of records that the contact and response history module processes in one iteration, this is the maximum number of records of a JDBC batch to process (and assemble together in a batch) before committing the query into the Campaign system tables The default value is 1000.
Because this value works together with the maxJDBCFetchBatchSize property, you might need to increase this value if that property also increased significantly. For example, if you set maxJDBCFetchBatchSize to 2,500,000, you might increase this value to 10,000 to handle the increase in records.
Note that memory requirements increase as you increase this value; a setting of 10,000 for this property is a good upper-limit due to memory demands.
 
maxJDBCFetchBatchSize
Determines the maximum number of records to fetch from the staging database for an ETL batch processing operation. The default value is 1000, but to tune the performance of the contact and response history module, make sure to set this value to a number greater than the number of contact history records generally processed each day.
This property is used together with maxJDBCFetchChunkSize and maxJDBCInsertBatchSize to determine how the records are processed. For example, suppose the values were set as shown here:
*
maxJDBCFetchBatchSize: 30000
*
maxJDBCFetchChunkSize: 1000
*
maxJDBCInsertBatchSize: 1000
In this example, 30,000 records are fetched (or the total number of records if there are under 30,000). Then, the contact and response history module loops through that 30,000 records, processing 1,000 at a time, so that 1,000 records are marked in the staging tables, and 1,000 are inserted into the detail contact history table.
 
maxJDBCFetchChunkSize
Determines the maximum number of records in a JDBC chunk (from a total of up to maxJDBCFetchBatchSize records) to process with each pass The default value is 1000. In some cases, you might be able to improve performance by increasing this value above the MaxJDBCInsertBatchSize property value.
 
deleteProcessedRecords
This property, which specifies whether to retain contact and response history records after they are processed, is set to YES by default. Changing this value can give you more control over the data flow within the ETL process and affect performance (by delaying the purging of these records until a later time of your determination); however, you must be knowledgeable to handle the maintenance of these records manually to be sure that they are removed at appropriate times. Use caution when modifying this setting.
 
fetchSize
Providing a value for the JDBC fetchSize can improve performance for large batches of records, but the tradeoff for improved networking performance is the impact of larger fetch sizes on memory usage. See the description of this configuration property in the IBM® Interact Administrator's Guide for more information about adjusting this setting.
For detailed descriptions of each of the configuration properties described here, see the online help for that configuration page, or see the IBM® Interact Administrator's Guide.
Copyright IBM Corporation 2014. All Rights Reserved.