The Data Masker Create/Edit Cross Database Table-to-Table Rule Form

This form is used to create and edit Data Masker Cross Database Table-to-Table rules. Cross Database Table-to-Table rules are designed to synchronize data in a table in the SQL Server database referenced by the current Rule Controller and update the corresponding records in a table in another database. The remote database can be on a different server than that configured in the Rule Controller and can also be of a completely different database type. Currently connections to remote SQL Server and Oracle databases are supported. The example above illustrates a Cross DB Table-to-Table rule configured to copy the contents of the DM_CUSTOMER table in the local SQL Server database to table named DM_CUSTOMER_NOTES in a remote Oracle database.

This form supports both the editing of existing and the creation of Cross Database Table-to-Table Rules. The title text and button labels on the form will change as is appropriate to the create or edit mode. In the example screen shot above, the form is editing an existing Cross Database Table-to-Table rule.

The connection information for the remote database is entered in the panel at the top of the Create/Edit Cross Database Table-to-Table Rule Form (please see the image above). Details on the usage of this area of the form can be found on the Cross Database Login Panel help page. Please see that page for further information on configuring the remote database connection.

Important Note: Please read the section of the Cross Database Table-to-Table rule help page which discusses the pre-masking of the columns in the target table prior to performing the Cross Database Table-To-Table synchronization. This can avoid accidentally leaving some columns un-masked if the join values in the target do not appear in the source table.

The Target Table

The name of the target table is entered in the Target Table Name field at the top of the Cross Database Login Panel. If the remote database is case sensitive be sure to enter this table name exactly as the remote database requires it. The target table name does not have to be identical to the source table name.

The Sync. Columns Panel

The Sync. Columns panel is designed to contain the column information for the source and target tables which will need to be synchronized. In other words, the values of the specified source columns will be used to update the contents of matching records in the remote target table based on a configured join condition specified elsewhere (see below).

The contents of the Sync. Columns Panel cannot be specified until the source table has been selected in the panel on the left hand side. Once the source table has been selected, the drop down box in the first row of the Local Column Name area can be used to choose a column to synchronize. It is a common requirement to synchronize multiple columns and the Add button can be used to add additional synchronization columns to the rule.

By default, the Cross Database Table-to-Table rule assumes that the target synchronization column name is identical to the source column name. When a new column is added, the Remote Column Name area is populated with the name of the source table column. If this is incorrect, then the name of the remote column can be changed by placing the cursor on the remote column name and editing the name to an appropriate value. In the example in the screenshot above, the Customer_LastName column name is identical in both the local and remote tables and has been left un-edited. However the CUSTOMER_FIRSTNAME column in the local table is named FNAME in the remote table and so that value has been edited as appropriate. The names of the remote columns can be changed at any time after the rule has been created.

The Join Columns Panel

The Join Columns panel is designed to contain the column information for the source and target tables which identify the equivalent rows. In other words, the values of the specified source join columns will be used to find the equivalent rows in the remote target table and then the columns on the source side of the Sync. Columns panel will be used to update the specified Sync. Columns on the target side.

The contents of the Join Columns Panel cannot be specified until the source table has been selected in the panel on the left hand side. Once the source table has been selected, the drop down box in the first row of the Local Column Name area can be used to choose a column on which to join. Many tables will need more than one column in order to fully specify the join condition and the Add button can be used to add additional join columns to the rule.

It is not possible to configure the Table-To-Table Synchronization rule to use an join columns which are also part of the Sync. Column list. If you need this functionality please contact the Data Masker support team for advice.

By default, the Cross Database Table-to-Table rule assumes that the target join column name is identical to the source column name. When a new column is added, the Remote Column Name area is populated with the name of the source table column. If this is incorrect, then the name of the remote column can be changed by placing the cursor on the remote column name and editing the name to an appropriate value.

In general, it is a good idea to always ensure the columns specified for the target side of the join condition are indexed. If they are not indexed, the rule will be much slower as the remote database will need to scan the entire contents of the remote table for each row which needs to be synchronized.

The Where Clause Panel

Cross Database Table-to-Table rules will, by default, synchronize all of the rows in the source table into the remote target table. If it is necessary to only move a subset of the source table rows to the remote database table a Where Clause can be implemented on the Cross Database Table-to-Table rule. Where Clauses are defined using the Where Clause panel on the Where Clause tab. Since the Where Clause will be applied to the source table rows, the SQL of the Where Clause must be defined in a manner appropriate to the source SQL Server database defined in the Rule Controller. The example below illustrates a Where Clause applied to the rule which will only synchronize the rows in the source tables CUSTOMER_ID column which start with the characters IND.

Important Note: Please read the section of the Cross Database Table-to-Table rule help page which discusses the pre-masking of the columns in the target table prior to performing the Cross Database Table-To-Table synchronization. This can avoid accidentally leaving some columns un-masked if the join values in the target do not appear in the source table.

The Data Masker The Cross Database Table-to-Table Rule Where Clause Tab

The Error Manager Panel

The Ignore listed errors panel of the Error Manager tab permits the execution of the other rules in the masking set to continue if the specified error occurs. The Ignore Errors panel can contain the error number of all or part of the error message. Cross Database Table-to-Table rules can have other rules dependent on them. When the If error is ignored, skip all dependent rule option is active, no dependent rules will be processed if an error is trapped and ignored.

Important Note: The error manager does not trap errors on a row-by-row basis. In other words if it is not possible to update a row (perhaps due to a primary or foreign key violation) the rule will error. If that error is trapped, then the rule will just stop and no further rows will be updated. The rule will not attempt to synchronize further rows in the remote table. In such a case, the remote table will be in an indeterminate state. In the example below the rule has been configured to ignore any errors relating to the non-existence of the target table. Presumably, in this case, the update of the source rows to the remote table has not been considered essential for some reason.

The Data Masker The Cross Database Table-to-Table Rule Form Error Manager Tab

Creating a new Cross Database Table-to-Table Rule

Creating or editing a Cross Database Table-to-Table rule is a straightforward process. Cross Database Table-to-Table rules are created by launching the New Cross Database Table-to-Table rule form using the New Rule button located on the bottom of the Rules in Set tab.

As with all Data Masker rules - Cross Database Table-to-Table rules execute in the order specified by their Rule Block and Dependency state.

How to Create a New Cross Database Table-to-Table Rule