OpenEdge provides support for a feature called Change Data Capture(CDC). CDC is an OpenEdge RDBMS feature that identifies and captures data that has 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 this feature and replaces the ABL triggers with CDC. Pro2 CDC implementation also supports a feature called thread#0. When this feature is used, the CDC Admin thread converts all CDC_change_tracking records irrespective of which thread the table is mapped to. This provides the convenience of having a single CDC thread that converts all CDC records to replqueue records.

The CDC replication process is faster than using ABL triggers and can be managed online. This results in relatively low database downtime. The client application connection remains unchanged and there is no need to connect to the repl database. You can create a copy of the CDC batch program (CDCBatch.p) and append the file name with the thread number to run multiple threads. For example, CDCBatch0, CDCBatch1, and CDCBatch2.

Limitations

Pro2 and CDC is limited in that Pro2 handles only Level 0 policies. Policies should be created with the suffix Pro2 to be recognized by Pro2. For example, <tablename>_pro2. More over, Pro2 does not replicate any changes that are generated from Non-Pro2 policies. To determine whether a record is read, Pro2 uses the _userMisc field in the change_tracking_table.

Enable CDC on source database

In a CDC based replication the replication queue records (also known as replQ records) are generated from change tracking table. The change tracking table is present in the source database. Hence, the change tracking records are stored in the source database. In a trigger based replication the replQ records are generated by deploying pro2 triggers on source database (in this case, sports). However, for CDC based replication, the source database should be CDC enabled.

Note:

The same versions of OpenEdge must be loaded on both the Pro2 machine and source database machine. For example, if the OE Database version is 11.7, then 11.7 must be loaded on the Pro2 machine.

Perform the following steps to enable CDC on source database:

  1. Open the Proenv window.
  2. Shut down PAS instance and source database (here, sports).
  3. Enter the following command to add CDC structure file to the source database:
    prostrct add sports cdcadd.st
    Note: The structure file will assign particular areas in the source database for CDC function.
  4. Enter the following command to enable CDC on source database:
    proutil sports -C enablecdc area ReplCDCArea indexarea ReplCDCarea_IDX
  5. Restart the PAS instance and the source database.
  6. In the Pro2 web user interface, select the source database from the Manage Replication tab. The Edit Replication window appears.
  7. Change the Source DB Mode to CDC.
Note: Once you have enabled CDC on source database, configure the replication process for CDC based replication.

Add, remove and customize CDC threads

You can perform CDC based replication, once your source database is CDC enabled. The replication process is similar to trigger based replication. You can add, remove and customize CDC threads as per your needs.

Perform the following steps to add replication threads:

  1. In the Pro2 web user interface, select Dashboard > Total Threads. The Total Threads dropdown appears.
  2. Click +Add. The Add New Thread window appears.
  3. Select CDC as Thread Type, Enter a Replication Thread ID and set up the other controls based on your needs.
  4. Click Add.


  5. The CDC thread will now appear on the Total Threads list.

Perform the following steps to delete CDC threads:

  1. In the Pro2 web user interface, select Dashboard > Total Threads. The Total Threads dropdown appears.
  2. Select the Check-box beside the CDC thread that you want to delete. Click Delete. The Delete Threads window appears.
    Note: You can delete multiple threads by selecting multiple check-boxes at the same time.
  3. Click OK. The CDC thread does not appear in the Total Threads window anymore.

Perform the following steps to customize CDC threads:

  1. Click the Replication Process drop-down button in the Pro2 web user interface Dashboard.
  2. Select CDC Process. The CDC threads will appear on screen.
  3. Customize the Logging Level, Disposition, and Replication Control as per your needs.

Map change data capture

Configure the replication process again after you have enabled CDC on the source database. It is important to enable CDC based replication. The configuration of CDC replication process is similar to trigger based replication process.

After you have enabled CDC on source database, a CDC Mapping tab appears in the Manage Replication window. Perform the following steps to enable CDC mapping:

  1. Select the source database from the Manage Replication window. The Edit Replication tab appears.
  2. Ensure that the Source DB Mode is CDC. Choose the Source DB Connection. Click Next. The Select Source tab appears.
  3. Fill up the details in the Select Source window. Click Save. The Set Target tab appears.
  4. Fill up the details in the Set Target window. Click Save. The Generate Target Schema tab appears.
  5. Generate target schema, download schema files and click Save. The Mapping Source to Target Tables tab appears.
  6. Select the tables from the source database that you want to map with the target database and click Next. The CDC Mapping tab appears.
    Note: Mapping tables from source to target database is a prerequisite to CDC mapping. You will be able to view the tables that you have mapped from the source to the target database in the CDC Mapping tab. The tables which are not mapped will not appear in the CDC Mapping tab. See .
  7. Select the tables on which you want to activate CDC mapping policy and click Add.
  8. Select the Policy Settings for each of the Source Table in the Add / Activate Policy column.
    Note: Pro2 replication is currently designed to capture the fact that records have changed. In CDC terms this is known as Policy. Activating a policy forces the OpenEdge database to immediately start tracking changes to the selected table(s). Inactivating a policy forces the OpenEdge database to immediately stop tracking changes to the selected table(s).
  9. Click Next. The Generate Code tab appears.

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 ten 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, use this procedure:
  1. Create new .bat files. For example:
    • 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.
    • Edit the replBatch51.bat file, locate and modify Thread=5 to Thread=51 and save your changes. Repeat this step replBatch52.bat and replBatch53.bat files.
  2. Create new thread positions:
    • To create new thread positions within Pro2, click the Advanced Configuration tab in the Pro2 web user interface.
    • For thread 51, type 51 in the field next to the Create New Thread button, and then click Create New Thread. Repeat this step for threads 52 and 53.
  3. Create a new property:
    • In the Pro2 web user interface, click the Properties tab and then click Add.
    • In the Property Name field, specify the name of the property as SPLIT_THREAD5.
    • 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 thread. 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 trigger uses only the actual thread number.
Attention:
  • Even after generating the trigger code and creating new property for split threads, if you do not create the .bat files and do not schedule the task entries, the data remains in the queue until the new thread jobs are started.
  • It is recommended 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 specific to Pro2 WAN setup only.

You can enable or disable queue compression by means of the CDC_QUEUE_COMPRESSION property.

You can add this property by manual intervention from the web UI. Click Properties tab and Select Add, Enter the Property Name and Value. Click Save. If this property exists, and the value is 'YES', queue compression is enabled. Else, queue compression is disabled by default.