How To: Integrate only rows having specific column value from a source table to target

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #15210
    Pallavik
    Keymaster

    How To:  Integrate only rows having specific column value from a source table to target

    Scenario:

    Imagine you have a business requirement to filter only rows with specific column value 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 which have Status column with value ‘valid’. Below we will see how this can be achieved using HVR’s Restrict/CaptureCondition and Restrict/IntegrateCondition on source and Target groups.

    Overview:

    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 syntax and it is case sensitive.

    2.Correct column name can be found by right clicking on 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 oracle database but in HVR 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 which are valid. In this case rows with ID 2, 3 and 5

    3) Action Capture and Integrate are already defined for the channel chn1

    Steps:

    Step A] Add action Restrict/ CaptureConditon on the source group

    1.Right click on the channel select Restrict under New Action

    2.In the pop up window check box for /CaptureCondition for source group and table employee

     

    3.In the space provided next to option /CaptureCondition type {status}=’valid’

     

    4.Click on OK to add action to the channel

    Step B] Add action Restrict/ IntegrateCondition on Target group

    1.Right click on the channel select Restrict under New Action

     

    2.In the pop up window check box for /IntegrateCondition for target group and table employee

    3.In the space provided next to /IntegrateCondition enter {status}=’valid’

    4.Click on OK to add action to the channel

    5.The actions on this channel should look like in below image

     

    Step C] Perform Initialize so that these actions can take effect.

    1.Right click on channel and Click on HVR Initialize

    2.In the pop up window click on Initialize and then 2 new jobs are created

    3.In the Scheduler we see that 2 new jobs are created and are running.

    4.As a test we will insert one row to the source with value ‘valid’ for column ‘status’ and one without value ‘valid’

    a.Insert a row with status value ‘valid’ to the table employee on source database

    You will notice that this row is replicated to the target

    b.Insert a row with status value ‘invalid’ to the table employee in source database

    This row will not get replicated to the target

     

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

    Source table

    Target Table

    6.As another test we will update the invalid row on the table to be valid and vice versa

    a.Update a row to be valid

    Which means that John Doe who is invalid should be replicated to the target. And as expected we see it in Target database

    b.Update a row to be invalid on 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.

Viewing 1 post (of 1 total)
  • You must be logged in to reply to this topic.

© 2020 HVR

Test drive Contact us