Configuration of EnterworksDiff Utility

Perform the following steps to set up the first diff configuration.

Open a browser and enter the following URL:
http://localhost:8090/infolink/
Click on the EnterworksDiff application link.
Expand Scenarios, expand pg_diff, and click on source_data1.

Open pg_diff and Select source_data1

Field	Value	Comment
Source	ServerFileSystem	Specifies the source is a file.
From table or query	Table	The source is a file which is like a table in terms of structure vs. a query.
Space	<install drive> :/Enterworks/shared/DiffFiles	Specifies where to find the file.
Table	source_new.csv	Specifies the new file name.
Archive format	none
Content format	csv	If the source file is pipe-delimited, set this to psv.

Configure the Application for the New Source File

Ensure the Target settings are as follows:

Field	Value	Comment
Source	PostgreSQL	Specifies the target is the database.
Table	source_new	Specifies the name of the target table.
Standardize column names	<unchecked>

Click on Load: source_data2.

Configure the Application for the Old Source File
Ensure the Input settings are as follows:

Field	Value	Comment
Source	ServerFileSystem	Specifies the source is a file.
From table or query	Table	The source is a file which is like a table in terms of structure vs. a query.
Space	<install drive> :/Enterworks/shared/DiffFiles	Specifies where to find the file.
Table	source_old.csv	Specifies the old file name.
Archive format	none
Content format	csv	If the source file is pipe-delimited, set this to psv.

Field	Value	Comment
Source	PostgreSQL	Specifies the target is the database.
Table	source_old	Specifies the name of the target table.
Standardize column names	<unchecked>

Select Difference: source data1, copy the following JSON to a JSON editor (e.g., http:// http://jsoneditoronline.org/) and update the keyColumns array to contain only the file columns to be used to uniquely identify each record in the file. Change keyCol1 and keyCol2 with the primary key columns, (as few or as many as needed):
{
"into": {
"tableName": "source_data_new_updated_removed"
},
"opName": "Difference",
"opParams": {
"ignoreColumns": [],
"curTableSpaceName": null,
"keyColumns": [
"keyCol1",
"keyCol2"
],
"sourceName": "PostgresSQL",
"prevTableSpaceName": null,
"curTableName": "source_new",
"prevTableName": "source_old"
},
"title": "Difference: source_new"
}
Copy the resulting JSON to the Define operation in JSON format: field.

Configure the Key Columns
Select the final Load Activity.
Ensure the Input settings are as follows:

Field	Value	Comment
Source	PostgresSQL	Specifies the source is the PostgreSQL database.
From table or query	Table	The source is a table in terms of structure vs. a query.
Space	<blank>	Specifies where to find the file.
Table	source_data_new_updated_removed	Identifies the delta table.
Archive format	none
Content format	csv

Field	Value	Comment
Source	ServerFileSystem	Specifies the target is a file.
Table	<install drive> :/Enterworks/shared/DiffFiles/ source_delta.csv	Specifies the name of the delta file to be generated.
Standardize column names	<unchecked>

Right-click on the pg_diff scenario and select Append Operation -> Transform from the pop-up menu.
Select Transform
Set the Source to PostgresSQL.
Set the Target Source to PostgresSQL.
Set the Target Table to source_data_stat.
Click on the query window and press the i key to enter insert mode, (the window is a vi-based editor).

select

select count(*) from source_new) as cur_data_count,

select count(*) from source_old) as prev_data_count,

select count(*) as c from source_data_new_updated_removed where il_modification_status = 'new') as diff_new_count,

select count(*) as c from source_data_new_updated_removed where il_modification_status = 'updated') as diff_updated_count,

select count(*) as c from source_data_new_updated_removed where il_modification_status = 'removed') as diff_removed_count;

Click in the query window and press Ctrl->v to paste the query to the query window.

Paste the Query
Press the Esc key (to get out of insert mode in the query window).
Right-click on the pg_diff scenario and select Append Operation->LoadTableFromSource from the pop-up menu.

Select LoadTableFromSource
Ensure the Input settings are as follows.

Field	Value	Comment
Source	PostgresSQL	Specifies the source is the PostgreSQL database.
From table or query	Table	The source is a table in terms of structure vs. a query.
Space	<blank>	Specifies where to find the file.
Table	source_data_stat	Identifies the delta table.
Archive format	none
Content format	csv

Field	Value	Comment
Source	ServerFileSystem	Specifies the target is a file.
Table	<install drive> :/Enterworks/shared/DiffFiles/ source_stat.csv	Specifies the name of the delta file to be generated.
Standardize column names	<unchecked>

To test the process, in the folder:
<install drive> :\Enterworks\shared\DiffFiles
place two files, with the same column header, named:
source_old.csv
source_new.csv

When complete, the new file source_delta.csv will be in the specified folder. It contains the new records, updated records and deleted records.

The source_delta.csv File
The contents of the source_delta.csv file will be the same as the old and new files but with the added column il_modification_status, which will contain the statuses new, removed, or updated to indicate whether a record has been added, removed, or changed between the old and new file.
The source_stat.csv file will contain the record counts for the old and new files, as well as the number of added, updated, and deleted records.
The format of the file is shown below.

The Format of the source_stat.csv File