OpenEdge provides support for a feature called Change Data Capture (CDC). CDC is an OpenEdge RDBMS feature that identifies and captures data that changed in tables of a source database, as a result of CREATE, UPDATE, and DELETE (CUD) operations.

CDC is an industry term that describes the process of duplicating subsets of OLTP data in an external data source with a relatively up-to-date version of relational data. The OpenEdge implementation of CDC provides a flexible and scalable capture process to facilitate the data extraction, transformation, and eventually the loading of the data to an external data source.

Pro2 leverages two methods to implement CDC: CDC Thread and Replication-CDC Thread.

The first method, CDC Thread, is the standard implementation of this feature which replaces ABL triggers with CDC. The Pro2 standard CDC implementation supports a feature called thread#0. When this feature is used, the CDC Thread converts all CDC_change_tracking records to ReplQueue records, regardless of thread that the table is mapped to. This provides the convenience of having a single CDC Thread that converts all CDC records to replication queue records. The CDC replication process is faster than using ABL triggers and can be managed online.

The second method, Replication-CDC Thread, is a faster more efficient method of CDC replication. This method is achieved from bypassing the conversion of CDC records to replication queue records by writing changes directly from the source CDC database to the target database. This results in a replication performance speed increase and a decrease in lag.

Each type has advantages over the other. Replication-CDC Thread has the aforementioned speed, while CDC Thread can use compression and thread splitting. For example, CDC Thread can take multiple updates to a single record with the same rowid and compress it down into a single record to be processed. In contrast, Replication-CDC Thread processes a record for each transaction that occurs. The method that you choose depends on your business needs.

Pro2 use only Level 0 policies. Pro2 creates policies with the suffix _pro2 so that it is easily recognized by the application, for example, tablename_pro2. If you create custom policies, ensure that _pro2 is appended to each policy create for use with Pro2. Moreover, Pro2 does not replicate any changes that are generated from non-Pro2 policies. To determine whether a record is processed, Pro2 uses the _user-misc field in the _change_tracking-table. After the CDC record is processed, it either assigns the _user-misc value to applied, or it deletes the CDC record based on disposition value.

Limitations

To avoid read/write conflicts between replication thread types, there are restrictions around which threads can run simultaneously.

  • A CDC Thread does not start if any of the same Replication-CDC thread(s) are running. For example, CDC Thread thread#1 and Replication-CDC thread#1 cannot run simultaneously. Likewise, Replication thread#1 and Replication-CDC thread#1 cannot run simultaneously.
  • By design Replication-CDC cannot use split threads or queue compression.
  • Second pass replication is not supported with Replication-CDC threads.

If you encounter an error derived from one of the above restrictions, you can delete or modify any threads from Total Threads watch-box on the Pro2 dashboard. In the Total Threads watch-box, select the problemed thread and click Delete.

Choose between standard CDC or Replication-CDC

Assume a scenario where a you are running standard CDC replication with 5 active threads. Your replication threads 1-4 are configured normally, thread 5 is a 3-way split thread, and 2nd pass replication is not in play. The thread number list would be 1,2,3,4,5,51 and 52. At this point, LAN or WAN is irrelevant to the scenario.

Split threads are not supported for threads of type Replication-CDC. This means the Replication-CDC thread type is not an option for replication of tables mapped to thread 5.

Threads 1-4 are potential candidates for flipping the thread type. For this example, we will pick thread 4 to convert to a Replication-CDC thread type.

On the web user interface, add a new thread 4 with type Replication-CDC. There is no need to modify the mapping, and it does not matter if single or multiple tables replicate under thread 4.

Ensure that CDC is not running for thread 4.

Note: If you are running one CDC thread to convert all _cdc rows, then you must rework your thread conversion strategy, and stop CDC conversions of thread 4.

Start a Replication-CDC thread only for thread #4.

If performance is improved over the standard CDC replication method, you should consider converting threads 1-3 to Replication-CDC.

If performance is not improved, then stop Replication-CDC Thread 4, and revert the CDC changes and restart standard replication thread #4.

As a safety precaution, set the disposition of the new thread to mark as applied. Doing so allows you to fallback if you the results of the scenario are not satisfactory.

Enable CDC on the source database

For a CDC-based database replication, you must configure the source database as CDC.

Note: The same versions of OpenEdge must be loaded on both the Pro2 machine and source database machine. For example, if the OpenEdge Database release is 12.0, then 12.0 must be loaded on the Pro2 machine.

If you are unsure if your database has already been enabled for CDC, you test it by opening the Procedure Editor > Tools > Data Dictionary > Show Hidden Tables > _cdc-change-tracking. If the CDC Tracking Table is there, then your database is enabled for CDC.

To enable CDC on the source database:
Note: This procedure can be run in Single User mode only.
  1. Open the Proenv window.
  2. Shut down your source database and PAS for OpenEdge instance.
  3. Direct your Proenv session to your database location and enter the following command to add the CDC structure file to the source database:
    prostrct add database name db/cdcadd.st
    Note: The structure file will assign particular areas in the source database for the CDC function.
  4. Enter the following command to enable CDC on source database:
    proutil database name -C enablecdc area ReplCDCArea indexarea ReplCDCarea_IDX
  5. Restart the PAS for OpenEdge instance and the source database.
  6. In the Pro2 web interface, select the source database from the Manage Replication tab.

    The Edit Replication window appears.

  7. Change the Source DB Mode to CDC.
Note: After you have enabled CDC on the source database, configure the replication process for CDC based replication.

Add, remove, and customize CDC threads

You can perform CDC-based replication after your source database is CDC enabled. Pro2 treats the both replication threads and CDC threads the same, allowing you greater flexibility in your replication configurations. You can add, remove and customize threads as needed. Again, there are two types of CDC threads that you can select, Replication-CDC Threads and CDC Threads.

Perform the following steps to add replication threads:

  1. On the Pro2 web user interface dashboard, select Total Threads.

    The Total Threads window appears.

  2. Click +Add.

    The Add New Thread window appears.

  3. Select either Replication-CDC Threads or CDC Threads.
  4. Enter a Thread ID. If the thread type is CDC, select the database and other controls according to your needs..
    Note: It is best practice to set the Disposition to Delete as it improves performance.
  5. Choose your database, if you have multiple source databases.
  6. Click Add.
  7. The CDC thread appears in the Total Threads list.

To delete threads:

  1. On the Pro2 UI dashboard, select Total Threads watchbox.

    The Total Threads window appears.

  2. Choose either Replication-CDC Threads or CDC Threads.
  3. Click the check box beside the thread that you want to delete and select Delete.

    The Delete Threads window appears.

    Note: You can delete multiple threads by selecting multiple check boxes at the same time.
  4. Click OK. The thread no longer appears on the Total Threads window.

To customize CDC threads:

  1. On the Pro2 UI dashboard, select the Total Threads watch-box.

    The Total Threads window appears.

  2. Choose either Replication-CDC Threads or CDC Threads.
  3. Indicate the threads you want to update.
  4. Customize the Logging Level, Disposition, and Thread Control as needed using the Actions drop down.

Map CDC

To configure CDC replication, you must map your source and target database after your CDC source database is enabled.

After you have enabled CDC on source database, a CDC Mapping tab appears in the Manage Replication window.

To enable CDC mapping:

  1. From the Manage Replication window on the Pro2 web interface, select the Source Database.
  2. Ensure that the Source DB Mode is CDC. Choose the Test DB Connection button. A message will indicate if CDC is enabled on the source database.
  3. Click the Mapping option and ensure that all tables to be replicated have been mapped from source to target.
  4. Select CDC Mapping .
  5. Select the source tables to be mapped for CDC and click Add.
  6. Select the Policy Settings for each of the Source Tables in the Add / Activate Policy column.
    Note: Pro2 replication is currently designed to capture records the have changed. In CDC, this is known as a Policy. Activating a policy forces the OpenEdge database to immediately start tracking changes to the selected tables. Deactivating a policy forces the OpenEdge database to immediately stop tracking changes to the selected tables.

You can use the Table List functionality to specify specific tables to map by entering them as a comma separated list. This can be useful when you have many tables to sift through or a select few tables that you want to map. By entering in your list of tables, you can Add, Activate, Inactivate or Delete the CDC Policy.

Schedule CDC jobs

You can also schedule a variety of jobs by using the job scheduler. The job scheduler enables you to schedule recurring replications, CDC tasks, file purges, log checks, and more so that you no longer need to perform these tasks manually.

To schedule a job:

  1. From the Actions tab of the side menu, click Jobs > Scheduled jobs > New.
  2. Under the Type drop-down list, select the one of the following job types:
    • CDC Purge
    • CDC Threads
    • Replication-CDC Threads
  3. Enter a Description for the job. A description helps you differentiate between similarly scheduled jobs.
  4. If you are creating a new job, then ensure that the Enable check-box is selected. If you want to disable a scheduled job, but not remove a scheduled job, then you can do so by using this check-box.
  5. In the Setting section, choose the job's start and end date as well as the start and end time.

    You can schedule a job to occur once, daily, weekly, or monthly depending upon your business needs.

  6. Indicate the Database you want to run the thread against, as well as Thread number.
  7. Click Save.

Split CDC threads

Using split threads in a CDC-enabled Pro2 setup can improve replication queue processing performance. You can split each replication thread into multiple split threads, regardless of whether the thread serves to replicate a single table or a group of tables. You can split a single thread into a maximum of 10 split threads. When you split a thread into n number of parts, it provides n-1 split threads. For example, if you split the thread 5 (replBatch5.bat) into four parts, it provides three split threads 51, 52, and 53 along with the original thread 5. This means, the four parts are threads 5, 51, 52, and 53.

To split replication threads in a CDC-enabled Pro2 setup:
  1. Create new .bat files. For example:
    1. Create three copies of the replBatch5.bat file located in the Pro2\bprepl\Scripts folder and rename them as replBatch51.bat, replBatch52.bat, and replBatch53.bat respectively.
    2. Edit the replBatch51.bat file, locate and modify Thread=5 to Thread=51, and save your changes. Repeat this step for replBatch52.bat and replBatch53.bat files.
  2. Create new thread positions:
    1. Go to the Pro2 web interface and click the Total Threads watchbox.
    2. b. Click the Add button,
    3. In the Thread ID type 51, and fill out the remaining settings.
    4. Click Add to repeat this step for threads 52 and 53.
  3. Create a new property:
    1. In the Pro2 interface, click the Properties tab, and then click Add.
    2. In the Property Name field, specify the name of the property as SPLIT_THREAD5.
    3. In the Value field, specify the number of threads as 4.
      Note: You can add up to n-1 value where n is the number of threads. The maximum value allowed per thread is 10. However, if you specify a value (characters or negative numbers) other than the numbers 1 to 10, the CDC uses only the actual thread number.

After generating the CDC code and creating new property for split threads, if you do not create the .bat files and do not schedule the task entries, then the data remains in the queue until the new replication thread is started.

Progress recommends that you do not change the split number if there are existing records in the queue. Changing the split number might push the changes of the same record and row to a differently calculated queue.

Enable or disable queue compression for CDC

Note: This is an optional property and is disabled by default. This property is not applicable for Replication-CDC Threads

You can enable or disable queue compression by means of the CDC_QUEUE_COMPRESSION property. You can edit this property from the web UI.

  1. Click Properties tab and select the CDC_QUEUE_COMPRESSION property.
  2. Click Edit.
  3. Set the Value to either YES or NO.
  4. Click Save.

Optimize CDC performance

You can optimize performance for both LAN and WAN configurations that use CDC by running CDC replication threads directly on the source database machine of your deployment.

There are two methods to optimize CDC replication. The method that you use depends on the configuration of your deployment.

For LAN configurations:
  1. Go to bprepl/scripts/ in the Pro2 root folder.
  2. Locate the Build_CDC_pkg.bat file.
  3. Run the Build_CDC_pkg.bat file directly on your source database machine.
For WAN configurations:
  1. Go to bprepl/scripts/ in the Pro2 root folder.
  2. Locate the CDCBatch.bat file.
  3. Update the CDCBatch.bat with your database name and thread number. For example, database=database name, Thread=1.
  4. Run the bprepl/scripts/CDCBatch.bat directly on the on the source database machine of your WAN deployment.

For more information about CDC optimization, see Docs/README_CDC_Build.txt in the root folder of your Pro2 installation.