How to Integrate Only Rows Having Specific Column Value from Source Table to Target
Problem Summary
How to integrate only rows having specific column value from a source table to target table.
Imagine you have a business requirement to filter only rows with specific column values from a source table into the target. For example, you have an Employee table in the source database with columns ID
, Name
, Address
, and Status
. Now you want to load only the employees for whom the Status
column has value valid
. In other words, rows that have Status column with value 'valid'. Below we will see how this can be achieved using action Restrict with parameter /CaptureCondition on the source group and action Restrict with parameter /IntegrateCondition on the target groups.
Solution
In our case the /CaptureCondition and /IntegrateCondition will be {status}='valid', which means that we want to integrate only the rows that have column value valid
.
NOTE: The column name is always surrounded by curly braces as a part of the syntax and is case-sensitive. You can edit column name by right-clicking the table name in HVR GUI and checking the Properties for the table. For example in our case, column name 'status' is in UPPERCASE in the Oracle database but in HVR GUI it is in lowercase, so we will use lowercase.
NOTE: The channel can have either of the 2 conditions: Restrict /CaptureCondition or Restrict /IntegrateCondition.
Recommendations
- If Db2i is a source and SQL Server is the target, Restrict /IntegrateCondition works better.
- If Oracle is used as a source or target, either of the conditions (Restrict /CaptureCondition or Restrict /IntegrateCondition) will work just fine.
Pre-requisites
Channel chn1 is already present on Oracle source and SQL Server target.
Table Employee is already added to channel chn1 as in the image below, and we want only rows that are valid. In this case, rows with IDs 2, 3 and 5.
Actions Capture and Integrate are already defined for channel chn1.
Steps
Add action Restrict /CaptureCondition on the source group: right-click the channel and select Restrict under New Action.
In the pop-up dialog, select /CaptureCondition for the source group and table Employee:
In the /CaptureCondition field, type
{status}='valid'
:Click OK to add the action to the channel.
Add action Restrict /IntegrateCondition on the target group. Right-click the channel and select Restrict under New Action.
In the pop-up dialog, select /IntegrateCondition for the target group and table Employee.
In the /IntegrateCondition field, enter
{status}='valid'
.Click OK to add the action to the channel. The actions on this channel should look like in the image below.
Perform Initialize so that these actions can take effect. Right-click the channel and click HVR Initialize.
In the pop-up dialog, click Initialize to create two new jobs.
Under the Scheduler, two new jobs are created and running.
Insert a row with value
valid
for column Status in table Employee on the source database.You will notice that this row is replicated to the target.
Insert a row with value
invalid
for column Status to table Employee in the source database.This row will not get replicated to the target.
Below is the source table and target table. We can clearly see the difference.
Source table
Target Table
Update the
invalid
row on the table to bevalid
and vice versa.
Update a row to be valid.This means that the row that has value
invalid
should be replicated to the target. As expected, we see it in the target database.Update a row to be
invalid
on the source database.Since row with ID=2 is no more
valid
, it will not get replicated to the target.