How To: Set up a capture from multiple sources when all the tables in the channel don't reside in all of the source locations
Scenario:
Consider a use case where you want to capture some tables from oracle and some tables from sqlserver. And each of these tables belong to only one source. Assume you want to capture table 'customer' and 'orders' from oracle location (orcl) and tables 'item' and 'order_line' from SQL server database (sqlw2).
Pre-requistes:
- Channel chn1 is created
- Source oracle (orcl) and source SQL server are created in Location Configuration
Steps:
Step 1]Define SOURCE group
A] Right click on Location groups under chn1 and select New Group
B]In the new group window select the 2 sources that you want to capture from. In our case it is orcl and sqlw2.
C] Click ok to confirm
Step 2] Define TARGET group
A] Right click on Location groups under chn1 and select New Group
B] In the new group window select the target. In our case it is oracle on Linux (olx)
C] Click ok to confirm.
Step 3] Add Tables to the channel from respective sources. In our case we add tables 'customer' and 'orders' from orcl and tables 'item' and 'order_line' from SQL server.
Step 4] Add action Capture for SRC group
A] Right click on the SRC group to select Capture under New Action
B] On the Capture Action window click on ok to confirm
C] Action will look as below
Step 5] Next step will be to expand the capture action so that it is assigned for each table. This extra step is required in this kind of scenario.
A] Right click on asterisk under the Tables which will open a drop down box. And click on Expand option
B] After clicking on Expand it will list capture action for all the tables as shown below.
Step 6] Select correct Location for each table.
A] Right click on the action and go to its properties
B] In the pop up window check box for Configuration Action and select the correct source location where the table exists. In our case table customer resides in orcl.
C] Similarly we need to do this for all the tables. Perform step 6 again to specify that table 'orders' reside in orcl, 'item' resides in sqlw2 and order_line resides in sqlw2.
Step 7] Create Integrate action for TGT group
A] Perform Initialize which will create 3 jobs, 2 for capture and 1 for integrate.
Step 8] Perform Refresh
Step 9] Start the capture and Integrate Jobs