For database ingestion and replication initial load jobs and the unload phase of combined load jobs, you can enable auto-tuning of some key parameters to optimize job performance on the source side and target side. The jobs can use a Db2 for i, Db2 for LUW, Db2 for z/OS, MySQL, Oracle, PostgreSQL, SAP HANA, SQL Server, or Teradata source type with any supported target type.
Auto-tuning is based on performance and system metrics automatically collected from your environment, such as network and database latency, row counts, table sizes, CPU cores, and memory usage. It’s also based on application-specific metrics such as JVM heap allocation and task capacity. These metrics are used collectively to make dynamic tuning adjustments for optimal efficiency and resource use.
Collecting some of the system and workload metrics requires additional database user privileges. Specifically, retrieving allocated table size from source tables requires access grants specific to the source type. For detailed information about the required privileges, see Sources - preparation and usage. If those grants are not available, the auto-tuner uses a fallback method. It runs a standard COUNT(*) query to obtain the row count, then derives an estimated row size and multiplies the two values to approximate the table size. Without additional user privileges, the table-size estimate might be less precise.
To enable auto-tuning for a task, select the Auto-Tune option on the final Let's Go! page of the task configuration wizard.
You can override any value set by auto-tuning by adding a custom property in the Custom Properties section of the advanced properties on the Task Details or Let's Go! pages in the task configuration wizard, depending on the property. To add a property, click the + icon to add a row. In the Property Name field, select a property from the list and enter the value, or select the Custom option and manually enter both the property name and value.
Auto-tuning automatically optimizes settings for the following properties:
•Partitioning properties for improved data extraction performance at the source:
- Enable Partitioning. Controls whether to use multiple partitions to query for the source data to be unloaded in parallel.
- Unload JDBC Partitioning Technique. The type of partitioning technique to use for unloading source data. Options are uniform or heuristic.
- Unload Source Partition Count. The total number of partitions to use for concurrently reading source data in parallel.
- Unload Source Max Parallel Partitions. The maximum number of partition reader threads that can be used to query the source for data in parallel.
•Multiple-distributor properties for enhancing parallelism and throughput at the target side:
- Writer Unload Multiple Distributors. Controls whether multiple distributor threads can run in parallel to perform work such as uploading data files to staging areas and flushing data to the target.
- Writer Distributor Count. The number of distributors that can run on separate threads in parallel for event distribution when writing to the target.
•Thread-count properties for optimizing concurrency and resource utilization:
- Unload Help Thread Count. The number of unload helper threads that can be used concurrently to convert unloaded raw events to DML events that can be passed to the writer. Increasing the thread count can help unload helpers keep pace with high data read rates.
- Writer Helper Thread Count. The number of writer helper threads that can run in parallel to convert incoming data to the target output format. This setting can improve the write efficiency of payload processing.
•In-memory pipeline properties for reliable processing across tables of varying sizes:
- ringBufferSize. The number of slots in the internal pipeline ring buffer that connects source readers to the writer. The auto-tuned value selects a slot count sized to each table's profile, reducing the risk of failures on wide tables and avoiding unnecessary memory use on narrow ones. To override the auto-tuned value, manually enter this property in the Custom Properties section of the Let's Go! page. Valid values are integers from 1 to 2,147,483,647. The value is rounded up to the next power of 2 (for example, 100 becomes 128). Default is 128.
- unloadEventBatchSize. The number of rows packed into each ring buffer slot. This value is automatically tuned together with ringBufferSize so that peak in-flight payload stays near the configured size target, within the allowed minimum and maximum bounds. To override the auto-tuned value, manually enter this property in the Custom Properties section of the source page in the task configuration wizard. Valid values are integers from 1 to 2,147,483,647. Default is 1000.
•Writer output properties for efficient data writing under varying workload conditions:
- Number of Rows in Output File. The maximum number of rows that the writer batches before flushing to the target. Auto-tuning adjusts this value based on observed target latency to improve throughput.
•Snowflake property:
- snowflakeCompression. For Snowflake targets, a compression option to pass to Snowflake for creating the internal stage that stores data files. Options are: Auto, None, or a specific compression type. For information about Snowflake compression options, see the CREATE STAGE command > COMPRESSION option in the Snowflake documentation. To override the auto-tuned value, manually enter this property in the Custom Properties section of the target page in the task configuration wizard.
Evaluate-and-stop mode
To preview the Auto-Tune recommendations without moving any data, you can run auto-tuning in evaluate-and-stop mode. In this mode, auto-tuning collects metrics and runs a full tuning evaluation, logs the recommended values for all components, and then stops the task gracefully before the unload and load execution begins. The task exits with a SUCCESS status.
Use this mode to validate the auto-tuning decisions against a real task's metrics, compare recommended values across different source and target configurations, and identify regressions in the tuning algorithm without incurring the cost of a full data-movement run.
To enable the evaluate-and-stop mode, select the Auto-tune checkbox and set the enableAutoTuningEvaluateAndStop property to true. Add this property in the Custom Properties section of the Let's Go! page in the task configuration wizard by selecting the Custom option and manually entering the property name and value. The default value is false.
Note:
This mode applies to initial load jobs only. If the property is set on a CDC job or any phase of a combined job, the task logs a warning and continues its normal run.