Problem Summary
How to integrate only rows having specific column value from a source table to target
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 employees who are valid. In other words, rows that have Status column with value 'valid'. Below we will see how this can be achieved using Local Data Processing's Restrict/CaptureCondition and Restrict/IntegrateCondition on the source and target groups.
Answer
In our case the /CaptureCondition and /IntegrateCondition will be {status}='valid' which means that we want to integrate only the rows which have column value as 'valid'.
Notes:
1. The column name is always surrounded by curly braces as a part of the syntax and it is case sensitive.
2. Edit column name can be found by right-clicking on the table name in Local Data Processing 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 Local Data Processing GUI it is in lowercase so we will use lowercase.
3. Please note that the channel can have either of the 2 conditions. i.e. Channel can have either 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 above conditions will work just fine.
Pre-requisites
1) A channel chn1 is already present with Oracle source and SQL server Target
2) Table Employee is already added to the channel chn1 which looks like below and we want only rows that are valid. In this case rows with ID 2, 3 and 5
3) Action Capture and Integrate are already defined for the channel chn1
Add action Restrict/ CaptureConditon on the source group by Right-click on the channel select Restrict under New Action
4) In the pop up window check box for /CaptureCondition for source group and table employee
5) In the space provided next to option /CaptureCondition type {status}='valid'
6) Click on OK to add action to the channel
7) Add action Restrict/ IntegrateCondition on Target group. Right-click on the channel select Restrict under New Action
8) In the pop-up window checkbox for /IntegrateCondition for the target group and table employee
9) In the space provided next to /IntegrateCondition enter {status}='valid'
10. Click OK to add action to the channel. The actions on this channel should look like in below image
11) Perform Initialize so that these actions can take effect. Right-click on channel and Click on Initialize
12) In the pop-up window, click on Initialize and then 2 new jobs are created
13) In the Scheduler, we see that 2 new jobs are created and are running.
14) As a test we will insert one row to the source with value 'valid' for column 'status' and one without value 'valid'
15) Insert a row with status value 'valid' to the table employee on the source database
You will notice that this row is replicated to the target
16) Insert a row with status value 'invalid' to the table employee in the source database
This row will not get replicated to the target
17) Below is the source table and target table. We can clearly see the difference
Source table
Target Table
18) As another test, we will update the invalid row on the table to be valid and vice versa. Update a row to be valid
This means that John Doe who is invalid should be replicated to the target. And as expected we see it in Target database
19) 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
This explains how Restrict/CaptureCondition and Restrict/IntegrateCondition can be used to get the filtered data.