How to Integrate Only Rows Having Specific Column Value from Source Table to Target

Question

How can I integrate only rows having specific column value from a source table to target table?

Environment

HVR 5

Answer

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.

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

1. Add action Restrict /CaptureCondition on the source group: right-click the channel and select Restrict under New Action.

   

2. In the pop-up dialog, select /CaptureCondition for the source group and table Employee:

   

3. In the /CaptureCondition field, type {status}='valid':

   

4. Click OK to add the action to the channel.

5. Add action Restrict /IntegrateCondition on the target group. Right-click the channel and select Restrict under New Action.

   

6. In the pop-up dialog, select /IntegrateCondition for the target group and table Employee.

   

7. In the /IntegrateCondition field, enter {status}='valid'.

   

8. Click OK to add the action to the channel. The actions on this channel should look like in the image below.

   

9. Perform Initialize so that these actions can take effect. Right-click the channel and click HVR Initialize.

   

10. In the pop-up dialog, click Initialize to create two new jobs.

    

11. Under the Scheduler, two new jobs are created and running.

    

12. 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.

    

13. 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.

14. Below is the source table and target table. We can clearly see the difference.

    Source table

    

    Target Table

    

15. Update the invalid row on the table to be valid 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.

    

16. 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.