Multi-threaded bulk load accelerates the initial replication of large source tables into the target database. Instead of processing an entire table sequentially through a single process, Pro2 divides the table into equal-sized work units (ranges) and processes them concurrently across multiple worker processes.

This feature enhances the existing mass replication (bulk load) operation. The loaded data is identical to what the single-threaded bulk load produces, only the execution strategy is different.

Multi-threaded bulk load works for both LAN and WAN (application server) deployments and requires no changes to your replication policies, triggers, or downstream applications.

For steps to run a bulk load from the UI or command line, see Bulk load with Pro2.

Key benefits

Multi-threaded bulk load provides the following benefits:

  • Faster initial loads—Large tables with millions to billions of rows load in a fraction of the time required by single-threaded loads. Throughput scales with the number of worker threads your hardware can support.

  • Better hardware utilization—A single process no longer bottlenecks CPU cores, disk bandwidth, and network throughput on the target server.

  • Automatic crash recovery—If a worker fails or the server restarts mid-load, the job resumes from where it stopped. Completed work is never repeated, and partially-loaded ranges restart from a recent checkpoint.

  • Live progress visibility—Each running table reports per-worker progress and a global record count that updates as ranges complete.

  • LAN and WAN support—Regardless of whether the source database is local or accessed through an application server, this feature accelerates loads.

  • No application changes—Multi-threaded bulk load is a configuration option, which does not affect your existing replication setup.

How multi-threaded bulk load works

Multi-threaded bulk load uses a producer-consumer architecture:
  1. Plan the work (Producer phase)—Before any worker starts, Pro2 performs a lightweight scan of the source table, reading only record identifiers (ROWIDs) instead of full record data. The scan divides the table into equal-sized ranges, for example, 100,000 rows per range, and writes each range as a queued work item in the Pro2 control database.
    Note: By default, each range contains 100,000 rows. You can override thisvalue using the MULTI_BULK_RANGE_BATCH_SIZE property.
  2. Dispatch workers (Consumer phase)—The configured number of worker processes launch in parallel. Each worker:

    1. Claims the next available range from the queue.

    2. Loads the assigned range into the target database by using the existing per-table replication procedure.

    3. Marks the range as complete and updates the running record total.

    4. Repeats until no more ranges remain.

  3. Finish and report—Workers never overlap on the same range. The queue is the authoritative source of truth for what has been completed and what remains. After every range is loaded, the last worker writes a summary to the run log, and the table status changes to COMPLETE.

LAN vs. WAN

The feature works identically in both deployment models:
  • LAN—The producer and workers read the source table directly through a local database connection.
  • WAN—The source-table scan runs on the application server co-located with the source database. Only compact range descriptors travel over WAN. Row data is fetched range-by-range as workers consume the queue.
The monitoring experience, recovery behavior, and configuration are the same in both modes.

Range and table states

Pro2 tracks the progress of a multi-threaded bulk load by storing state values in the Repl_Control table. Two sets of records are maintained:
  • One per range or queue records
  • One per table or status records

Range states are stored in Repl_Control records, where GroupID is BULKQUEUE. Each record represents one ROWID range for a specific table. The state transitions are as follows:

State Value Description
Fresh (empty) The range has been queued by the producer but has not yet been claimed by a worker.
In process IN-PROCESS A worker has claimed the range and is actively loading it into the target database.
Complete COMPLETE The worker has successfully loaded the range. The record is retained for reporting and crash-recovery purposes.

Table states are stored in a separate Repl_Control record, where GroupID is BULKCOPY. This record drives what the Bulk-Copy Results page displays for each table. The state transitions are as follows:

State Value Description
In process IN-PROCESS Set by the first worker to claim a range for the table. Indicates that at least one worker is actively loading data.
Complete COMPLETE Set by the last worker to finish all ranges for the table. Indicates that the full table has been loaded successfully.
Note: If a range fails, Pro2 logs the error to the worker error log file ending in _Err.log and moves on to the next range. Because the range record is not marked with a failed state in the database and the error is captured exclusively in the log file, you check the MASS_LOAD_LOG_DIR folder for _Err.log files.

When a bulk load restarts after an interruption, the populateQueue.p producer inspects existing queue records before generating new ranges. If IN-PROCESS records are found, they are reset to the fresh (empty) state so that workers can retry them during the next run. Their per-range checkpoint is preserved, allowing the generated procedure to resume from the last committed row within the range rather than restarting from the beginning of the range.

Crash recovery and resilience

Multi-threaded bulk loading ensures that no completed work is lost in the event of a failure:
  • If a single worker process is terminated, only the in-flight range is affected. All ranges already completed by that worker remain committed in the target database.

  • When you relaunch bulk load, Pro2 automatically detects the partial run and resumes exactly where the previous run stopped. It does not restart the whole table.

  • If a range is interrupted mid-flight, it resumes from a recent checkpoint close to where it stopped, rather than restarting from the beginning of the range.
  • You do not need to perform any manual cleanup before restarting an interrupted bulk load.

Configuration

Multi-threaded bulk load is controlled by the following parameters:
Parameter Description Default Valid range
RangeMode

Enables multi-threaded (range-based) mode instead of legacy single-threaded mode.

 NO YES or NO
MProcThreads The number of concurrent worker processes per table. 9 1-9
MULTI_BULK_RANGE_BATCH_SIZE The number of source records grouped into one work unit (range). This parameter is set in Pro2 Properties. 100,000 Any positive integer
MASS_LOAD_LOG_DIR

The directory where progress and error logs are written. This parameter is set in Pro2 Properties.

 repl_mproclog Any valid path
Command-line example 
-param "DB=sports;TableInc=Customer,Order;TableExc=Benefits;RunFlag=A; 
ForThread=0;MaxRecordCount=0;ResetBulkload=yes;ResetInProcess=yes; 
MProcThreads=5;RangeMode=yes"
This example configuration:
  • Targets the sports database.
  • Includes the Customer and Order tables.
  • Excludes the Benefits table.
  • Launches 5 worker threads per table.
  • Enables multi-threaded bulk load (Thread Mode: Multi).
Note: The MaxRecordCount parameter is ignored when RangeMode=YES. In multi-threaded bulk load, the full table is always processed.

Thread tuning guidelines

Select the right number of worker threads depending on your environment:
Table size Avaliable CPU cores Recommended threads
Less than 100,000 rows Any 1 (use single-threaded mode)
100,000–1,000,000 rows 4 cores 2–3
100,000–1,000,000 rows 8+ cores 3–5
1,000,000–10,000,000 rows 8+ cores 5–7
More than 10,000,000 rows 8+ cores 7–9

Here are the general thread tuning recommendations:

  • Start with a thread count equal to roughly half of the available CPU cores on your target server.

  • Do not exceed the available CPU cores minus 2. This way, you leave headroom for the OS, database broker, and producer process.

  • If bulk-loading multiple tables concurrently, consider reducing the per-table threads. This way, all tables can share the 9-slot thread pool.

  • Monitor disk and network utilization. Adding threads beyond the point of I/O saturation does not improve throughput.
For batch-size tuning, adjust MULTI_BULK_RANGE_BATCH_SIZE alongside thread count:
  • For smaller batches, for example, 50,000, create more ranges for better distribution across threads, but introduce more overhead per range.
  • For larger batches, for example, 200,000 or more, create fewer ranges with less overhead, but risk uneven work distribution.
  • The total number of ranges (Total Records / Batch Size) should be at least 2–3 times the thread count, so that all workers remain busy throughout the load.

Constraints and limitations

Before using multi-threaded bulk load, be aware of the following constraints and limitations that affect execution, configuration, and upgrade behavior:

  • The absolute maximum number of worker threads is 9 per bulk-load run.

  • Multi-threaded bulk load processes tables in primary index order. Ensure that the source table has a suitable primary index defined.

  • The MaxRecordCount (row limit) option is not supported when Thread Mode is set to Multi. If both RangeMode=YES and MaxRecordCount > 0 are specified, the row limit is ignored and a warning is logged.

  • All tables within a single bulk-load run share the same global 9-slot thread pool. When bulk-loading many tables simultaneously, plan thread allocation accordingly.

  • After upgrading to Pro2 6.5.3, you must regenerate per-table bulk load procedures (by selecting the Generate option in the Pro2 web UI or using the GEN_PROCS command-line mode), before setting Thread Mode to Multi. The generated procedures now accept additional parameters required by the multi-threaded architecture.

  • Multi-threaded bulk-load using the tmpl_mreplproc_oracle.p Oracle default template requires the ORACLE_NATIVE_ROWID parameter to be set to YES. This limitation applies only when the target type is Oracle. As a workaround, if your configuration uses ORACLE_NATIVE_ROWID=NO, you can use the tmpl_mreplproc_restart-auto-push.p template to perform multi-threaded bulk-load operations.

When to use multi-threaded bulk load

Use multi-threaded bulk load when:

  • You are performing the initial replication (mass replication) of one or more large tables into the target database.

  • The tables have 100,000 or more rows. Smaller tables do not benefit from parallelism due to the overhead of range management.

  • Your target server has multiple CPU cores and sufficient I/O bandwidth to support concurrent writes.

  • You want faster completion times for database migrations, disaster-recovery seeding, or new target-environment setup.

  • You need resilient, resumable loads that can tolerate server restarts or worker failures without losing progress.

Do not use multi-threaded bulk load when:
  • Tables are small (under 100,000 rows).
  • The target database is on extremely constrained storage, where parallel writes would cause contention.
  • You are running ongoing change-data replication (the regular replication processor handles that independently).
For frequently asked questions about multi-threaded bulk load, see Pro2 FAQs.