Creating Change Data Capture Reports in IRI Workbench

by Claudia Irvine

There is now a bulk/batch Change Data Capture (CDC) wizard in the IRI Workbench GUI for Voracity, built on Eclipse™. This new graphical report builder compares previous and current data sources based on a flag field you select, and gives you the option of producing five different targets — Cumulative, Delete, Equal, Insert, and Update — or any combination thereof.

As this how-to article shows, the new CDC wizard also allows you to select a delta field where you can add a text description of your transaction type. In this example, I’m specifying “DELETE”, “EQUAL”, “INSERT”, and “UPDATE” in my Cumulative report.

On the input screen, you enter the two data sources (tables or files), and the format and metadata available (or that you create) for each. You also enter the field that is your flag for each data file. In this case, we use checksum, but it could be a transaction date, etc.

The next page is where you enter the join condition:

On the final output page, you specify the targets, formats, and metadata for each report type you want produced. You can select as many report types as needed. You can also select a delta if your report has a description of that transaction type.

Because you may have standard company terminology you need to use, you can type in your delta description. In this case, I only need that description in the cumulative report. Note that I can use conditional /INCLUDE logic for incremental reports on flat files like these, and for database sources, /QUERY logic, as shown in this example of incremental data replication in an IRI job script.

When you click ‘Finish’ the wizard produces a single CoSort SortCL job script that Voracity users can schedule or run from the GUI or in a command-line or batch environment.

The script contains multiple /CONDITION statements to differentiate the type of transaction. The DELTA_FLAG field in my Cum.data output file contains an If-Then-Else statement that determines which delta description to use. The other report outputs use Include and Omit statements to pull the proper transaction type into the report:

The script can be modified to add headers, counts, formatting, etc. to your report — even cross-calculations that would show the differences in values between ‘previous’ and ‘current.’ After modifying my script to add a header and running the script, my output for the Cumulative report looks like this: