How you specify join tables - Connect_CDC - connect_cdc_mimix_share

The trigger table is the sending table whose row triggers replication. This applies to Copy as well. Each join includes one additional table via the trigger table; there may be multiple joins from one trigger table. To define how to access a record in a secondary table given a record in the trigger table, use one of the following:

Refresh the list of columns and indexes for only those tables to which you want to join. If, for a given trigger table, you know which these are, right-click the trigger table, then select Refresh>This table only, to get the index information.

Alternately, if you choose Refresh Available tables, use this with appropriate filtering since it can take considerable time for a large number of tables.

Note: Check Retrieve columns and indexes with tables in the Refresh Available tables dialog.
Access the Join Tables dialog, as follows.
Usually joins are defined using the context menu on an available table. However, when a table becomes a sending table, it is removed from the available table list so then you can use the Join button on the sending table Properties page to display the Join Table dialog. Use the Join popup menu item on an available table. See the following example:

or

Use the Join button on the Sending Table Properties, Table tab to display the Join Tables dialog.

Note: A join is a property of an Available Table not a Sending Table. It can be defined once and stays with the Available Table even if it is not a Sending Table at the current time.
Specify the join information on the Join Table dialog.

The Join Table field contains a list of all the tables in the Available Tables cache. You see in the example, that we are joining to a table qatest.dbo.ADDRESS that contains employee address information.
1. Check Allow outer join, to allow the source row to be sent even if the join fails.
2. In Join table, select the table.
3. If required, select Allow non-unique column join, which enables you to select any column from the join table as a join column. At least one indexed column must be selected to avoid a table scan at execution time.
4. If Allow non-unique column join is not selected, select the index.
5. When a table is selected, Join table indexes is filled in automatically. If Allow non-unique column join was not selected, only unique indexes are displayed. If Allow non-unique column join was selected, all indexes are displayed. Any displayed index is a potential join index, but only one join can be defined between any two tables.
6. When an index is selected, the grid fills in. At present, the only method available is Copy column.
7. Define how each join column value is to be formed from the trigger table columns; this may currently be only a single index, which may consist of more than one column. Click the blank field under Source Data to access a list of compatible columns from the source table.If Allow non-unique column join is selected, any column from any index may be used.
  
  If no join data existed, you may manually map the join columns, or use Defaults to try to automap by name.
8. If you selected Allow non-unique column join, an additional Indexed column displays in the grid. The checkbox is selected if the column is defined as the first column in at least one index on the join table. It is the first column of the index to ensure that the index is usable when the join query is executed. In most databases, if only the secondary columns of a multi-column index are specified in the where clause, the index is not used, resulting in a table scan.
9. Use Apply to validate and save the current grid data. After you use Apply, you may define additional join conditions to other tables.
  
  When the join condition is defined, all columns in the joined table are available to be used in an expression. Currently, all join columns used in an expression must be fully qualified by the table name or user-assigned alias.
  
  Once join columns are mapped, you can use Unmap all to remove the join definition.
10. Using the Distribute Tables dialog, distribute the trigger table then select the table for distribution.
When you click OK, you receive a message about the distribution definition.
Click OK.
On the Properties page Mapping tab:
1. Since these tables already have a distribution, the target columns that are being mapped without using join columns have the method defined, Copy Column, as seen in the example below.
2. On the target columns that are being mapped using join columns, write an expression:
  1. Select Expression in the Method column.
  2. Click Edit Source to open the Target Column Expression dialog.
3. Build an expression using columns from the trigger table, as usual. To include columns from any tables joined from the trigger table, you must select the joined table from the Table field.
4. Specify an alias, if desired.
  
  After you select the joined table, the box marked “Select table, give alias” becomes available . It is never enabled for the trigger table itself.
  
  Since a joined table may contain columns with the same name as those in the trigger table or another joined table, you must distinguish (qualify) them in the expression text, by doing one of the following:
  - Fully qualify the column by the catalog.schema.table name.
  - Assign an alias to the fully qualified table name and use the alias to qualify them.
  An unqualified column name is assumed to be one from the sending table. If an alias is given, any columns from joined tables will use the alias to qualify them when you insert the column; if no alias is given, the fully qualified table name will be used to qualify the column name.
  
  You receive a warning message if an unqualified column exists in more than 1 table.
5. Choose the method you want to use in the expression. In the example, ADDRESS_1 and ADDRESS_2 are going to be concatenated so the choice is concat2. When you click insert, the word concat appears in the window.
6. Place your cursor in the parentheses in the window. In the Column field, select the name of your joined column and click the Insert button. Your column name is inserted in the expression window.
7. Enter a comma between the column names.
8. Select the next Column from the Column field, and click Insert. The entire expression in the example is:
```
concat( qatest.dbo.ADDRESS.”ADDRESS_1”, QATEST.DBO.ADDRESS.”ADDRESS_2”)
```
9. Validate the expression.
10. Click OK

Note: The Source Data on the Mapping tab fills in with the expression.

Once a join is defined, you can write multiple expression to columns in the joined table.

To continue with our example, you could concatenate three columns in the joined table, CITY, STATE, and PROVINCE to one target column LOCATION.

Using the same steps, this time you choose concat3 as the method since there are 3 inputs. The entire expression is:

concat( qatest.dbo.ADDRESS.”CITY”,qatest.dbo.ADDRESS.”STATE”,qatest.dbo.ADDRESS.”PROVINCE”)

Similarly, you could write an expression for the column ZIP_CODE to the target column ZIP_CODE.

On the Target Column Expression window, choose the column ZIP_CODE in the Column field and click Insert. The entire expression is:

qatest.dbo.ADDRESS.”ZIP_CODE

When you validate, you may receive a message like the following warning you about possible null values:

How you specify join tables - Connect_CDC - connect_cdc_mimix_share - Latest

Connect CDC Advanced User Guide